Homework Help is Here – Start Your Trial Now!
Business
Accounting
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

FINANCIAL ACCOUNTING
FINANCIAL ACCOUNTING
10th Edition
ISBN:9781259964947
Author:Libby
Publisher:Libby
Chapter1: Financial Statements And Business Decisions
Section: Chapter Questions
Problem 1Q
See similar textbooks
icon
Related questions
Question
### Scenario Analysis for Financial Projections #### Introduction This worksheet demonstrates how to use the Scenario Manager in a spreadsheet to evaluate different financial outcomes based on varying assumptions. #### Key Components 1. **Assumptions** - **Tax Rate**: 18.0% - **Unit Sales**: 200,000 - **Sales Growth**: 5.0% - **Year 1 Price**: $75.00 - **Year 1 Cost**: $60.00 - **Cost Growth Rate**: 3.0% - **Price Growth Rate**: 5.0% #### Financial Model The financial model is calculated over five years with key variables: - **Unit Sales**: Begins at 200,000. - **Unit Price**: Starts at $75.00. - **Unit Cost**: Initially $60.00. - **Revenues** and **Costs** are calculated based on the growth rates provided. **Formula Hints**: - Yearly Sales = Prior Year Sales * (1 + Unit Sales Growth Rate) - Yearly Price = Prior Year Price * (1 + Price Growth Rate) - Yearly Cost = Prior Year Cost * (1 + Cost Growth Rate) - **Revenues** = Units * Unit Price - **Costs** = Units * Unit Cost - **Before Tax Profits** = Revenues - Costs - **After Tax Profits** = Before Tax Profits * (1 - Tax Rate) #### Scenarios The model includes several scenarios with varying growth rates: - **Accelerated Growth**: - Unit Sales Growth = 6.0% - Sales Growth = 4.0% - Cost Growth = 6.0% - Price Growth = 6.0% - **Sustainable Growth**: - Unit Sales Growth = 6.0% - Sales Growth = 3.0% - Cost Growth = 2.0% - Price Growth = 3.0% - **Multi Yearf**: - Unit Sales Growth = 6.0% - Sales Growth = 2.5% - Cost Growth = 0.8% - Price Growth = 1.0% - **Moderate Decline**: - Unit Sales Growth = 5.0% - Sales Growth
Transcribed Image Text:### Scenario Analysis for Financial Projections #### Introduction This worksheet demonstrates how to use the Scenario Manager in a spreadsheet to evaluate different financial outcomes based on varying assumptions. #### Key Components 1. **Assumptions** - **Tax Rate**: 18.0% - **Unit Sales**: 200,000 - **Sales Growth**: 5.0% - **Year 1 Price**: $75.00 - **Year 1 Cost**: $60.00 - **Cost Growth Rate**: 3.0% - **Price Growth Rate**: 5.0% #### Financial Model The financial model is calculated over five years with key variables: - **Unit Sales**: Begins at 200,000. - **Unit Price**: Starts at $75.00. - **Unit Cost**: Initially $60.00. - **Revenues** and **Costs** are calculated based on the growth rates provided. **Formula Hints**: - Yearly Sales = Prior Year Sales * (1 + Unit Sales Growth Rate) - Yearly Price = Prior Year Price * (1 + Price Growth Rate) - Yearly Cost = Prior Year Cost * (1 + Cost Growth Rate) - **Revenues** = Units * Unit Price - **Costs** = Units * Unit Cost - **Before Tax Profits** = Revenues - Costs - **After Tax Profits** = Before Tax Profits * (1 - Tax Rate) #### Scenarios The model includes several scenarios with varying growth rates: - **Accelerated Growth**: - Unit Sales Growth = 6.0% - Sales Growth = 4.0% - Cost Growth = 6.0% - Price Growth = 6.0% - **Sustainable Growth**: - Unit Sales Growth = 6.0% - Sales Growth = 3.0% - Cost Growth = 2.0% - Price Growth = 3.0% - **Multi Yearf**: - Unit Sales Growth = 6.0% - Sales Growth = 2.5% - Cost Growth = 0.8% - Price Growth = 1.0% - **Moderate Decline**: - Unit Sales Growth = 5.0% - Sales Growth
Expert Solution
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 3 steps with 3 images

SEE SOLUTIONCheck out a sample Q&A here
Blurred answer
Similar questions
Recommended textbooks for you
FINANCIAL ACCOUNTING
FINANCIAL ACCOUNTING
Accounting
ISBN:
9781259964947
Author:
Libby
Publisher:
MCG
Accounting
Accounting
Accounting
ISBN:
9781337272094
Author:
WARREN, Carl S., Reeve, James M., Duchac, Jonathan E.
Publisher:
Cengage Learning,
Accounting Information Systems
Accounting Information Systems
Accounting
ISBN:
9781337619202
Author:
Hall, James A.
Publisher:
Cengage Learning,
Horngren's Cost Accounting: A Managerial Emphasis…
Horngren's Cost Accounting: A Managerial Emphasis…
Accounting
ISBN:
9780134475585
Author:
Srikant M. Datar, Madhav V. Rajan
Publisher:
PEARSON
Intermediate Accounting
Intermediate Accounting
Accounting
ISBN:
9781259722660
Author:
J. David Spiceland, Mark W. Nelson, Wayne M Thomas
Publisher:
McGraw-Hill Education
Financial and Managerial Accounting
Financial and Managerial Accounting
Accounting
ISBN:
9781259726705
Author:
John J Wild, Ken W. Shaw, Barbara Chiappetta Fundamental Accounting Principles
Publisher:
McGraw-Hill Education
SEE MORE TEXTBOOKS