Question

weighted average spreadsheet setup
mendell reviews his financing options and decides to adjust the amounts
borrowed. he now takes $15,000 from the dealership at an interest rate of
4.5% and $10,000 from a personal line of credit at an interest rate of 5.5%.
mendell inputs his loan details into a spreadsheet to calculate the weighted
average interest rate.

which formula should be entered in cell d5 to compute the weighted average
interest rate based on the totals calculated in the spreadsheet?

=d4/b4*100

=(b2c2 + b3c3)/b4

=sum(d2:d3)/sum(b2:b3)

=d3/d4

Explanation:

Step1: Recall weighted average formula

The weighted average formula \( \text{Weighted Average}=\frac{\sum(\text{Value}\times\text{Weight})}{\sum\text{Weight}} \). In this case, the \(\text{Value}\) is the interest rate (but since in the spreadsheet, the weighted product \(D2 = B2\times(C2/100)\) and \(D3=B3\times(C3/100)\), when we sum \(D2:D3\), we have \(\sum(\text{Amount}\times\text{Interest Rate (in decimal)})\). The sum of weights is \(\sum(B2:B3)\) (the total amount of money borrowed).

Step2: Analyze each formula option

• For the formula \(=D4/B4 * 100\): \(D4\) is not a meaningful sum for the numerator of the weighted - average interest rate formula. \(D4\) is not defined in a way that relates to the weighted - product sum.
• For the formula \(=(B2C2 + B3C3)/B4\): \(B2*C2\) and \(B3*C3\) do not convert the percentage interest rates to decimals (since \(C2 = 4.5\) and \(C3 = 5.5\) are percentages).
• For the formula \(=\text{SUM}(D2:D3)/\text{SUM}(B2:B3)\): \(D2=B2\times(C2/100)\) (converts the percentage interest rate \(C2\) to a decimal and multiplies by the amount \(B2\)), \(D3=B3\times(C3/100)\). \(\text{SUM}(D2:D3)\) gives \(\sum(\text{Amount}\times\text{Interest Rate (in decimal)})\) and \(\text{SUM}(B2:B3)\) gives the total amount. This is the correct weighted - average (in decimal) formula.
• For the formula \(=D3/D4\): \(D3\) and \(D4\) are not defined in a way that represents the relationship for the weighted - average interest rate. \(D4\) is not the correct total for the denominator and \(D3\) is only part of the numerator sum.

Answer:

\(=\text{SUM}(D2:D3)/\text{SUM}(B2:B3)\)