046 A B D E F F G . Use this blue area to include regression re Put first cell of regression output here 2 Data Table 1 3 Month 4 January 5 February 6 March 7 April 8 May 9 June 10 July 11 August 12 September 13 October 14 November 15 December 16 Totals 17 Labor Hours 1,262 962 1,318 1,280 942 1,188 1,114 1,006 1,344 1,196 760 792 13,164 Machine Hours 1,752 $ 1,362 $ 2,238 $ 2,205 $ 2,013 S 2,205 $ 1,845 $ 1,773 S 1,641 $ 1,971 $ 1,752 $ 2,229 $ 22,986 S OH Costs 230,304 198,758 232,883 239,335 223,621 228,848 215,957 213,047 226,754 230,287 203,962 207,559 Units Mfg'd 5,256 4,086 6,714 6,615 5,652 6,615 5,535 5,319 4,923 5,913 4,560 4,752 65,940 2,651,313 18 19 20 Max 21 Min 22 A 23 VC 24 FC Use the blue area to show your high-low calculations Before you answer the required questions I suggest tha displayed in the Question worksheet. You will also see E 046 A B D E F G . vispiacu FESTIG LUGLI MUISTEL. FUU W i act 25 26 Use Data Table 1 to answer the following Questions It is always a good idea to start your data analysis by lo 27 $ $ You can change the X value of the displayed scatter gra Data >> Chart Source Data This will bring up a pop up window. Select the X value to include a trend line and the equation of the trend in OH - intercept + slope . X 28 The following assumptions apply unless an alternative is specifically given in a question 29 i) The Sales Price per unit 30 ) Direct materials and direct labor costs per unit. 31 iii) Variable General and Administrative costs per unit. 32 iv) Fixed General and Administrative costs per month 33 v) Marginal tax rate on Income $ $ 75.00 20.00 15.50 350,000 21% With this technique you can look at the pair-wise relati 34 35 Part 1. Answer these questions by applying the high-low method. Assume that the 36 Overhead costs are estimated using labor hours. Use the data in Table 1 37 Work Area Q1 38 What is the estimated variable Overhead cost per labor hour? 39 40 What is the estimated monthly fixed overhead cost? Q2 41 1,100 Q3 42 Forecast total monthly mfg. overhead costs for a month when labor hours equal: ==> 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? Q5 47 48 What is your estimate of the contribution margin per unit? Q6 A B D E F H G Q7 50 What is your estimate of total fixed costs per month? 51 52 What is your estimate of total fixed costs per year? 53 54 How many units are required to break-even? (Note: Check that the profit does equal $0) Q8 09 55 $ 750,000 010 56 How many units are required to earn an annual before tax profit equal to 57 58 What is the after tax profit required to earn a before tax profit of 59 60 How many units are required to earn an annual after tax profit equal to S 750,000 Q11 S 750,000 Q12 61 200,000 Q13 62 What is the annual before tax profit if the number of units manufactured and sold is 63 64 What is the annual before tax profit if the Copy formatting from one locations and apply it to another old is 210,000 Q14 65 66 What is the annual before tax profit if the number of units manufactured and sold is 220,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 Q16 71 Based on the results, what is the estimate of monthly fixed manufacturing OH costs? 72 73 What is the estimate of variable OH costs per labor hour? Example 017 Instructions A Questions + A B D E F F G H 62 What is the annual before tax profit if the number of units manufactured and sold is 200,000 Q13 63 64 What is the annual before tax profit if the number of units manufactured and sold is 210,000 Q14 65 220,000 Q15 Q16 66 What is the annual before tax profit if the number of units manufactured and sold is 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 73 What is the estimate of variable OH costs per labor hour? 74 75 What is the estimate of variable OH costs per machine hour? 76 77 What are the estimated total OH costs for a month with average labor hours and machine hours? 78 79 If output where 10% higher than average what is your estimate of total Mfg OH Costs? Q17 Q18 Q19 Q20 80 81 If output where 15% higher than average what is your estimate of the contribution margin per unit? Q21 82 83 84 Instructions Questions Example + G H N 0 P R s T Use this blue area to include regression results Put first cell of regression output here 4 7 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 Before you answer the required questions I suggest that you go to the Data worksheet. There I have a set of data that is related, but different to the data displayed in the Question worksheet. You will also see a X-Y scatter graph that includes a trend lin Instructions Questions Example + G H 1 j K L M N 0 P a R S T 23 24 25 Before you answer the required questions I suggest that you go to the Data worksheet. There I have a set of data that is related, but different to the data displayed in the Question worksheet. You will also see a X-Y scatter graph that includes a trend line. It is always a good idea to start your data analysis by looking at the raw data to check for outliers and also to see if there are any obvious trends. 26 27 28 29 30 You can change the X value of the displayed scatter graph by selecting the graph and then on the Menu Data >> Chart Source Data This will bring up a pop up window. Select the X-value box and then pick a new range of cells. The scatter plot will update automatically. Since I set the plot up . I to include a trend line and the equation of the trend line you will also see the results of a simple linear regression of the form OH - intercept + slope X 31 With this technique you can look at the pair-wise relationships between OH Costs and labor hours, machine hours & units manufactured. 32 33 34 35 36 37 Work Area 38 01 39 40 Q2 41 42 Q3 43 44 45 04 46 05 Instructions Questions Example + 046 A B D E F F G . Use this blue area to include regression re Put first cell of regression output here 2 Data Table 1 3 Month 4 January 5 February 6 March 7 April 8 May 9 June 10 July 11 August 12 September 13 October 14 November 15 December 16 Totals 17 Labor Hours 1,262 962 1,318 1,280 942 1,188 1,114 1,006 1,344 1,196 760 792 13,164 Machine Hours 1,752 $ 1,362 $ 2,238 $ 2,205 $ 2,013 S 2,205 $ 1,845 $ 1,773 S 1,641 $ 1,971 $ 1,752 $ 2,229 $ 22,986 S OH Costs 230,304 198,758 232,883 239,335 223,621 228,848 215,957 213,047 226,754 230,287 203,962 207,559 Units Mfg'd 5,256 4,086 6,714 6,615 5,652 6,615 5,535 5,319 4,923 5,913 4,560 4,752 65,940 2,651,313 18 19 20 Max 21 Min 22 A 23 VC 24 FC Use the blue area to show your high-low calculations Before you answer the required questions I suggest tha displayed in the Question worksheet. You will also see E 046 A B D E F G . vispiacu FESTIG LUGLI MUISTEL. FUU W i act 25 26 Use Data Table 1 to answer the following Questions It is always a good idea to start your data analysis by lo 27 $ $ You can change the X value of the displayed scatter gra Data >> Chart Source Data This will bring up a pop up window. Select the X value to include a trend line and the equation of the trend in OH - intercept + slope . X 28 The following assumptions apply unless an alternative is specifically given in a question 29 i) The Sales Price per unit 30 ) Direct materials and direct labor costs per unit. 31 iii) Variable General and Administrative costs per unit. 32 iv) Fixed General and Administrative costs per month 33 v) Marginal tax rate on Income $ $ 75.00 20.00 15.50 350,000 21% With this technique you can look at the pair-wise relati 34 35 Part 1. Answer these questions by applying the high-low method. Assume that the 36 Overhead costs are estimated using labor hours. Use the data in Table 1 37 Work Area Q1 38 What is the estimated variable Overhead cost per labor hour? 39 40 What is the estimated monthly fixed overhead cost? Q2 41 1,100 Q3 42 Forecast total monthly mfg. overhead costs for a month when labor hours equal: ==> 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? Q5 47 48 What is your estimate of the contribution margin per unit? Q6 A B D E F H G Q7 50 What is your estimate of total fixed costs per month? 51 52 What is your estimate of total fixed costs per year? 53 54 How many units are required to break-even? (Note: Check that the profit does equal $0) Q8 09 55 $ 750,000 010 56 How many units are required to earn an annual before tax profit equal to 57 58 What is the after tax profit required to earn a before tax profit of 59 60 How many units are required to earn an annual after tax profit equal to S 750,000 Q11 S 750,000 Q12 61 200,000 Q13 62 What is the annual before tax profit if the number of units manufactured and sold is 63 64 What is the annual before tax profit if the Copy formatting from one locations and apply it to another old is 210,000 Q14 65 66 What is the annual before tax profit if the number of units manufactured and sold is 220,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 Q16 71 Based on the results, what is the estimate of monthly fixed manufacturing OH costs? 72 73 What is the estimate of variable OH costs per labor hour? Example 017 Instructions A Questions + A B D E F F G H 62 What is the annual before tax profit if the number of units manufactured and sold is 200,000 Q13 63 64 What is the annual before tax profit if the number of units manufactured and sold is 210,000 Q14 65 220,000 Q15 Q16 66 What is the annual before tax profit if the number of units manufactured and sold is 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 73 What is the estimate of variable OH costs per labor hour? 74 75 What is the estimate of variable OH costs per machine hour? 76 77 What are the estimated total OH costs for a month with average labor hours and machine hours? 78 79 If output where 10% higher than average what is your estimate of total Mfg OH Costs? Q17 Q18 Q19 Q20 80 81 If output where 15% higher than average what is your estimate of the contribution margin per unit? Q21 82 83 84 Instructions Questions Example + G H N 0 P R s T Use this blue area to include regression results Put first cell of regression output here 4 7 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 Before you answer the required questions I suggest that you go to the Data worksheet. There I have a set of data that is related, but different to the data displayed in the Question worksheet. You will also see a X-Y scatter graph that includes a trend lin Instructions Questions Example + G H 1 j K L M N 0 P a R S T 23 24 25 Before you answer the required questions I suggest that you go to the Data worksheet. There I have a set of data that is related, but different to the data displayed in the Question worksheet. You will also see a X-Y scatter graph that includes a trend line. It is always a good idea to start your data analysis by looking at the raw data to check for outliers and also to see if there are any obvious trends. 26 27 28 29 30 You can change the X value of the displayed scatter graph by selecting the graph and then on the Menu Data >> Chart Source Data This will bring up a pop up window. Select the X-value box and then pick a new range of cells. The scatter plot will update automatically. Since I set the plot up . I to include a trend line and the equation of the trend line you will also see the results of a simple linear regression of the form OH - intercept + slope X 31 With this technique you can look at the pair-wise relationships between OH Costs and labor hours, machine hours & units manufactured. 32 33 34 35 36 37 Work Area 38 01 39 40 Q2 41 42 Q3 43 44 45 04 46 05 Instructions Questions Example +