3. For the lemonade stand example in Chapter 17, "Sensitivity Analysis with Data Tables," use the Scenario Manager to display a report summarizing profit for the following scenarios: Scenario Price Unit cost Fixed cost High cost/high price $5.00 $1.00 $65,000.00 Medium cost/medium price $4.00 $0.75 $45,000.00 Low cost/low price $2.50 $0.40 $25,000.00
3. For the lemonade stand example in Chapter 17, "Sensitivity Analysis with Data Tables," use the Scenario Manager to display a report summarizing profit for the following scenarios: Scenario Price Unit cost Fixed cost High cost/high price $5.00 $1.00 $65,000.00 Medium cost/medium price $4.00 $0.75 $45,000.00 Low cost/low price $2.50 $0.40 $25,000.00
Essentials Of Investments
11th Edition
ISBN:9781260013924
Author:Bodie, Zvi, Kane, Alex, MARCUS, Alan J.
Publisher:Bodie, Zvi, Kane, Alex, MARCUS, Alan J.
Chapter1: Investments: Background And Issues
Section: Chapter Questions
Problem 1PS
Related questions
Question
Calculation Need to be done in Excel Format.
Transcribed Image Text:Microsoft Excel 2010: Data Analysis and Business Modeling
F
1
price
4.00
2
demand
29000
$
$ 45,000.00
#####HHHHH
unit cost
0.45
4
fixed cost
revenue
variable cost $ 13,050.00
profit
6.
7
$ 57,950.00
8.
9.
profit
revenue
variable cost
10 price
57950
116000
13050
1.00 $ (14,200.00) $ 56,000.00 $ 25,200.00
(2,000.00) $ 67,187.50 $ 24,187.50
1.50 $ 9,075.00 $ 77,250.00 $ 23,175.00
1.75 $ 19,025.00 $ 86,187.50 $ 22,162.50
2.00 $ 27,850.00 $ 94,000.00 $ 21,150.00
2.25 $ 35,550.00 $ 100,687.50 $ 20,137.50
2.50 $ 42,125.00 $ 106,250.00 $ 19,125.00
2.75 $ 47,575.00 $ 110,687.50 $ 18,112.50
3.00 $ 51,900.00 $ 114,000.00 $ 17,100.00
3.25 $ 55,100.00 $ 116,187.50
3.50 $ 57,175.00 $ 117,250.00 $ 15,075.00
3.75 $ 58,125.00 $ 117,187.50 $ 14,062.50
4.00 $ 57,950.00 $ 116,000.00 $ 13,050.00
11
12
1.25
13
14
$
15
16
17
18
19
$
$
$ 16,087.50
20
21
22
23
FIGURE 17-4 One-way data table with varying prices.
H
9
unit cost
10
0.40 $
1.50 $ 16,800.00 $ 14,225.00 $11,650.00 $ 9,075.00 $ 6,500.00 $ 3,925.00 $ 1,350.00
1.75 $26,412.50 $23,950.00 $21,487.50 $ 19,025.00 $ 16,562.50 $ 14,100.00 $11,637.50
13 $ 2.00 $ 34,900.00 $32,550.00 $30,200.00 $27,850.00 $25,500.00 $23,150.00 $20,800.00
57950 $
0.30 $
0.35
0.45 $
0.50
%24
0.55 $
0.60
11 $
12 $
$ 35,550.00 $33,312.50 $31,075.00 $28,837.50
14 $ 2.25 $42,262.50
15 $ 2.50 $ 48,500.00 $ 16,375.00 $44,250.00 $ 42,125.00 $40,000.00
16 $ 2.75 $53.612.50 $51,600.00 $49,587.50
17 S 100
18 $ 3.25 $ 60,462.50 $58,675.00
$ 40,025.00
$37,787.50
$ 35,750.00
$47,575.00 $45,562.50
$50,000.00
$53,312.50
$37,875.00
$43,550.00
$48,100.00 $46,200.00
$41,537.50
3.00 $57,600.00 $55,700.00 $53,800.00 $11,900.00
$55,100.00
3.50 $62,200.00 $60,525.00 $58,850.00 $57.175.00 $55,500.00 $53.825.00 $52.150.00
3.75 $62.81250 $61250.00 $59,687.50 $58,125.00 $56,562.50 $55,000.00 $53,437.50
4.00 $62,300.00 $60,850.00 $59,400.00 $57,950.00 $56,500.00 $55,050.00 $53,600.00
4.25 $60,662.50 $59,325.00 $57,987.50 $56,650.00 $55,312.50 $53,975.00 $52,637.50
4.50 $57,900.00 $56,675.00 $55,450.00 $54,225.00 $53,000.00 $51,775.00 $50,550.00
4.75 $54,012.50 $52,900.00 $51,787.50 $50,675.00 $49,562.50 $48,450.00 $47,33I.50
5.00 $49,000.00 $48,000.00 $47,000.00 $ 46,000.00 $ 45,000.00 $44,000.00 $43,000.00
$56,887.50
$51,525.00
$49,737.50
19 $
20 $
21
$
22 $
23 $
24 $
|25 $
FIGURE 17-5 A two-way data table showing profit as a function of price and unit variable cost.
Transcribed Image Text:3. For the lemonade stand example in Chapter 17, "Sensitivity Analysis with Data Tables,"
use the Scenario Manager to display a report summarizing profit for the following
scenarios:
Scenario
Price
Unit cost
Fixed cost
High cost/high price
Medium cost/medium price
Low cost/low price
$5.00
$1.00
$65,000.00
$4.00
$0.75
$45,000.00
$2.50
$0.40
$25,000.00
A
B
C
D
1
price
demand
4.00
29000
unit cost
24
0.45
$ 45,000.00
$ 116,000.00
$ 13,050.00
$ 57,950.00
4
fixed cost
revenue
variable cost
7
profit
FIGURE 17-1 The inputs that change the profitability of a lemonade store.
B
C
F
9.
10 price
profit
revenue
variable cos
57950
116000
13050
1.00 $ (14,200.00) $ 56,000.00 $ 25,200.00
1.25 $ (2,000.00) $ 67,187.50 $ 24,187.50
1.50 $ 9,075.00 $ 77,250.00 $ 23,175.00
1.75 $ 19,025.00 $ 86,187.50 $ 22,162.50
2.00 $ 27,850.00 $ 94,000.00 $ 21,150.00
2.25 $ 35,550.00 $ 100,687.50 $ 20,137.50
2.50 $ 42,125.00 $ 106,250.00 $ 19,125.00
2.75 $ 47,575.00 $ 110,687.50 $ 18,112.50
3.00 $ 51,900.00 $114,000.00 $ 17,100.00
3.25 $ 55,100.00 $116,187.50 $ 16,087.50
3.50 $ 57,175.00 $ 117,250.00 $ 15,075.00
3.75 $ 58,125.00 $117,187.50 $ 14,062.50
4.00 $ 57,950.00 $116,000.00 $ 13,050.00
11
12
13
14
$
2$
$
15
16
17
18
19
20
$
$
21
22
23
FIGURE 17-2 One-way data table with varying prices.
Expert Solution
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
This is a popular solution!
Trending now
This is a popular solution!
Step by step
Solved in 2 steps with 3 images
Recommended textbooks for you
Essentials Of Investments
Finance
ISBN:
9781260013924
Author:
Bodie, Zvi, Kane, Alex, MARCUS, Alan J.
Publisher:
Mcgraw-hill Education,
Essentials Of Investments
Finance
ISBN:
9781260013924
Author:
Bodie, Zvi, Kane, Alex, MARCUS, Alan J.
Publisher:
Mcgraw-hill Education,
Foundations Of Finance
Finance
ISBN:
9780134897264
Author:
KEOWN, Arthur J., Martin, John D., PETTY, J. William
Publisher:
Pearson,
Fundamentals of Financial Management (MindTap Cou…
Finance
ISBN:
9781337395250
Author:
Eugene F. Brigham, Joel F. Houston
Publisher:
Cengage Learning
Corporate Finance (The Mcgraw-hill/Irwin Series i…
Finance
ISBN:
9780077861759
Author:
Stephen A. Ross Franco Modigliani Professor of Financial Economics Professor, Randolph W Westerfield Robert R. Dockson Deans Chair in Bus. Admin., Jeffrey Jaffe, Bradford D Jordan Professor
Publisher:
McGraw-Hill Education