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)

1. Contribution Margin
2. Break Even in Units
3. Break Even in Dollars
4. Total Sales
5. Income Before Taxes
6. Margin of Safety in Dollars
7. Margin of Safety in Units
8. 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)

1. Contribution Margin
2. Break Even in Units
3. Break Even in Dollars
4. Total Sales
5. Income Before Taxes
6. Margin of Safety in Dollars
7. Margin of Safety in Units
8. Degree of Operating Leverage