Question
ACC 154- Managerial Accounting Cost-Volume-Profit Analysis Excel Assignment Introduction: Congratulations! You have finally been promoted to the position of Division Manager with your long time
ACC 154- Managerial Accounting
Cost-Volume-Profit Analysis Excel Assignment
Introduction:
Congratulations! You have finally been promoted to the position of Division Manager with your long time employer Solar Co., a manufacturer of solar panels. It is now your responsibility to take the lead for corporate planning, analysis and oversight of the companys soleproduct- solar panels. Much of the companys future success or failure rides with your insight and leadership.
Your analysis for the coming year is extremely challenging given current economic conditions, random government programs and competition from lower cost non-U.S. manufacturers. You will be required to forecast changes in the companys fixed and variable costs for 2019 to determine its break even point, and then proceed to completing its Master Budget for 2016 based upon these forecasts.
As a wholly owned subsidiary of a large diversified company, management is under pressure to deliver an increase of 8 % over 2018s Income Before Taxes.
Situation as of Year-End 2018:
Sales Price: 1 (one) Solar Panel sells for $400.
Sales Volume: 200,000 Solar Panels were sold this past year.
Current capacity: 220,000 units (there is no plan (or budget) to expand operating capacity for 2018)
Fixed Costs:
ManufacturingOverhead:
Facility Rent:$400,000
Management Salaries: $1,200,000
Depreciation, machinery: $40,000
Maintenance and repair: $60,000
Other overhead expenses: $24,000
Selling and Administrative Costs:
Taxes and insurance: $600,000
Sales salaries: $180,000
Variable Costs:
Manufacturing Costs:
Materials: $180 per Solar Panel
Labor:$70 per Solar Panel
Employee Benefits: $20 per Solar Panel
Utilities: $5 per Solar Panel
Selling and Administrative Costs:
Delivery expenses: $60 per Solar Panel
Sales commissions: $15 per Solar Panel
You have pondered the future for Solar Co. and have identified three very possible scenarios in which your company may have to operate:
Scenario 1: The government institutes a program to support wind power that makes wind power relatively less expensive than other sources of energy.
Scenario 2: One of raw materials used to make a solar panel increases dramatically forcing Solar Co. to increase their selling price per unit.
Scenario 3: The go green mantra catches on (supported by government consumer tax credits) increasing demand for the companys solar panels.
REQUIRED: Cost-Volume-Profit Analysis Current vs Projected
1. 2018 CVP:Using the Excel spreadsheet file perform the following calculations using the information as of the end of 2018:
(8 Points)
- Contribution Margin
- Break Even in Units
- Break Even in Dollars
- Total Sales
- Income Before Taxes
- Margin of Safety in Dollars
- Margin of Safety in Units
- Degree of Operating Leverage
2. Projected 2019 CVP:Selectone of the three scenarios on the previous page and then provide the following projected calculations for 2019:
(8 Points)
- Contribution Margin
- Break Even in Units
- Break Even in Dollars
- Total Sales
- Income Before Taxes
- Margin of Safety in Dollars
- Margin of Safety in Units
- Degree of Operating Leverage
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started