CVP Model and Budget 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. Description of the business: Fitness First Inc. is specialized in selling sports drink in boxes to major retailers. Currently, they are launching a new organic drink rich in essential electrolytes with minimal preservative. The Colorado division of the company manufactures the drink in-house and Arizona division custom orders the drink from a Mexican manufacturer. The following projected data is provided for the Fourth quarter of 20Y1:: Projected data Table: Quarterly volume of units sold in the fourth quarter 20Y1 Selling price per unit Total Variable Costs of Goods Sold Total Fixed Costs of Goods Sold Total Variable Selling and Administration Expenses Total Fixed Selling and Administration Expenses Colorado Arizona 50,000 50,000 $70 $70 $700,000 $1,900,000 $1,200,000 $0 $200,000 $100,000 $400,000 $500,000 DIRECTIONS: Part A. CVP analysis: 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 projections changed. G 2. Prepare projected Contribution margin-based Income Statement for the fourth quarter of 2011 for Colorado and Arizona divisions based on the projected data transferred in the Assumption box. Use formula as needed. Do not hardcode. Check figure: Net Income should be $1,000,000 for both divisions. in 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,o). Check figures: Colorado 30,770 units; - Break even sales (use formula: Break-even units x Selling price per unit) Check figures: Arizona: $1,166,690 - Margin of safety in percentage - Operating leverage FO 00 60 00 s! w 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 in both divisions? Check figure: for Colorado division: $220,000 b) Optimistic scenario: What would be the projected net income if sales increase by 30% in the fourth quarter in both divisions? Check figure for Arizona division: $1,450,000 Si Se As ac YO 5. Assume that, you are in the position of Cost Accountant in FitnessFirst 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). Part B. Preparation of sales and puchase budget: You are in the process of preparing budget for 20Y2 for Arizona Division. - Based on the forecasts of the Sales Department, the sales are expected to increase by 10 per cent in each quarter. - According to the company's policy, the desired ending inventory is expected to be 20 per cent of the budgeted sales of the current quarter. Required: 1. Complete Budget assumption box in the Part B_ Data file Budget. (See the comments) 2. Prepare a Sales budget and Purchase budget for Arizona Division for 2012 (Use appropriate formula. Use Roundup function to calculate the number of units as a full unit in each row of the budget tables) Check figures: (Total budget sales units: 255,255 units) (Total needs for inventory in 2042: 269,896 units) Submission Instructions: - This is an individual submission. Submission Deadline: Before March 13, 2020, 11:59 PM. - Please refer to the relevant chapters in the textbook for the required accounting formula. - Please refer to the knowledge gathered in Excel Project 1, consult with your friends and instructor or Google for Excel functions. - Complete your solution in this template and submit the electronic copy of your Excel worksheet in Canvas under the assignment link: ACC 2020 Excel Project 2 Fall 2021 by deadline. - Please name your file using your last name, first initial and the project name, for example, Siddiques_ACC2020_EP 2.xlsx. See the grading criterial on the next worksheet "Rubries". An assignment which is turned in late will have a 5% penalty per day. No assignments will be accepted one week after the due date without the prior approval of the instructor. You must submit your assignment in Canvas. Any assignment submitted in email will not be graded B D E F G H Siddique, Salina: Do not hard code. Link the appropriate cell in Project Description worksheet to get the data. A 1 Budget Assumptions for Arizona Division 2 Projected/Budgeted sales in unit for the Fourth Quarter of 20Y1 3 The percentage of sales increase from one quarter to next quarter 4 Desired Ending Inventory at the end of each quarter is 20% of the 5 Budgeted sales unit of the same quarter 6 Budgeted Ending inventory units for the Fourth quarter of 20Y1 50,000 10% 20% Siddique, Salina: Complete Cell B3, B4 and B6 from the data provided in the Part B in the Directions box in Project Description worksheet. These three are the only cells you are supposed to hard 0 7 8 9 10 Sales unit 11 Answer check 13 14 15 Budgeted Sales unit 16 Plus Desired Ending Inventory 17 Equal to Total need 18 Less Beginning inventory 19 Equal to Budgeted Purchase 20 Answer check Sales Budget for the 20Y2 1st quarter 2nd quarter 3rd quarter 4th quarter Total sale units 55,000 60,500 66,550 73,205 255,255 Correct Correct Correct Correct Correct Purchase Budget for the 20Y2 1st quarter 2nd quarter 3rd quarter 4th quarter 20Y2 Amounts Answer check 55,000 60,500 66,550 73,205 255,255 Correct 11,000 12,100 13,310 14,641 14,641 Correct 66,000 72,600 79,860 87,846 269,896 Correct 0 11,000 12,100 13,310 14,641 Check formula 66,000 61,600 67,760 74,536 255,255 Check formula Check formula correct correct correct Check formula 27 28 29 A B D E 1 2 3 4 5 ASSUMPTIONS Product: Sports drink Selling price per unit Total Variable costs of Goods Sold Total Fixed Costs of Goods Sold Total Variable Selling and Administration Expenses Total Fixed Selling and Administration Expenses 6 Colorado $70 $700,000 $1,200,000 $200,000 $400,000 Arizona $70 $1,900,000 $0 $100,000 $500,000 -7 8 9 10 Quarterly volume of units sold in the fourth quarter 2041 50,000 50,000 11 12 13 14 15 16 17 18 Sales Total Variable costs Contribution margin Total Fixed costs Net income Projected Contribution Margin based Income Statement For Fourth quarter 2011 Colorado $ $3,500,000 $900,000 $2,600,000 $1,600,000 $1,000,000 Arizona $ Answer check $3,500,000 Correct $2,000,000 Correct $1,500,000 Correct $500,000 Correct $1,000,000 Correct 19 20 21 22 23 24 25 26 27 3 Variable Cost per unit Contribution Margin per unit Break Even units Break Even sales (Calculate as: Breakeven sales x Selling price per box) Margin of Safety percentage Operating Leverage $18.00 $40.00 Correct $52.00 $30.00 Correct 30,769.23 16,666.67 Check formula $2,153,846 $1,166,667 Check formula 349999938.46% 349999966.67% check formula 26.00 15.00 check formula 28 29 4 Sensitivity Analyis Conservative scenario: Sales decrease by 40% than the projected sales Net Income under conservative scenario: -3096 3096 $1,200,000 Check formula $540,000 30 31 32 33 34 35 36 37 38 39 Optimistic scenario: Sales increase by 40% than the acted sales Net Income under optimistic scenario: 30% $2,040,000 3096 $1,600,000 Check formula 5. Explanation: Project Description Part A Data File CVP Part B_Data File Budget Rubrics + Ready 32F Mostly sunny