Unit 11 of 12

Business math

Growth, margins, loans, and savings.

Key ideas

=(C2-B2)/B2

New minus old, divided by old. Format it as a percent.

=PMT(rate/12,years*12,amount)

Use monthly rate and number of months. The result is negative because money goes out.

=SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5)

Multiply each score by its weight, add them up, divide by total weight.

Find the growth from last year to this year as a decimal in D2. Fill to D5.
A B C D
1 Product Last year This year Growth
2 Notebook 4000 4600
3 Pen 2500 2250
4 Stapler 1200 1500
5 Binder 800 880
Hint: =(C2-B2)/B2
Show answer
=(C2-B2)/B2 in D2. 0.15 means 15% growth. The Pen shrank by 10%.
What will A1 show?
A
1
A1 contains =(120-100)/100
Show answer
0.2. 20 more on a base of 100 is 0.2, or 20%.
The discount in F1 applies to every price. Write the sale price in C2 so it fills to C5.
A B C D E F
1 Product Price Sale price Discount 0.15
2 Lamp 40
3 Desk 220
4 Chair 150
5 Rug 90
Hint: Pay 85% of the price: =B2*(1-$F$1)
Show answer
=B2*(1-$F$1) in C2. Lock the discount cell so every row shares it.
Price is $50 and cost is $30. What is the profit margin?
- 40%
- 66.7%
- 20%
- 60%
Show answer
40%. Margin is profit divided by price: 20 / 50.
Calculate the profit margin (profit divided by price) in D2. Fill to D4.
A B C D
1 Item Price Cost Margin
2 Mug 18 7.2
3 Tote 25 11
4 Cap 22 13.2
Hint: =(B2-C2)/B2
Show answer
=(B2-C2)/B2 in D2. Margin is on price. Markup would be on cost.

	A
1

Why does PMT return a negative number?
- It is money leaving your pocket
- It is an error
- Interest is negative
- The loan is paid off
Show answer
It is money leaving your pocket. Excel uses signs for cash direction. Wrap it in -PMT() or ABS() to show it positive.
What is the monthly payment on this loan? Put it in B4.
A B
1 Annual rate 0.06
2 Years 5
3 Amount 25000
4 Payment
Hint: Monthly rate and months: =PMT(B1/12,B2*12,B3)
Show answer
=PMT(B1/12,B2*12,B3) in B4. About $483 a month. It shows negative because it is a payment.
Saving $200 a month at 5% a year for 10 years. How much will you have? Put it in B4.
A B
1 Annual rate 0.05
2 Years 10
3 Monthly deposit 200
4 Future value
Hint: Deposits go out, so make them negative: =FV(B1/12,B2*12,-B3)
Show answer
=FV(B1/12,B2*12,-B3) in B4. About $31,000 from $24,000 of deposits. That is compound interest.
What will A1 show?
A
1
A1 contains =PMT(0,10,1000)
Show answer
-100. With no interest, $1000 over 10 payments is $100 each, shown negative.
Build the monthly loan payment
Show answer
=PMT(B1/12,B2*12,B3). Always match the rate period to the payment period.

	A
1

Calculate the weighted course grade in B6.
A B C
1 Assignment Score Weight
2 Homework 92 0.2
3 Midterm 78 0.3
4 Project 88 0.2
5 Final 84 0.3
6 Grade
Hint: =SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5)
Show answer
=SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5) in B6. A plain AVERAGE would treat homework the same as the final.
Total order value is quantity times price for every line, added up. Put it in C7 with one formula.
A B C
1 Item Qty Price
2 Pens 40 1.25
3 Pads 25 3.5
4 Ink 6 18
5 Tape 12 2.75
6
7 Total
Hint: =SUMPRODUCT(B2:B5,C2:C5)
Show answer
=SUMPRODUCT(B2:B5,C2:C5) in C7. No helper column needed.
What does SUMPRODUCT(A,B) do?
- Multiplies matching items, then adds the results
- Adds both ranges, then multiplies
- Multiplies the two totals
- Counts matching items
Show answer
Multiplies matching items, then adds the results. A1*B1 + A2*B2 + A3*B3 and so on.
Build a running total of sales in C2 that fills down to C6.
A B C
1 Month Sales Running total
2 Jan 1200
3 Feb 950
4 Mar 1400
5 Apr 1100
6 May 1650
Hint: Lock the start of the range: =SUM($B$2:B2)
Show answer
=SUM($B$2:B2) in C2. Same expanding range trick as duplicates.
What will A1 show?
A
1
A1 contains =ROUND(1234.567,-2)
Show answer
1200. Negative digits round to the left of the decimal: hundreds.