If I could please have help with this excel spreadsheet, that would be great. I am having trouble with it. Thank you! I need help with the High Low method for this problem here. Any help would be greatly appreciated thank you!
AutoSave . Off Copy of a311 A1 Cost Estimation - Saved Search Cody Gabriel CG X File Home Insert Page Layout Formulas Data Review View Help Power Pivot QuickBooks 15 Share Comments & Cut Palatino-Roman 14 " A" A" ab Wrap Text General Comma 2 Normal 2 Normal Bad AutoSum AY O Paste [G Copy BIU~ ~ MA E Merge & Center $ ~ % " Conditional Format as Good Neutral Calculation Check Cell nsert Delete Format Fill Clear Sort & Find & deas Format Painter Formatting Table Filter ~ Select v Clipboard Font Alignment Number Styles Cells Editing Ideas R65 X V E G 0 P Q R S T U 2 34 V W Y AA Part 1. Answer these questions by applying the high-low method. Assume that the 36 Overhead costs are estimated using labor hours. 37 38 What is the estimated variable cost per labor hour? S 175.359 39 Q1 40 What is the estimated monthly fixed overhead cost? 41 Q2 42 Forecast monthly overhead costs for a month when labor hours are equal to 1,100 Q3 43 44 What is the average number of labor hours per unit? Q4 45 46 What is your estimate of the variable overhead cost per unit? 47 48 What is your estimate of the contribution margin per unit? 49 8 50 What is your estimate of total fixed cost per month? 51 52 What is your estimate of total fixed cost per year? 53 55 54 How many units are required to break-even? 8 8 : 56 How many units are required to earn an annual before tax profit equal to S 750,000 Q10 57 58 What is the after tax profit required to earn a before tax profit of 5 750,000 Q11 59 60 How many units are required to earn an annual after tax profit equal to 750,000 Q12 61 62 What is the before tax profit if the number of units manufactured and sold is 150,000 Q13 63 64 What is the before tax profit if the number of units manufactured and sold is 180,000 Q14 65 66 What is the before tax profit if the number of units manufactured and sold is 210,000 Q15 67 68 In order to complete the remaining questions use the regression capabilities of Excel to estimate 69 the following cost equation: Monthly OH = b0 + b1 x labor hours + b2 x machine hours 70 71 Based on the results, what is the estimate of monthly fixed manufacturing OH costs? 72 Q16 73 What is the estimate of variable OH costs per labor hour? Q17 74 75 What is the estimate of variable OH costs per machine hour? Q18 76 77 What are the estimated total OH costs for an average month based on the cost model? Q19 78 79 If output where 10% higher than average what is your estimate of total Mfg OH Costs? Q20 80 82 81 If output where 15% higher than average what is your estimate of the contribution margin per unit? Q21 83 Instructions Questions Data + + 60% O 4:57 PM 10/14/2020 21AutoSave . Off Copy of a311 A1 Cost Estimation - Saved Search Cody Gabriel CG X File Home Insert Page Layout Formulas Data Review View Help Power Pivot QuickBooks 15 Share Comments & Cut Palatino-Roman 14 " A" A" ab Wrap Text General Comma 2 Normal 2 Normal Bad AutoSum O Paste [G Copy Fill AY Format Painter BIU~ ~LA = FEE Merge & Center $ ~ % " Conditional Format as Good Neutral Calculation Check Cell Formatting Table 41 nsert Delete Format Clear Sort & Find & deas Filter ~ Select v Clipboard Font Alignment Number Styles Cells Editing Ideas R65 X V B C D E G H M N 0 P Q R 1 Data Table 1 3 Month Labor Hours Machine Hours OH Costs Units Mfg'd 4 January 1,262 1,752 221,304 5,256 5 February 962 1,362 189,758 4,086 6 March 1,318 2,238 $ 223,883 6,714 7 April 2,205 230,335 6,615 8 May 942 214,621 5,652 9 June 1, 188 2,205 1,845 $ 219,848 6,615 10 July 1,114 206,957 5,535 11 August 1,006 1,773 204,047 5,319 12 September 1,344 1,641 217,754 4,923 13 October 1,196 1,971 221,287 5,913 14 November 760 1,752 194,962 4,560 Use the light blue cells above to show the results 15 December 792 2,229 198,559 4,752 22,986 $ of running a linear regression 16 Totals 13, 164 2,543,313 65,940 17 18 19 20 Max Use the blue area Before you answer the required questions I suggest that you go to the Data worksheet. There I have 21 Min to show your a set of data that is related, but different to the data displayed in the Question worksheet. You will 22 4 high-low also see a X-Y scatter graph that includes a trend line. 23 VC calculations 24 FC It is always a good idea to start your data analysis by looking at the raw data to check for outliers 25 and also to see if there are any obvious trends. 26 Use Data Table 1 to answer the following Questions You can change the X value of the displayed scatter graph by selecting the graph and then on the Menu 28 The following assumptions apply unless an alternative is specifically given in a question 29 i) The Sales Price per unit 74.00 Data >> Chart Source Data 30 ii) Direct materials and direct labor costs per unit. 19.00 This will bring up a pop up window. Select the X-value box and then pick a new range of cells. The 31 ili) Variable General and Administrative costs per unit. 14.00 scatter plot will update automatically. Since I set the plot up to include a trend line and the equation 350,000 of the trend line you will also see the results of a simple linear regression of the form 32 iv) Fixed General and Administrative costs per month. OH = intercept + slope . x 33 v) Marginal tax rate on Income 21% 34 With this technique you can look at the pair-wise relationships between OH Costs and labor hours, 35 Part 1. Answer these questions by applying the high-low method. Assume that the 36 Overhead costs are estimated using labor hours. 37 38 What is the estimated variable cost per labor hour? $ 175.359 Q1 Instructions Questions Data 80% O Si A Q X 4:57 PM 10/14/2020 721
