We leamed how to use the Scenario Manager to view the results of a model when multiple assumptions are changed. Using the Assumptions below, build the pro forma (in yellow) model and then run the Scenarios using the the inputs in green below the pro forma. Your answer should show the After Tax Profits per Year (Summary range should be B25:F25). Assumption Tax Rate Unit Sales Sales Growth Year 1 Price Year 1 Cost Cost Growth Rate 3 Price Growth Rate Unit Sales 3 unit price e unit cost Revenues Costs 3 Before Tax Profits Tax 5 After Tax Profits Range name taxes Yearlunits salesgrowth Yeartprice Yeartcost costgrowth pricegrowth 1 200,000 $75.00 $60.00 $0 $0 18.0% 200,000 3.5% $75.00 $60.00 3.0% 5.0% SO SO Year 3 $0 $0 SO $0 Formula hints: Prior year Sales*(1+unit sales growth rate) Prior year Price*(1+price growth rate) Prior year Cost*(1+cost growth rate) Units Unit Price Units Unit Cost $0 Revenues-Costs Before Tax Profits Tax Rate $0 Before Tax Profits - Tax
We leamed how to use the Scenario Manager to view the results of a model when multiple assumptions are changed. Using the Assumptions below, build the pro forma (in yellow) model and then run the Scenarios using the the inputs in green below the pro forma. Your answer should show the After Tax Profits per Year (Summary range should be B25:F25). Assumption Tax Rate Unit Sales Sales Growth Year 1 Price Year 1 Cost Cost Growth Rate 3 Price Growth Rate Unit Sales 3 unit price e unit cost Revenues Costs 3 Before Tax Profits Tax 5 After Tax Profits Range name taxes Yearlunits salesgrowth Yeartprice Yeartcost costgrowth pricegrowth 1 200,000 $75.00 $60.00 $0 $0 18.0% 200,000 3.5% $75.00 $60.00 3.0% 5.0% SO SO Year 3 $0 $0 SO $0 Formula hints: Prior year Sales*(1+unit sales growth rate) Prior year Price*(1+price growth rate) Prior year Cost*(1+cost growth rate) Units Unit Price Units Unit Cost $0 Revenues-Costs Before Tax Profits Tax Rate $0 Before Tax Profits - Tax
Chapter4A: Nopat Breakeven: Revenues Needed To Cover Total Operating Costs
Section: Chapter Questions
Problem 1EP
Related questions
Question
Transcribed Image Text:1 We learned how to use the Scenario Manager to view the results of a model when multiple
2 assumptions are changed. Using the Assumptions below, build the pro forma (in yellow) model and then run the
3
Scenarios using the the inputs in green below the pro forma. Your answer should show the After Tax Profits per Year (Summary range should be B25:F25).
4
5
6
Assumption
7
Tax Rate
8
Unit Sales
9 Sales Growth
10 Year 1 Price
11 Year 1 Cost
12 Cost Growth Rate
13 Price Growth Rate
14
15
16
17 Unit Sales
18 unit price
19 unit cost
20
21 Revenues
22 Costs
23 Before Tax Profits
24 Tax
25 After Tax Profits
26
27
28 Scenario
29 Accelerated Growth
30 Sustainable Growth
31 Moderate Growth
32 Moderate Decline
33 Accelerated Decline
34
35
Range name
taxes
Year1 units
salesgrowth
Year1 price
Year1 cost
costgrowth
pricegrowth
1
200,000
$75.00
$60.00
$0
$0
Unit Sales
300,000
250,000
225,000
180,000
165,000
18.0%
200,000
3.5%
$75.00
$60.00
2
3.0%
5.0%
$0
$0
Year
3
$0
$0
Sales Growth Cost Growth
10.0%
6.0%
3.0%
-1.0%
-6.0%
4.0%
3.0%
2.5%
4.0%
10.0%
$0
$0
Price Growth
6.0%
3.0%
1.0%
-1.0%
-3.0%
5
Formula hints:
Prior year Sales * (1+unit sales growth rate)
Prior year Price * (1+price growth rate)
Prior year Cost* (1+cost growth rate)
Units * Unit Price
Units Unit Cost
$0 Revenues - Costs
Before Tax Profits Tax Rate
$0 Before Tax Profits - Tax
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 3 steps with 3 images
Recommended textbooks for you