LESSON 1: In this lesson, we will use good
formatting practices for Excel to set up a basic
spreadsheet model for a business's profits
Randy is a t-shirt vendor. The fixed cost of any order is $750,
the variable cost per T-shirt to Randy is $8, and Randy’s selling
price is $18. However, this price will be charged only until a
week after the tournament. After that time, Randy figures that
the interest in the t-shirts will be low, so he plans to sell all
remaining t-shirts, if any, at $6 each. His best guess is that the
demand for the t-shirts during the full price period will be
1500. He is thinking about ordering 1450 t-shirts, but he wants
to build a spreadsheet model that will let him experiment with
the uncertain demand and his order quantity. How should he
proceed?
LESSON 2: In this lesson, we will use tables and
charts in Excel to model cost projections
numerically and visually for managers
The Woodworks Company produces a variety of custom-
designed wood furniture, made from either cherry or oak. The
company knows that wood prices and labor costs are likely to
increase in the future. The table below shows the number of
board-feet and labor hours required for a bookshelf, the current
costs per board-foot and labor hour, and the anticipated annual
increases in these costs.
Build a spreadsheet model that enables the company to
experiment with the growth rates in wood and labor costs so
that a manager can see, both numerically and graphically, how
the costs of bookshelves vary in the next few years.
NCAA T-Shirt Vendor
Costs
Fixed cost $750
Variable cost $8
Revenue
Selling price ₫18
Reduced price $6
Number of shirts orde 1650
Demand 1500
Number of shirts sold 1500
Number of shirts sold 150
Total cost $13,950
Total revenue $27,900
Total Profit $13,950
the fomula max(B11-B12, 0) guarantee B15 will no
Number of shirt ordered > D
D > the number of shirts ordered
D = the number of shirts ordered
Woodworks Bookshelf Co.
Costs Cherry
Unit Cost $ 5.50
Board feet 30
Materials Cost $ 165
Labor Required 16
Unit Cost $ 18.50
Labor Cost $ 296
Total Cost $ 461
Cost Increases 2.40%
Year Cherry
0 $ 165
1 $ 168.96
2 $ 173.02
3 $ 177.17
4 $ 181.42
5 $ 185.77
$600
$500
$400
$300
$500
$400
$300
$200
$100
$-
0 1
Che
per shirt
per shirt
per shirt
=MIN(D12:D13) Sell the smaller
=MAX(D12-D13,0) To not be negative
guarantee B15 will not be negative considering these situations:
Oak Labor
$ 4.30
30
$ 129
16
$ 18.50
$ 296
$ 425
1.70% 1.50%
Total cost
Oak Labor Cherry Oak
$ 129 $ 296 $ 461 $ 425
$ 131.19 $ 300.44 $ 469.4 $ 431.6
$ 133.42 $ 304.95 $ 478.0 $ 438.4
$ 135.69 $ 309.52 $ 486.7 $ 445.2
$ 138.00 $ 314.16 $ 495.6 $ 452.2
$ 140.34 $ 318.88 $ 504.6 $ 459.2
Chart Title
1 2 3 4 5
Axis Title
Cherry Oak Labor Cherry Oak
=D48*(1+$D$44) Increase= 1 + the rate increase
5