Modeling profit and break even point
1 Quality Sweater Company one-way data table Goal Seek
2 B&N Bookstore two-way data tables Conditional Formatting
3 Screamin' Blue Jays Assignment
What if analysis
Vlookup If Min Max
Symbol Operation
+ (plus sign) Addition
- (minus sign) Subtraction
* (asterisk) Multiplication
/ (forward slash) Division
% (percent sign) Percent (divide by 100)
^ (caret) Exponentiation
Quality Sweater Company
In this lesson, we will explore changes in a business's profits by using what-if analysis tools in Excel including data tables and G
Scenario
The Quality Sweater Company sells hand-knitted sweaters. The company is planning to print a catalog of its products and und
is $0.15. In addition, the company plans to include direct reply envelopes in its mailing and incurs a $.20 in extra costs for each
value – that is $32. The company plans to mail 100,000 catalogs. It wants to develop a spreadsheet model to answer the follo
A. How does a change in the response rate affect profit?
B. For what response rate does the company break even?
C. If the company estimates a response rate of 3%, should it proceed with the mailing?
D. How does the presence of uncertainty affect the usefulness of the model?
printing cost Fixed $ 20,000.00
printing cost Fixed $ 0.10 unit
mailing cost $ 0.15 unit
envelopes cost $ 0.20 Order
Avg order size $ 40.00
variable cost per order $ 32.00 per order
Total mail 100000
Haute Dog
Sales:
Days Open 6
Daily Sales 50
Costs:
Start-up Cost $ 500.00
Variable Cost $ 0.50
Total weekly cost: $ 850.00
Rent 200
Revenue:
Price $ 4.00
Total weekly Revenue $ 1,200.00
Weekly profit: $ 350.00
ts by using what-if analysis tools in Excel including data tables and Goal Seek . Read the problem below carefully. If you’d like, take notes a
s. The company is planning to print a catalog of its products and undertake a direct mail campaign. The cost of printing the catalog is $20,
reply envelopes in its mailing and incurs a $.20 in extra costs for each direct mail envelope used by a respondent. The average size of a cu
atalogs. It wants to develop a spreadsheet model to answer the following questions:
it proceed with the mailing?
ness of the model?
Response rate 5.8%
B. For what response rate does the company break even?
Goal Seek
select your Response rate , Then go to the Da
Forecast group. Click on What-If Analysis and
Look at the pop-up window that appears.
Break even rate 5.8% Set cell: Profit
To value: Zero
By changing cell: Response Rate
days per week
hot dogs per day
per hot dog
=C42+C45+C38*C39*C43
per hot dog
=C49*C39*C38
=C50-C44
blem below carefully. If you’d like, take notes and start setting up a spreadsheet model of your own. Then, watch the next video in the cou
paign. The cost of printing the catalog is $20,000 plus $.10 per catalog. The cost of mailing each catalog (including postage, order forms,
ed by a respondent. The average size of a customer order is $40, and the company’s variable cost per order (primarily due to labor and m
A. How does a change in the response rate affect p
C. If the company estimates a response rate of 3%
Data Table Profit
ghost cell $ -
Total order 5769.230769231 =E7*B19 3%
Total Revenue $ 230,769.23 =H7*B15 3.50%
Total Cost $ 230,769.23 =B7+(B8+B9)*B19+(B10+B16)*H7 4%
Profit $ - =H8-H9 4.50%
5%
5.50%
6%
6.50%
7%
7.50%
8%
8.50%
Goal Seek
select your Response rate , Then go to the Data Tab and locate What-If Analysis in the
Forecast group. Click on What-If Analysis and select Goal Seek
Look at the pop-up window that appears.
Set cell: Profit
To value: Zero
By changing cell: Response Rate
h the next video in the course to see a solution.
ding postage, order forms, and buying names from a mail-order database)
rimarily due to labor and material costs) averages about 80% of the order’s
n the response rate affect profit?
mates a response rate of 3%, should it proceed with the mailing?
DATA TABLE
select your table, including the top row with the grayed-out cell. Then go to the Data
Tab and locate What-If Analysis in the Forecast group. Click on What-If Analysis and
select Data Table.
Look at the pop-up window that appears. You should see two fields in the pop-up:
Row input cell-NA
Column input cell - Respnse Rate
Response rate vs profit
$12
$10
$8
$6
$4
$2
$-
3% 4% 4% 5% 5% 6% 6% 7% 7% 8% 8% 9%
B&N Bookstore
In the previous example, you created a one-way data table to show how variations in one input affected a formula. Using a sim
In this example, we will use two-way data tables to explore uncertain demand. We will also introduce some new and useful Ex
solution.
Scenario
B&N Bookstore, with many locations across the United States, places orders for all the latest books and then distributes them
for $30. It can purchase any number of this book from the publisher, but due to quantity discounts, the unit cost for all books
copies; to $22.25 for at least 2000; to $21.75 for at least 3000; and to $21.30 (the lowest possible unit cost) for at least 4000.
come out in paperback. Therefore, if B&N has any hardbacks left when the paperback comes out, it will put them on sale for $
How many copies of this hardback novel should B&N order from the publisher?
B&N Bookstore
hardback novel 30 Costs 21.3
TOQ Total cost 85200
0 24
1000 23 Rev 100000
2000 22.25
3000 21.75 Profit 14800
4000 21.3
hardback pre 30
hardback post 10
Total Demand 3000 Range-500-4500
Total Order 4000
Surplus 1000 =MAX(B18-B17,0)
Way2 1000 =IF((B18-B17)>0,(B18-B17),0)
Copy shop
No of copies 3
Copy Rent 5000 Year
Copy cost 0.03 Per copy
store Rent 400 Month
Charge 0.1 Per copy
Days open 365 Days
Capacity 300000 Year
Demand 775 500 to 2000 per day
Annual Demand 282857
Total copies 282857.1429
Annual Cost 28285.71429 =B28*B27+B29*B40+B30*12
Revenue
Total Revenue 28285.71429 Year
Annual Profit 0 Year
s in one input affected a formula. Using a similar process, you can also build two-way data tables, with both a row input and a column inpu
e will also introduce some new and useful Excel functions. Read the problem below carefully. If you’d like, take notes and start setting up a
the latest books and then distributes them to its individual bookstores. B&N needs a model to help it order the appropriate number of an
uantity discounts, the unit cost for all books it orders depends on the number ordered. Specifically, if the number ordered is less than 100
owest possible unit cost) for at least 4000. B&N is very uncertain about the demand for this book – it estimates that demand could be an
back comes out, it will put them on sale for $10, at which price it believes all the leftovers will be sold.
Order Dem
14800 500 1000 1500
500
=VLOOKUP(B18,A7:B12,2,TRUE()) 1000
=F6*B18 1500
2000
=MIN(B17:B18)*B15+B20*B16 2500
3000
=F9-F7 3500
4000
4500
DATA TABLE
select your table, including the top row with the grayed-out cell. Then
Click on What-If Analysis and select Data Table.
Look at the pop-up window that appears. You should see two fields in
Row input cell-Demand Column input cell - Order
# copy Demand
0 500 1000 1500 2000
1
2
3
4
input and a column input, to show how variations in two quantities jointly affect the value of a formula.
tes and start setting up a spreadsheet model of your own. Then, watch the next video in the course to see a
appropriate number of any title. For example, B&N plans to order a popular new hardback novel, which it will sell
ordered is less than 1000, the unit cost is $24. After each 1000, the unit costs drops: to $23 for at least 1000
hat demand could be anywhere from 500 to 4500. Also, as with most hardback novels, this one will eventually
Demand estimation
2000 2500 3000 3500 4000 4500
he grayed-out cell. Then go to the Data Tab and locate What-If Analysis in the Forecast group.
e.
should see two fields in the pop-up:
Order
Assignment -Screamin' Blue Jays
Number of Concerts
Concerts per week: 5
Weeks on tour 50
Total concerts: 250 =B4*B5
Expected Concert Attendance
Maximum capacity: 800
Attendance (%): 90%
Number of tickets sold: Costs 720 =B9*B10
Cost
Fixed start-up cost: $ 5,500,000.00
Venue rental cost: $ 1,000.00 per show
Total cost: $ 5,750,000.00 =B14+B15*B6
Revenue
Ticket price: Concession sales: $ 55.00 Per person
Concession sales: $ 1.50 Per person
Total Revenue $ 10,170,000.00
Profit $ 4,420,000.00 5324000
One way sheet
Seat sold profit
$ 4,420,000
40%
50%
60%
70%
80%
90%
100%
110%
120%
two way sheet
$ 4,420,000.00 $ 40 $ 45 $ 50 $ 55 $ 60 $ 65
30
35
40
45
50
55
