Budgeting and CVP Analysis In Excel, make up a production company (and product produced) Create a one year monthly and annual budget, in the format of a Contribution Margin financial statement on an excel sheet and name the excel tab, Budget Be sure its realistic and includes a mix of variable cost per unit as well as fixed costs in the $100,000 $1,000,000 range This budget should be driven by inputs that can be changed easily and carry through to the reporting side of the model The drivers (assumptions) will include Sales units, Sales price per unit, and Variable costs per unit (Driven by inputs means do not calculate numbers on a calculator and then input the result into the excel sheet ) Include below the budget the, Contribution Margin ratio, Break Even in units and Break Even in Sales, Margin of Safety, Degree of Operating Leverage Copy the budget excel tab and name the tab, Budget 2 Refer to the original data The president is convinced that a 10 reduction in the selling price, combined with an increase of $25,000 in the monthly advertising budget, will double UNIT sales What will the new contribution format income statement budget look like if these changes are adopted Copy the budget excel tab and name the tab, Budget 3 Refer to the original data By automating, the company could slash its variable cost per unit in half However, fixed costs would increase by $100,000 per month Compute the new CM ratio and the new break even point in both units and dollars Formulas Sales Variable Expenses Contribution Margin Contribution Margin Fixed Expenses Net Operating Income Contribution Margin Ratio Contribution Margin per unit (expressed as decimal or ) Sales Price per unit Total Contribution Margin Sales x Contribution Margin Ratio Break Even in Units Fixed Expenses Contribution Margin per Unit Break Even in Sales Fixed Expenses Contribution Margin Ratio Margin of Safety Total Sales Break Even Sales Degree of Operating Leverage Contribution Margin Net Operating Income

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on May 31, 2024

Budgeting and CVP Analysis In Excel, make up a production company (and product produced). Create a one year monthly and annual budget, in the format

Budgeting and CVP Analysis

In Excel, make up a production company (and product produced). Create a one year monthly and annual budget, in the format of a Contribution Margin financial statement on an excel sheet and name the excel tab, Budget. Be sure its realistic and includes a mix of variable cost per unit as well as fixed costs in the $100,000 - $1,000,000 range.

This budget should be driven by inputs that can be changed easily and carry through to the reporting side of the model. The drivers (assumptions) will include Sales units, Sales price per unit, and Variable costs per unit. (Driven by inputs means do not calculate numbers on a calculator and then input the result into the excel sheet.)

Include below the budget the, Contribution Margin ratio, Break Even in units and Break Even in Sales, Margin of Safety, Degree of Operating Leverage.

Copy the budget excel tab and name the tab, Budget 2. Refer to the original data. The president is convinced that a 10% reduction in the selling price, combined with an increase of $25,000 in the monthly advertising budget, will double UNIT sales. What will the new contribution format income statement budget look like if these changes are adopted?

Copy the budget excel tab and name the tab, Budget 3. Refer to the original data. By automating, the company could slash its variable cost per unit in half. However, fixed costs would increase by $100,000 per month. Compute the new CM ratio and the new break-even point in both units and dollars.

Formulas:

Sales Variable Expenses = Contribution Margin

Contribution Margin Fixed Expenses = Net Operating Income

Contribution Margin Ratio = Contribution Margin per unit

(expressed as decimal or %) Sales Price per unit

Total Contribution Margin = Sales x Contribution Margin Ratio

Break Even in Units = Fixed Expenses/Contribution Margin per Unit

Break Even in Sales = Fixed Expenses/Contribution Margin Ratio

Margin of Safety = Total Sales Break Even Sales

Degree of Operating Leverage = Contribution Margin/Net Operating Income