I will upload excel file. The question is in the excel file. Answer it in the same file . And please note the assignment has 3 sheets. so look at them carefully

BUSI3320(All sections) Computer Assignment #2 Fall 2008 1 Chapter 3 and Computer Assignment II 2017WI Include your Name & ID # in your Header and in your file name The CFO of the XYZ Company is interested in predicting the future Overhead costs for planning and control purposes. She is uncertain as to whether to use scatergraph method, Hi/Low method or regression to develop the formulae. She gathered the following data from the previous year to aid in her decision. Data for The XYZ ComMonth 1 Prior Year January 2 February 3 March 4 April 5 May 6 June 7 July 8 August 9 September 10 October 11 November 12 December DL Hours Overhead Cost 19,600 82,346 23,500 91,135 21,600 87,735 22,500 88,225 19,600 79,940 18,600 73,960 13,700 69,181 11,200 65,674 11,800 67,680 16,700 73,430 15,700 70,074 18,600 76,476 DL Hours Overhead Cost REQUIRED FOR COMPUTER ASSIGNMENT and Chapter 3 Assignment ROUND UNIT VARIABLE COSTS TO 2 DECIMAL PLACES AND FIXED COSTS TO 1 1 Prepare a scattergraph, including on it all of the data for the prior year. Based on visual inspection are the overhead costs fixed, variable or mixed costs? Explain your answer. Highlight the columns including the header for all the data. Click on Insert and choose the icon showing the scatter graph. Answer in space provided . Calculations to the right. Drag the graph to start in column J. BUSI3320(All sections) Computer Assignment #2 Fall 2008 1b Using the scattergraph, estimate the cost formula for overhead costs. Fit the regression line to the plotted point by visual inspection. If you use Excel to draw a single trend line (SLC Insert; Shapes; Click on the line). Move the mouse to among the largest data values. SLC and hold the left button down. While holding the button down move the mouse down and towards the y-axis and slightly across the y-axis. The line should be on the graph. Adjust it by moving the left and right ends of the line until it reaches the optimum fit. Remember the point there the line crosses the y-axis in the fixed cost. The variable cost will have to be calculated. Show all calculations. Use formualae if possible. Scattergraph formula 2 Using the high-low method, estimate the cost formula for overhead costs. Show thr details of all calculations Hi/Low Cost Formula 3 Complete regression analysis for the data. Estimate the regression method cost formula. Regression cost formula Doing regression Click on Data on the ribbon. Is Data Analysis available? If yes click on DataAnalysis, scroll down to regression and click OK and follow the instructions in the pop-up menu. Input the Y data (costs), input the X data the DLHs, insert J49 OR leave New Worksheet Ply selected. Click on OK. The regression output should appear. If used, rename the ply Regression output. If Data Analysis is not available on the Data ribbon, use the following instructions: click on File at the top; click on Options at the botton; Click to the right of Manage Excel Add-Ins Bottom Centre; Choose Analysis ToolPak and Analysis ToolPak - VBA (both may not be needed, but regression works with both; Click OK. Data Analysis should now load into Excel. Follow the instructions above. 2 BUSI3320(All sections) Computer Assignment #2 Fall 2008 3 4 Compute the expected overhead cost assuming 21,000 DL hours is the expected activity level next month: Use cell refernces and formulae to complete this table a. High low formula b. Scattergraph formula c. Regression Formula # of units 21,000 21,000 21,000 VC per unit Total VC Total FC Total Cost Of the three proposed methods, which one should XYZ Company to estimate monthly overhead costs for the company? Explain why the other methods are less desirable. Total BUSI3320(All sections) Marks Student 3 2 3 Computer Assignment #2 Fall 2008 4 BUSI3320(All sections) 3 6 6 Computer Assignment #2 Fall 2008 5 BUSI3320(All sections) 3 3 3 3 35 Computer Assignment #2 Fall 2008 6 BUSI3320 (02 03) WI 2011 Ch 3 CA#3 InstructorSOLUTION Student Name TILLEY____ 1 "All equipment in the Ramon Companyis leased under the arrangement calling for a flat fee up to 19,500 direct labour hours of activity in the plant, after which the lease charges are assessed on an hourly basis. Lease expense is a major item of overhead cost." (Comprehensive Problem Ch3 pg 101) What problems do you think this information will cause for determining the expected overhead costs at different levels of activity? Answer below. 2 Using the high-low method, estimate the cost formula for overhead cost in the plant. Note: CHECK FIGURE Cost Formula, is incorrect Data for The Ramon Company Month 1 Prior Year January 2 February 3 March 4 April 5 May 6 June 7 July 8 August 9 September 10 October 11 November 12 December 13 Current year January 14 February 15 March 16 April 17 May 18 June 19 July 20 August 21 September DL Hours 19,600 24,400 21,600 22,500 19,600 18,600 13,700 11,200 11,800 16,700 15,700 18,600 20,600 23,500 22,500 21,600 19,600 17,600 11,800 12,700 14,700 Overhead Cost 82,300 94,080 87,700 88,200 79,900 74,000 69,100 65,700 67,600 73,500 70,100 76,400 84,300 91,100 91,100 85,300 78,400 75,000 66,200 69,600 72,000 BUSI3320 (02 03) WI 2011 22 23 24 Ch 3 CA#3 InstructorSOLUTION October November December 16,700 14,700 17,600 71,100 69,600 73,500 Student Name TILLEY____ BUSI3320 (02 03) WI 2011 Ch 3 CA#3 InstructorSOLUTION Student Name TILLEY____ 3 Using the scattergraph method, estimate the cost formula for overhead cost in the plant. 4 Using the Least-Squares Regression Method, estimate the cost formula for overhead cost in the Ramon Company for a) All 24 periods NOTE OUTPUT ON SEPARATE PLY b) All periods with DLH>19,500 5 Compute the expected overhead cost for next month assuming the plant uses Use Cell DL hours: Reference # of VC per Units unit Total VC Total FC 21,500 a. High low 21,500 b. Scattergraph 21,500 c. Regression values using all 24 periods 21,500 d. Regression values using data only for months with DL Hours >19,500 hours only. 21,500 Of the four proposed methods, which one should Ramon Company use to estimate next month's overhead 6 costs for the Franklin plant? Explain why the other methods are less desirable. 7 Would a relevant range concept be more or less important in the Ramon Company than in most companies? Why or why not? BUSI3320 (02 03) WI 2011 Ch 3 CA#3 InstructorSOLUTION Student Name TILLEY____ BUSI3320 (02 03) WI 2011 Ch 3 CA#3 InstructorSOLUTION Working Area ment calling for a flat ich the lease charges f overhead cost." nk this information will els of activity? Answer cost in the plant. ncorrect Student Name TILLEY____ BUSI3320 (02 03) WI 2011 Ch 3 CA#3 InstructorSOLUTION Student Name TILLEY____ BUSI3320 (02 03) WI 2011 head cost in the plant. DLH>19,500 e plant uses Total Cost mate next month's overhead amon Company than Ch 3 CA#3 InstructorSOLUTION Student Name TILLEY____ BUSI3320 (02 03) WI 2011 Ch 3 CA#3 InstructorSOLUTION Student Name TILLEY____ BUSI3320 (02 03) WI 2011 Ch 3 CA#3 InstructorSOLUTION Student Name TILLEY____ BUSI3320 (02 03) WI 2011 Ch 3 CA#3 InstructorSOLUTION Student Name TILLEY____ BUSI3320 (02 03) WI 2011 Ch 3 CA#3 InstructorSOLUTION Student Name TILLEY____ BUSI3320 (02 03) WI 2011 Ch 3 CA#3 InstructorSOLUTION Student Name TILLEY____ COMPUTER ASSIGNMENT and Chapter 3 Assignment Marks Student 1 Prob change cost structure 2 2 Using the high-low method, estimate the cost formula fo 6 3 Prepare a scattergraph, including on it all of the data fo 4 Good estimate 1 Why? Why not? 1 4 Complete regression analysis for the following groups of data: I All data for the two year period 4 ii Data only for PERIODS where DL hours > 1 4 Was 19,500 chosen deliberately? Why? 2 5 Compute the expected overhead 22,500 DL hours: a. High low b. of Scattergraph the data for the two with years. DL Hours >19,500 hours. Chosen method Regression >19500 fits production Why other NOT as good 6 relevant range concept be more or less important most companies? Why or why not? Total 3 3 3 3 1 1 1 1 2 42 0 42