thank you!

The purpose of this project is twofold: It will give you experience of: 1. Undertaking profitability analysis, and understanding the link between the cost structure and profitability of a company. 2. Preparing a sales budget and purchase budget for its merchandising division. Your goal will be to use Excel in such a way that any changes to the assumptions will correctly ripple through the entire profitability analysis and budget preparation. If executed properly, the company should be able to use this spreadsheet over and over, using different "what if" assumptions. 1. Complete the green cells in the Assumption box in the Part A Data File CVP worksheet by linking the appropriate cells in the Projected Data Table in the Project Description worksheet. Do not hardcode in the Assumption box, or else, this spreadsheet cannot be used over and over if the projections are changed. 2. Prepare projected Contribution margin-based income Statement for the fourth quarter of 20Y for Denver and Santa Fe Divisions based on the projected data transferred in the Assumption box. Use formula as needed. Do not hardcode. Check figure: Net Income should be $900,000 for both divisions. 3. Calculate the following for both divisions using appropriate formula: - Variable costs per unit - Contribution margin per unit - Break-even units Use Roundup formula in Excel to round up to full unit. The general roundup formula for break-even will be: =ROUNDUP(Total Fixed costs/Contribution margin per unit, 0 ). Check figures: Break-even units for Santa Fe Division: 30,000 units; - Break-even sales - Margin of safety in percentage - Operating leverage 4. Undertake a sensitivity analysis assessing the impact of operating leverage on net income based on the following two what-if scenarios for both the divisions. (Use appropriate formula and format the cells as 'currency'): a) Conservative scenario: What would be the projected net income if sales decrease by 30% in the fourth quarter? Check figure: for Santa Fe division: $540,000 b) Optimistic scenario: What would be the projected net income if sales increase by 30% in the fourth quarter? Check figure: for Denver division: $1,473,000 5. Assume that, you are in the position of Cost Accountant in Fitness Fast Inc. and the management seeks an explanation from you regarding the disparity among the comparative profit data of the sensitivity analysis of the two divisions. How do you explain this difference? (Write your answer in the Explanation box.)