1. The assignment problem is shown on the Questions worksheet 2. Spaces for your answers are indicated by cells highlighted in yellow, e.g., => Do not modify or enter any forumulas in cells highlighted in these colors => Do not modify or enter any forumulas in cells highlighted in light green => You may use cells highlighted in light blue to perform necessary calculations if you need more space => 3. Please put the answers in the appropriate cells. You may reference another cell that contains the results of a calculation where appropriate. 4. Your calculations should be accessible from your work sheets. I.e., do not do the supporting work elsewhere and then enter answers without indicating how the answers were determined. 5. You may discuss the assignment with your classmates. It is not a group assignment and I expect everyone to work through the material and submit their own solutions. 6. If you need additional work space you may add 1 additional work sheet to the note book. This sheet must be placed Instructions Questions Data + B C D E Units Mfg'd A 1 Month 2 January 3 February 4 March 5 April 6 May 7 June 8 July 9 August 10 September 11 October 12 November 13 December 14 Labor Hours 1,262 962 1,318 1,280 942 1,188 1,114 1,006 1,344 1,196 760 792 Machine Hours 1,752 $ 1,362 $ 2,238 $ 2,205 $ 2,013 $ 2,205 $ 1,845 $ 1,773 $ 1,641 $ 1,971 $ 1,752 $ 2,229 $ OH Costs 228,504 196,958 231,083 237,535 221,821 227,048 214,157 211,247 224,954 228,487 204,162 205,759 5,256 4,086 6,714 6,615 5,652 6,615 5,635 5,319 4,923 5,913 4,560 4,752 15 16 17 OH Costs 18 $250,000 19 20 21 $200,000 22 23 24 25 $150,000 26 27 y = 12.271x + 151777 R2 = 0.6951 28 $100,000 29 30 31 32 33 $50,000 34 35 1,000 2,000 3,000 4,000 5,000 6,000 7,000 8,000 36 37 E G H K M These Blue cells may be used for calculations 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 Use the light blue cells above to show the results of running a linear regression A B D 1 2 Data Table 1 3 Month Labor Hours Machine Hours OH Costs 4 January 1,262 1,752 $ 221,304 5 February 962 1,362 $ 189,758 6 March 1,318 2.238 $ 223,883 7 April 1,280 2.205 $ 230.335 8 May 942 2,013 $ 214,621 9 June 1,188 2,205 $ 219,848 10 July 1,114 1,845 $ 206,957 11 August 1,006 1.773 $ 204,047 12 September 1,344 1,641 $ 217,754 13 October 1,196 1,971 $ 221,287 14 November 760 1,752 $ 194,962 15 December 792 2.229 $ 198,559 16 Totals 13,164 22,986 $ 2,543,313 17 18 19 20 21 22 23 24 These Blue cells may be used for calculations 25 26 Use Data Table 1 to answer the following Questions 27 28 The following assumptions apply unless an alternative is specifically given in a question 29 i) The Sales Price per unit 30 ii) 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 34 35 Part 1. Answer these questions by applying the high-low method. Assume that the 36 Overhead costs are estimated using labor hours. 37 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. 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 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 #VALUE! #VALUE! #VALUE! $ 350,000 21% With this technique you can look at the pair-wise relationships between OH Costs and labor hours, Q1 These Blue cells may be used for calculations Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10 Q11 38 What is the estimated variable cost per labor hour? 39 40 What is the estimated monthly fixed overhead cost? 41 42 Forecast monthly overhead costs for a month when labor hours are equal to 1,100 43 44 What is the average number of labor hours per unit? 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 50 What is your estimate of total fixed cost per month? 51 52 What is your estimate of total fixed cost per year? 53 54 How many units are required to break-even? 55 56 How many units are required to earn an annual before tax profit equal to $ 750,000 57 58 What is the after tax profit required to earn a before tax profit of $ 750,000 59 60 How many units are required to earn an annual after tax profit equal to $ 750.000 61 62 What is the before tax profit if the number of units manufactured and sold is 150.000 63 64 What is the before tax profit if the number of units manufactured and sold is 180,000 65 66 What is the before tax profit if the number of units manufactured and sold is 210,000 67 Part 2 68 In order to complete the remaining questions use the regression capabilities of Excel to estimate 69 the following cost equation: Monthly OH = b0 + b1x 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 an average month based on the cost model? 78 79 IF output where 10% higher than average what is your estimate of total Mfg OH Costs? 80 81 If output where 15% higher than average what is your estimate of the contribution margin per unit? 82 83 Q12 Q13 Q14 Q15 Q16 Q17 Q18 Q19 Q20 Q21 1. The assignment problem is shown on the Questions worksheet 2. Spaces for your answers are indicated by cells highlighted in yellow, e.g., => Do not modify or enter any forumulas in cells highlighted in these colors => Do not modify or enter any forumulas in cells highlighted in light green => You may use cells highlighted in light blue to perform necessary calculations if you need more space => 3. Please put the answers in the appropriate cells. You may reference another cell that contains the results of a calculation where appropriate. 4. Your calculations should be accessible from your work sheets. I.e., do not do the supporting work elsewhere and then enter answers without indicating how the answers were determined. 5. You may discuss the assignment with your classmates. It is not a group assignment and I expect everyone to work through the material and submit their own solutions. 6. If you need additional work space you may add 1 additional work sheet to the note book. This sheet must be placed Instructions Questions Data + B C D E Units Mfg'd A 1 Month 2 January 3 February 4 March 5 April 6 May 7 June 8 July 9 August 10 September 11 October 12 November 13 December 14 Labor Hours 1,262 962 1,318 1,280 942 1,188 1,114 1,006 1,344 1,196 760 792 Machine Hours 1,752 $ 1,362 $ 2,238 $ 2,205 $ 2,013 $ 2,205 $ 1,845 $ 1,773 $ 1,641 $ 1,971 $ 1,752 $ 2,229 $ OH Costs 228,504 196,958 231,083 237,535 221,821 227,048 214,157 211,247 224,954 228,487 204,162 205,759 5,256 4,086 6,714 6,615 5,652 6,615 5,635 5,319 4,923 5,913 4,560 4,752 15 16 17 OH Costs 18 $250,000 19 20 21 $200,000 22 23 24 25 $150,000 26 27 y = 12.271x + 151777 R2 = 0.6951 28 $100,000 29 30 31 32 33 $50,000 34 35 1,000 2,000 3,000 4,000 5,000 6,000 7,000 8,000 36 37 E G H K M These Blue cells may be used for calculations 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 Use the light blue cells above to show the results of running a linear regression A B D 1 2 Data Table 1 3 Month Labor Hours Machine Hours OH Costs 4 January 1,262 1,752 $ 221,304 5 February 962 1,362 $ 189,758 6 March 1,318 2.238 $ 223,883 7 April 1,280 2.205 $ 230.335 8 May 942 2,013 $ 214,621 9 June 1,188 2,205 $ 219,848 10 July 1,114 1,845 $ 206,957 11 August 1,006 1.773 $ 204,047 12 September 1,344 1,641 $ 217,754 13 October 1,196 1,971 $ 221,287 14 November 760 1,752 $ 194,962 15 December 792 2.229 $ 198,559 16 Totals 13,164 22,986 $ 2,543,313 17 18 19 20 21 22 23 24 These Blue cells may be used for calculations 25 26 Use Data Table 1 to answer the following Questions 27 28 The following assumptions apply unless an alternative is specifically given in a question 29 i) The Sales Price per unit 30 ii) 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 34 35 Part 1. Answer these questions by applying the high-low method. Assume that the 36 Overhead costs are estimated using labor hours. 37 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. 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 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 #VALUE! #VALUE! #VALUE! $ 350,000 21% With this technique you can look at the pair-wise relationships between OH Costs and labor hours, Q1 These Blue cells may be used for calculations Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10 Q11 38 What is the estimated variable cost per labor hour? 39 40 What is the estimated monthly fixed overhead cost? 41 42 Forecast monthly overhead costs for a month when labor hours are equal to 1,100 43 44 What is the average number of labor hours per unit? 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 50 What is your estimate of total fixed cost per month? 51 52 What is your estimate of total fixed cost per year? 53 54 How many units are required to break-even? 55 56 How many units are required to earn an annual before tax profit equal to $ 750,000 57 58 What is the after tax profit required to earn a before tax profit of $ 750,000 59 60 How many units are required to earn an annual after tax profit equal to $ 750.000 61 62 What is the before tax profit if the number of units manufactured and sold is 150.000 63 64 What is the before tax profit if the number of units manufactured and sold is 180,000 65 66 What is the before tax profit if the number of units manufactured and sold is 210,000 67 Part 2 68 In order to complete the remaining questions use the regression capabilities of Excel to estimate 69 the following cost equation: Monthly OH = b0 + b1x 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 an average month based on the cost model? 78 79 IF output where 10% higher than average what is your estimate of total Mfg OH Costs? 80 81 If output where 15% higher than average what is your estimate of the contribution margin per unit? 82 83 Q12 Q13 Q14 Q15 Q16 Q17 Q18 Q19 Q20 Q21