please answer the following questions in the spreadsheet. also show the formula used

Name: Type your name here Project 5 Upload to Project 5 link. Remember the last day of the term is a Saturday and no late assignments are accepte Save your file using your first initial, last name, and name of project. There are 3 different parts to project 5. Make sure you click on the applicable part for specific requirements. Project 5 Objectives: 1. Prepare a flexible budget and performance report. 2. Formulate and interpret the variable cost variances. 3. Analyze impact of transfer pricing decision on ROI. Grading Rubric for Final Project: Part 1--Must use cell reference to earn credit Each number in revised report-.5 pt. each U or F variance designation-1 pt. each Comments #2 Comments #3 Total points possible Part 2--Must use cell reference to earn credit Each Variance calculation worth -4 pt. each Each U or F designation-4 pt. each Comments Total points possible Part 3 Each question 8 pts. each Total points for Final project 10 10 4 4 28 28 28 4 60 32 120 signments are accepted after the term ends. ecific requirements. Part 1 Several years ago the Cookie Company developed a comprehensive budgeting system for profit planning and control purposes. The line supervisors have been very happy with the system and with the reports being prepared on their performance, but both middle and upper management have expressed considerable dissatisfaction with the information being generated by the system. The following is the overhead performance report for the Baking department. Cookie Company Overhead performance report-Baking department For the quarter ended June 30 Actual Budget Variance U or F 34,000 35,000 1,000 U Units Variable Overhead Indirect materials Rework time Utilities Machine setup Total variable overhead cost 35000 8400 68000 17500 128900 36000 8500 69,000 18000 131500 1000 F 100 F 1000 F 500 F 2600 F Fixed overhead Costs Maintenance Depreciation Inspection Total fixed overhead cost Total overhead cost 79500 10000 60100 149600 278500 80000 9800 60000 149800 281300 500 F 200 U 100 U 200 F 2800 F After receiving a copy of this overhead performance report, the supervisor of the baking department stated, "These reports are super. It makes me feel really good to see how well things are going in my department. I can't understand why those people upstairs complain so much." The company's vice president has hired you as a consultant to develop a new overhead performance report for the quarter for the Baking department. After you have developed this new performance report, explain to the supervisor of the baking department the reasons for your changes and discuss the issue of controlling costs. Required: 1. Prepare a revised budget for June using the flexible performance approach. You need to use cell reference to calculate the variance . You must designate a variance for each cost category and for the total cost: U or F. The variable and fixed cost labeling has been set correctly, so do not change this around. Please use the template I have set out below to complete this Performance report. 2. Explain to the supervisor of the baking department the reasons for your changes and discuss the issue of controlling costs. 3. Identify variances in the report you prepared that should be investigated? Explain in detail why these variances should be investigated. Solution: 1. Revised flexible performance report. Cookie Company Overhead flexible performance report-Baking department For the quarter ended June 30 Actual Budget Variance U or F Units Variable Overhead Indirect materials Rework time Utilities Machine setup Total variable overhead cost Fixed overhead Costs Maintenance Depreciation Inspection Total fixed overhead cost Total overhead cost 2. Explain to the supervisor of the baking department the reasons for your changes and discuss the issue of controlling costs. 3. Identify variances in the report you prepared that should be investigated? Explain in detail why these variances should be investigated. Part 2 Foster Corporation uses a standard cost system. There is no beginning or ending work in process, and finished goods inventory balances. The following information was provided concerning the one product produced by this company for the period that just ended: This company used Direct labor hours as the cost driver for the application of overhead. Actual price per kilogram Actual kilograms of material used Actual kilograms of material purchased Actual hourly labor rate Actual hours of production Standard price per kilogram Standard kilograms per completed unit Standard hourly labor rate Standard time per completed unit Actual Variable factory overhead Actual Fixed factory overhead Standard variable factory overhead rate Budgeted fixed overhead Units completed during the period $2.96 32,000 34,000 $17.50 4,900 labor hrs. $2.97 6 $17.80 1 Hour $17,500.00 $18,100.00 $3.80 per labor hour $18,400.00 4,975 You have just been hired by a local plastic pool manufacturer to determine if they are controlling their costs. You have decided to use your recent knowledge of variance analysis to assist you in this endeavor. You will analyze the variances as stated in the requirements below. Required: Make sure you do not forget to label each variance U or F. You need to use cell references for your calculations. Variance U or F 1. Calculate the direct materials price and quantity variance. Material purchase price variance -Material price variance should be based on material purchased, since you want to isolate the variance as soon as possible. Material Quantity variance - Material Quantity variance should be based on materials used, since this is monitoring the production efficiency. 2. Calculate the direct labor rate and efficiency variances. Direct Labor rate variance Direct Labor Efficiency variance 3. Variable manufacturing overhead spending and efficiency variances. Variable overhead spending variance Variable overhead efficiency variance 4. Fixed manufacturing overhead budget variance. Fixed Manufacturing overhead budget variance 5. Pick out the one most significant variance that you computed above and explain the possible causes of this variance and why you chose it. Part 3 Rug, Inc. has acquired a new backing division that produces rubber backing, which it sells for $2.20 per square yard. Sales are about 1,200,000 square yards per year. Since the Backing Division has a capacity of 2,000,000 square yards per year, top management is thinking that it might be wise for the company's Tufting Division to start purchasing from the newly acquired Backing Division. The Tufting Division now purchases 600,000 square yards per year from an outside supplier at a price of $2 per square yard. The current price is lower than the competitive $2.20 price as a result of the large discounts. The Backing Division's cost per square yard follows: Direct materials $1.20 Direct Labor 0.3 Variable overhead 0.25 Fixed overhead (1,200,000 level) 0.1 Total cost $1.85 Required: 1. If both divisions are to be treated as investment centers and their performance evaluated by the ROI formula, what transfer price would you recommend that would benefit each division? Why? 2. Determine the effect on corporate profits of making the backing. Make sure you show your calculations. 3. Based on your transfer price, would you expect the ROI in the Backing Division to increase, decrease, or remain unchanged? Explain. 4. What would be the effect on the ROI of the Tufting Division using your transfer price? Explain