Please complete w/ formulas
D M N 0 CVP Model and Budget DIRECTIONS: = Part B. Preparation of sales and muchase budget: Part A. CVP analysis: You are in the process of preparing budget for 20Y2 for Arizona Division. 1. Complete the green cells in the Assumption box in the Part A Data File CVP worksheet by linking || - Based on the forecasts of the Sales Department, the sales are expected to increase by 10 perc the appropriate cells in the Projected Data Table in the Project Description worksheet. in each quarter. Do not hardcode in the Assumption box, or else, this spreadsheet cannot be used over and over if projections changed. - According to the company's policy, the desired ending inventory is expected to be 20 per cent , the budgeted sales of the current quarter. 2. Prepare projected Contribution margin-based Income Statement for the fourth quarter of 20Y1 for Colorado and Arizona divisions based on the projected data transferred in the Assumption box. Required: Use formula as needed. Do not hardcode. 1. Complete Budget assumption box in the Part B_Data file Budget. (See the comments.) Description of the business: Check figure: Net Income should be $1,000,000 for both divisions. 2. Prepare a Sales budget and Purchase budget for Arizona Division for 2012. Fitness First Inc. is specialized in selling sports drink in boxes to major retailers. Currently, they are (Use appropriate formula. Use Roundup function to calculate the number of units as a full unit launching a new organic drink rich in essential electrolytes with minimal preservative. 3. Calculate the following for both divisions using appropriate formula: each row of the budget tables) The Colorado division of the company manufactures the drink in-house and Arizona division custom Variable costs per unit Check figures: orders the drink from a Mexican manufacturer. Contribution margin per unit (Total budget sales units: 255,255 units) Break-even units (Total needs for inventory in 2022: 269,896 units) The following projected data is provided for the Fourth quarter of 2041:: 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). Projected data Table: Check figures: Colorado 30,770 units; Colorado Arizona - Break-even sales (use formula: Break-even units x Selling price per unit) Quarterly volume of units sold in the fourth quarter 20Y1 Submission Instructions: 50,000 50,000 Check figures: Arizona: $1,166,690 elling price per unit $70 $70 Margin of safety in percentage - This is an individual submission. Total Variable Costs of Goods Sold $700,000 Operating leverage $1,900,000 Submission Deadline: Before March 13, 2020, 11:59 PM. Cotal Fixed Costs of Goods Sold $1,200,000 $0 0 - Please refer to the relevant chapters in the textbook for the required accounting formula. 4. Undertake a sensitivity analysis assessing the impact of operating leverage on net income based otal Variable Selling and Administration Expenses $200,000 $100,000 - Please refer to the knowledge gathered in Excel Project 1, consult with your friends and instruc on the following two what-if scenarios for both the divisions. otal Fixed Selling and Administration Expenses or Google for Excel functions. $400,000 $500,000 (Use appropriate formula and format the cells as 'currency'): a) Conservative scenario: - Complete your solution in this template and submit the electronic copy of your Excel worksh What would be the projected net income if sales decrease by 30% in the fourth quarter in both in Canvas under the assignment link: ACC 2020 Excel Project 2 Fall 2021 by deadline. divisions? Check figure: for Colorado division: $220,000 - Please name your file using your last name, first initial and the project name, for example, b) Optimistic scenario: Siddiques_ACC2020_EP2.xlsx. What would be the projected net income if sales increase by 30% in the fourth quarter in both divisions? See the grading criterial on the next worksheet "Rubrics". Check figure: for Arizona division: $1,450,000 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. 5. Assume that you are in the position of Cost Accountant in Fitness First Inc. and the management , You must submit your assignment in Canvas. Any assignment submitted in email will not be seeks an explanation from you regarding the disparity among the comparative profit data of the graded. sensitivity analysis of the two divisions. How do you explain this difference? (Write your answer in the Explanation box). C31 x fx =(C$18*(1+$C30))-C$19 A B D E 1 1 ASSUMPTIONS 1 2 3 4 5 6 7 8 9 Nm 000 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 Colorado $70 $700,000 $1,200,000 $200,000 $400,000 Arizona $70 $1,900,000 $0 $100,000 $500,000 Quarterly volume of units sold in the fourth quarter 20Y1 50,000 50,000 2 Projected Contribution Margin based Income Statement For Fourth quarter 2011 Colorado 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 Sales Total Variable costs Contribution margin Total Fixed costs Net income $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 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 $52.00 30,770.00 $2,153,900 38.46% 2.60 $40.00 Correct $30.00 Correct 16,667.00 Correct $1,166,690 Correct 66.67% Correct 1.50 Correct 30 31 4 Sensitivity Analyis Conservative scenario: Sales decrease by 40% than the projected sales Net Income under conservative scenario: -30% $220,000 -30% $550,000 Correct Optimistic scenario: Sales increase by 40% than the projected sales Net Income under optimistic scenario: 5. Explanation: 30% $1,780,000 32 33 34 35 36 37 38 39 30% $1,450,000 Correct Jx A B H C D E F F G Siddique, Salina: Do not hard code. Link the appropriate cell in Project Description worksheet to get the data. 1 Budget Assumptions for Arizona Division 2 Projected/Budgeted sales in unit for the Fourth Quarter of 2041 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 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 code. 7 Sales Budget for the 20Y2 1st quarter 2nd quarter 3rd quarter 4th quarter Total sale units Purchase Budget for the 20Y2 1st quarter 2nd quarter 3rd quarter 4th quarter 2012 Amounts Answer check 8 9 10 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 27 28 29 30 31 32 33
