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 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 company’s soleproduct- solar panels. Much of the company’s 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 company’s 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 2018’s 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 company’s 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
   
   
   
   
   

Answer 1

Working Note 1-	Income Statement
	Particulars	Amount($)
	Sales	$    80,000,000.00
Less:-	Variable Cost
	Manufacturing Cost	$ (55,000,000.00)
	Selling and Admn Cost	$ (15,000,000.00)
	Contribution	$    10,000,000.00
Less:-	Fixed Cost-
	Manufacturing Cost	$    (1,724,000.00)
	Selling and Admn Cost	$       (780,000.00)
	Net Income before taxes	$      7,496,000.00

1	Following calculations for the year ended 2018
A	Contribution Margin:-	=	$                                                                 10,000,000.00
B	Break Even in Units:-	=	Fixed Cost
			Contribution per unit
		=	$                                                                    2,504,000.00
			$                                                                                  50.00
		=	50080 Units
	Where Contribution per unit	=	Total Contribution
			Total No. of unit
		=	$                                                                 10,000,000.00
			$                                                                       200,000.00
		=	$                                                                                  50.00
C	Break Even in dollars:-	=	Total Fixed Cost
			Contribution Margin Ratio
		=	$                                                                    2,504,000.00
			12.50%
		=	$                                                                 20,032,000.00
	Where Contribution Margin Ratio	=	Total Contribution	*100
			Sales
		=	$                                                                 10,000,000.00	*100
			$                                                                 80,000,000.00
		=	12.50%
D	Total Sales:-	=	Total Sales Volume *Sales Price per unit
		=	200,000 Solar panels * $ 400 per panel
		=	$                                                                 80,000,000.00
E	Income before taxes:-	=	$                                                                    7,496,000.00
	( Refer Working Note 1)
F	Margin of Safety in Dollars:-	=	Actual sales - Break even Sales
	( For Actual Sales refer point D and for Break even Sales refer point C)	=	$ 80,000,000.00 - $ 20,032,000.00
		=	$                                                                 59,968,000.00
F	Margin of Safety in Units:-	=	Actual Unit Sold - Break even point in units
	( For Actual Sales Unit refer question and for Break even Units refer point B)	=	200,000 Solar panels - 50,080 Solar panels
		=	149,920 Solar panels
H	Degree of Operating Leverage:-	=	Total Contribution Margin
	( Since there is no interest and taxes , So net income as calculated in the working note 1 shall be treated as Net Operating Income before interest and tax)		Net Operating Income before interest and tax
		=	$                                                                 10,000,000.00
			$                                                                    7,496,000.00
		=	1.33

2	Following calculations for the Project Year 2019 :-

We have selected the Scenario 3:  The “go green” mantra catches on (supported by government consumer tax credits) increasing demand for the company’s solar panels. So in the project year 2019, it is assumed that due to this scenario sales would increase to the maximum capicity of 220,000 Solar panels. and all other things will remain same

Working Note 2-	Income Statement (220,000 Solar Panels)
	Particulars	Amount($)
	Sales	$    88,000,000.00
Less:-	Variable Cost
	Manufacturing Cost	$ (60,500,000.00)
	Selling and Admn Cost	$ (16,500,000.00)
	Contribution	$    11,000,000.00
Less:-	Fixed Cost-
	Manufacturing Cost	$    (1,724,000.00)
	Selling and Admn Cost	$       (780,000.00)
	Net Income before taxes	$      8,496,000.00

1	Following calculations for the project year 2019
A	Contribution Margin:-	=	$                                                                 11,000,000.00
	( Refer Working Note 2)
B	Break Even in Units:-	=	Fixed Cost
			Contribution per unit
		=	$                                                                    2,504,000.00
			$                                                                                  50.00
		=	50080 Units
	Where Contribution per unit	=	Total Contribution
			Total No. of unit
		=	$                                                                 11,000,000.00
			$                                                                       220,000.00
		=	$                                                                                  50.00

C	Break Even in dollars:-	=		Total Fixed Cost
				Contribution Margin Ratio
	= $                                                                    2,504,000.00 12.50% = $                                                                 20,032,000.00 Where Contribution Margin Ratio = Total Contribution Sales = $                                                                 11,000,000.00 $                                                                 88,000,000.00 = 12.50% D Total Sales:- = Total Sales Volume *Sales Price per unit = 220,000 Solar panels * $ 400 per panel = $                                                                 88,000,000.00
E	Income before taxes:-	=	$                                                                    8,496,000.00
	( Refer Working Note 1)
F	Margin of Safety in Dollars:-	=	Actual sales - Break even Sales
	( For Actual Sales refer point D and for Break even Sales refer point C)	=	$ 88,000,000.00 - $ 20,032,000.00
		=	$                                                                 67,968,000.00
F	Margin of Safety in Units:-	=	Actual Unit Sold - Break even point in units
	( For Actual Sales Unit refer question and for Break even Units refer point B)	=	220,000 Solar panels - 50,080 Solar panels
		=	169,920 Solar panels
Note-	As management is under pressure to deliver an increase of 8 % over 2018’s Income Before Taxes.
1	Income Before Taxes.( Project Year 2019) ( refer WN 2)		$                                                                    8,496,000.00
2	Income Before Taxes.( Year 2018) ( refer WN 1)		$                                                                    7,496,000.00
3	% Increase in Income before taxes (1-2/2)*100		13.34%