Using the Data table from the first image, please answer the following questions

Aurinn DPP Cosy of 311 A1 Cost Estimation - Saved - e Saarch Cody Gabriel CG Fle Home Insert Page Layout Formula: Data REICH View Power Pivos OuickBooks Share Comments Ann + Palatino-Roma 14 AA 2 Wrap Text Geral Nerinal X X Cut L Copy Format Panter 27 % U2 Comma 2 Nomal 2 Conditional for Good Neutral Formwiling Table Bad Check Cell A- == Ida * Megell Calculation $ -% - Insert Delete format Clear Sortid File - Sclext- Clipboard Aline Numer Ideas D30 A B D E F G H 1 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 18 19 20 Max 21 Min Instructions Questions Data 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 $ 2,205 $ 1,845 $ 1,773 $ 1,641 $ 1,971 $ 1,752 $ 2.229 $ 22,986 $ OH Costs 221,304 189,758 223,883 230,335 214,621 219,848 206,957 204,047 217,754 221,287 194,962 198,559 2,543,313 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 of running a linear regression Use the blue area to show your Before you answer the requi a set of data that is related, I --------------- E 5 w 1443 PM A 4* 2000 121 Aurinn De Cosy of 311 A1 Cost Estimation - Saved - e Saarch Cody Gabriel C Fle Home Insert Page Layout Formula: Data REICH View Power Pivos QuickBooks Share Comments Ann + Palatino-Roma 14 AA 2 Wrap Text Geral FX X Cut Copy Putte 3 Format Panter Nommal 2 Neutral 2 Comma 2 Conditional forms Good Formwiling Table Nerinal Calculation Bad Check Cell 27 % == A- les * Megell $ -% - Insert Delete forma Clear Sortid File - Sext- Clipboard Fort Alignment Numer Ideas D30 A B D E F F H 25 26 Use Data Table 1 to answer the following Questions 27 $ $ $ S You can change the X value Menu Data >> Chart S This will bring up a pop up w scatter plot will update auto equation of the trend line ya OH = intercept 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 ) 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 38 What is the estimated variable cost per labor hour? 74.00 19.00 14.00 350,000 21% With this technique you can $ 69.480 Q1 39 $ 211,943 Q2 40 What is the estimated monthly fixed overhead cost? 41 42 Forecast monthly overhead costs for a month when labor hours are equal to 43 44 What is the average number of labor hours per unit? 1,100 $76,428 Q3 0.1996 Q4 45 Instructions Questions Data 14 O E: 5 W 3 PM A 14,200 21 Aurinn De Cosy of 311 A1 Cost Estimation - Saved - Cody Gabriela Fle Home Insert Page Layout Formula: Data REICH View Power Pivos OuickBooks Share Comment Ann + Pelatino-Roma 14 AA 2 Wrap Text Geral X Cut Copy 3 Format Panter Nomal 2 Neutral 2 Comma 2 Conditional for Goud Formwiling - T. Nerinal Calculation 27 % Bad Check Cell Putte A- Insert Delete Ida Clear Sortid Fill - Salut Clipboard Fort Alignment Number Fun Ideas D30 B D E F G H $ 38.5701 Q5 46 What is your estimate of the variable overhead cost per unit? 47 48 What is your estimate of the contribution margin per unit? Q6 49 Q7 50 What is your estimate of total fixed cost per month? 51 52 What is your estimate of total fixed cost per year? Q8 53 54 How many units are required to break-even? 09 55 S 750,000 Q10 S 750,000 Q11 $ 750,000 Q12 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 61 62 What is the before tax profit if the number of units manufactured and sold is 63 64 What is the before tax profit if the number of units manufactured and sold is 65 66. What is the hefore tax profit if the number of units manufactured and sold is Instructions 150,000 Q13 180,000 Q14 210 000 015 Questions Data - 14 P O 5 W A 0:44 PM 0714/2000 121 Aurinn DPE Cosy of 311 A1 Cost Estimation - Saved - p e Saarch Cody Gabriel Fle Home Insert Page Layout Formula: Data REICH View Power Pivos QuickBooks Share Comments Ann + Palatino- 14 AA 2 Wrap Text Geral Nerinal FX X Cut L Copy Paste 3 format Panter Clipboard Comma 2 Conditional forms Good Formwiling Table Nomal 2 Neutral 27 % Bad Check Cell BI. Sortid A- Ida == * Murger & Cab Calculation $ -% - Insert Delete forma Clear Aline Numer Editing Ideas D36 B D E F G H 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 210,000 Q15 66 What is the 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? Q16 72 Q17 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? Q18 76 77 What are the estimated total OH costs for an average month based on the cost model? Q19 78 Q20 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? Q21 Instructions Questions Data P EI w A 0:44 PM 0714/2000 121 Aurinn DPP Cosy of 311 A1 Cost Estimation - Saved - e Saarch Cody Gabriel CG Fle Home Insert Page Layout Formula: Data REICH View Power Pivos OuickBooks Share Comments Ann + Palatino-Roma 14 AA 2 Wrap Text Geral Nerinal X X Cut L Copy Format Panter 27 % U2 Comma 2 Nomal 2 Conditional for Good Neutral Formwiling Table Bad Check Cell A- == Ida * Megell Calculation $ -% - Insert Delete format Clear Sortid File - Sclext- Clipboard Aline Numer Ideas D30 A B D E F G H 1 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 18 19 20 Max 21 Min Instructions Questions Data 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 $ 2,205 $ 1,845 $ 1,773 $ 1,641 $ 1,971 $ 1,752 $ 2.229 $ 22,986 $ OH Costs 221,304 189,758 223,883 230,335 214,621 219,848 206,957 204,047 217,754 221,287 194,962 198,559 2,543,313 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 of running a linear regression Use the blue area to show your Before you answer the requi a set of data that is related, I --------------- E 5 w 1443 PM A 4* 2000 121 Aurinn De Cosy of 311 A1 Cost Estimation - Saved - e Saarch Cody Gabriel C Fle Home Insert Page Layout Formula: Data REICH View Power Pivos QuickBooks Share Comments Ann + Palatino-Roma 14 AA 2 Wrap Text Geral FX X Cut Copy Putte 3 Format Panter Nommal 2 Neutral 2 Comma 2 Conditional forms Good Formwiling Table Nerinal Calculation Bad Check Cell 27 % == A- les * Megell $ -% - Insert Delete forma Clear Sortid File - Sext- Clipboard Fort Alignment Numer Ideas D30 A B D E F F H 25 26 Use Data Table 1 to answer the following Questions 27 $ $ $ S You can change the X value Menu Data >> Chart S This will bring up a pop up w scatter plot will update auto equation of the trend line ya OH = intercept 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 ) 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 38 What is the estimated variable cost per labor hour? 74.00 19.00 14.00 350,000 21% With this technique you can $ 69.480 Q1 39 $ 211,943 Q2 40 What is the estimated monthly fixed overhead cost? 41 42 Forecast monthly overhead costs for a month when labor hours are equal to 43 44 What is the average number of labor hours per unit? 1,100 $76,428 Q3 0.1996 Q4 45 Instructions Questions Data 14 O E: 5 W 3 PM A 14,200 21 Aurinn De Cosy of 311 A1 Cost Estimation - Saved - Cody Gabriela Fle Home Insert Page Layout Formula: Data REICH View Power Pivos OuickBooks Share Comment Ann + Pelatino-Roma 14 AA 2 Wrap Text Geral X Cut Copy 3 Format Panter Nomal 2 Neutral 2 Comma 2 Conditional for Goud Formwiling - T. Nerinal Calculation 27 % Bad Check Cell Putte A- Insert Delete Ida Clear Sortid Fill - Salut Clipboard Fort Alignment Number Fun Ideas D30 B D E F G H $ 38.5701 Q5 46 What is your estimate of the variable overhead cost per unit? 47 48 What is your estimate of the contribution margin per unit? Q6 49 Q7 50 What is your estimate of total fixed cost per month? 51 52 What is your estimate of total fixed cost per year? Q8 53 54 How many units are required to break-even? 09 55 S 750,000 Q10 S 750,000 Q11 $ 750,000 Q12 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 61 62 What is the before tax profit if the number of units manufactured and sold is 63 64 What is the before tax profit if the number of units manufactured and sold is 65 66. What is the hefore tax profit if the number of units manufactured and sold is Instructions 150,000 Q13 180,000 Q14 210 000 015 Questions Data - 14 P O 5 W A 0:44 PM 0714/2000 121 Aurinn DPE Cosy of 311 A1 Cost Estimation - Saved - p e Saarch Cody Gabriel Fle Home Insert Page Layout Formula: Data REICH View Power Pivos QuickBooks Share Comments Ann + Palatino- 14 AA 2 Wrap Text Geral Nerinal FX X Cut L Copy Paste 3 format Panter Clipboard Comma 2 Conditional forms Good Formwiling Table Nomal 2 Neutral 27 % Bad Check Cell BI. Sortid A- Ida == * Murger & Cab Calculation $ -% - Insert Delete forma Clear Aline Numer Editing Ideas D36 B D E F G H 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 210,000 Q15 66 What is the 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? Q16 72 Q17 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? Q18 76 77 What are the estimated total OH costs for an average month based on the cost model? Q19 78 Q20 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? Q21 Instructions Questions Data P EI w A 0:44 PM 0714/2000 121