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 clients 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.

Business Description: 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 $800,000. The company has common fixed costs of $275,000. The anticipated sales mix for the company is 4:1 or 80/20. The actual level of sales units currently is 250,000 and your target income is $300,000. You expect sales to rise 15% in the coming year.

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 $300,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.

PLEASE SHOW EXCEL FORMULAS