My Paper Writer » Essay Blog » Business Essays Help » Cost Accounting Excel CVP Modeling and Analysis

# Cost Accounting Excel CVP Modeling and Analysis

## Project: CVP Modeling and Analysis

The purpose of this project is to give you experience creating a multiproduct profitability analysis that can be used to determine the effects of changing business conditions on the client’s financial position.

Your goal will be to use Excel in such a way that changes to the assumptions will correctly ripple through the entire profitability analysis.

The Stackpole Company retails two products, a standard and deluxe version of a luggage carrier.

The standard carrier sells for \$28, has variable costs of \$18, and direct fixed costs of \$1,000,000.  The deluxe model sells for \$50, has variable costs of \$30, and direct fixed costs of \$1,000,000.  The company has common fixed costs of \$250,000.  The anticipated sales mix for the company is 3:1 or 75/25.

Directions:

You have been hired to build a CVP model to help the company understand the impact of business conditions on operating income

Your model should include breakeven in units with a pro forma income statement, units sales for a target income of \$250,000 with a pro forma income statement, an income statement reflecting actual current sales, margin of safety in dollars and %, and degree of operating leverage.

Your model should have the capability of computing B/E, target income, MOS, DOL, and predicting change in income based on a change in sales.  Create a dashboard to show the these variables.

Once you have built the model, use the model to determine changes to the dashboard variables.  In a report to me, your CEO, show the original variables, the changed variables and explain why the change occurred.

1. Sales mix changes to 25/75
2. Common fixed costs increase by \$100,000
3. Reduce VC of the standard carrier to \$10 per unit

### How to Design a Spreadsheet Model:

• Determine the objective
• Determine what information is needed
• Make sure you know how to solve the problem
• Use Excel to model the objective, compute the solution, and display the results
• Test your spreadsheet to ensure it meets the objective (use the chapter example)

• Top-down; left-right
• One workbook, if possible
• Separate input and report areas
• All changes should occur in the input area – not in the body of the spreadsheet where you compute the solution
• All cells in the body of the spreadsheet should contain a reference, formula, or function
• Display – layout in a manner that is easy to follow; format cells to clearly identify the contents as numbers, dollars and cents, etc….; include labels, headings, etc…
• Include user instructions

Modeling and Analysis Competency Demonstration Project

Last Updated on November 24, 2019

Don`t copy text!
Scroll to Top