Please do with formulas in excel

ACC200 14 Page 1 Cake 1 Cake 2 ACC200 14 Assignment Cake 3 Oven Aussie bakery bakes only t hree types of cakes for a large retailer. 25kw h Oven electricity consumption is 25kwh. ACC200 14 Page 2 Assignment Part 1 (8 marks) Suppose you have the following information. Month Number of days Cake 1 Cake 2 Cake 3 Total cakes (1) Oven electricity usage (kwh) (2) Other electricity usage (kwh) (1)+(2) Total electricity usage (kwh) 1 31 9,500 250 2 28 8,800 249 3 31 7,500 261 4 30 6,500 337.9 5 31 7,200 489 6 30 7,500 607.2 7 31 8,200 638.4 8 31 8,300 697.5 9 30 8,500 480 10 31 9,000 337.9 11 30 9,400 246 12 31 10,000 400 Complete the above table considering the following assumptions:  Total number of cake 1 , cake 2 and cake 3 are 60% , 20% and 20% of total cakes.  The oven capacity is 10 cakes per load.  Cake 1 needs 30 minutes and cake s 2 and 3 need 36 minutes cooking time. 1. Compare rates of two electricity providers and choose the cheapest one based on the electricity usage for a year. 2. Create an Excel file that can be used as a tool for comparing the electricity cost of these two electricity providers for different levels of monthly productions . Provide a brief guide about how this tool works and formulas used. Electricity provider 1 Electricity provider 2 Usage /monthly Price Usage /monthly Price First 335 0 kw h 29.8 c/kwh First 255 0 kwh 29.55 c/kwh Between 335 0 kwh and 55 00 kwh 30 c/kwh Between 255 0 kwh and 550 0 kwh 29 c/kwh Thereafter 30.01 c/kwh Thereafter 28.75 c/kwh Supply charge per day $4.01 2 Supply charge per day $5 ACC200 14 Page 3 Assignment Part 2 (8 marks) Assume that monthly electricity costs (based on the cheape st electricity provider) and production levels from Part 1 are given as historical data. Complete this table and answer required questions using the following assumptions:  Direct material cost per cake is 60 cents .  Each cake requires 10 minutes direct labo ur work and the labour rate is $ 24 per hour.  Use the total number of cakes as the cost driver.  The relevant range is between 6,500 and 9, 800 cakes. Month Total Cakes Total Electricity Cost Rent Other expenses Direct material costs Direct labour costs 1 9,500 3,000 8,800 2 8,800 3,000 9,500 3 7,500 3,000 8,003 4 6,500 3,000 7,500 5 7,200 3,000 7,536 6 7,500 3,000 9,565 7 8,200 3,000 9,676 8 8,300 3,000 10,007 9 8,500 3,000 8,544 10 9,000 3,000 10,454 11 9,400 3,000 9,900 12 10,000 3,000 10,000 Required: 1. Use your judgement to classify cost s as fixed, variable or mixed. Explain your basis for your classification. 2. Draw a graph for each cost to demonstrate its behaviour using scatter diagram. 3. Estimate the total cost formula (monthly) for the bakery using the high -low and regression method s for mixed costs. 4. Estimate the cost of producing 6400, 8,900 and 9,900 cakes per month using the total cost formulas based o n both regression and high -low methods. Explain your results. ACC200 14 Page 4 Assignment Part 3 ( 10 marks) Suppose s elling price for cake 1 , cake 2 and cake 3 are $ 7 , $6 .5 and $ 7.2 , respectively. Also, a ss ume that the variable cost for each cake is as below: Cake 1 100% of estimated variable cost based on the total cost formula(regression) in Part 2 Cake 2 90% of estimated variable cost based on the total cost formula(regression) in Part 2 Cake 3 110% of estimated variable cost based on the total cost formula(regre ssion) in Part 2 Use the estimated fixed cost based on the total cost formula ( estimated using regression) in Part 2 to answer the following questions. All questions are independent unless explicitly stated. Required: 1. What is the breakeven point in unit s and sales dollars for the year for each type of cake?

2. Calculate the number of each type of cake and their dollar value s that must be sold to beak - even , assuming that the tax rate is 30 per cent . Explain your results.

3. Calcula te the margin of safety i n units and dollars for a year assume the budgeted sales is the same as given figures in Part 1 . Explain your results.

4. Use the degree of operating leverage to investigate the impact of 20 % increase and 20 % decrease in the sales revenue on profit. Explain y our results.

5. If it is expected that fluctuations in sales will rise, what is your recommendation about the cost structure for this business?

6. Calculate the number of each type of cake and their dollar value s that must be sold to achieve a targeted after tax profit of $ 1 50,000 assuming that the tax rate is 10 per cent.

7. If the fixed cost increases by $ 2 0 ,0 00 per year and the variable cost decreases by 10% . H ow many of each type of cake do they need to sell to b reakeven a ssuming the same sales mix?

8. If the variable cost per cake increases by 15 % and selling price by 20 %, what would be the profit?

9. What is the new break - even point , if both fixed costs and contribution margin s increase by 2 0%? Explain your results.

10. W hat is the new break - even point if the number of units sold increases by 2 5 %?

Explain your results. ACC200 14 Page 5 Assignment Part 4 (4 marks) The bakery’s budget for the year included the following predictions: Budgeted total manufacturing overhead $ 194,521 Budgeted number of cakes 115,000 Budgeted total labour hours 17 , 250 Budgeted total labour costs $ 345 , 000 Budgeted total oven hours 6 , 210 Assuming the number of cakes , direct material costs, direct labour costs, oven hours , labour hours and product mix in Part 1 and Part 2 are based on the actual numbers, answer the following questions: Required: 1. Calculate the cost of each type of cake using the following cost drivers:

a) Labour hours b) Labour costs c) Oven hours 2. Explain the difference between your results in Q 1 .

3. Calculate the overapplied or underapplied overhead for the year.