16- 21 please

F77 E . C 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 s 223,883 7 April 1280 2,205 $ 230,335 8 May 942 2013 $ 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,641s 217,754 13 October 1.196 1,971 $ 221,287 14 November 1,752 $ 194,962 15 December 792 2,229 $ 198,559 16 Totals 13,164 22,986 $ 2,543,313 17 18 MAX 1,344 2,238 $ 230,335 19 MIN 760 1362 189758 20 584 876 $ 40,577 21 VE 22 FC 23 24 These Blue cells may be used for calculations 25 26 Use Data Table i to answer the following Questions 27 28 The following assumptions apply unless an alternative is specifically given in a question 29 ) The Sales Price per unit Instructions Questi Data + Units Mfgd 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 760 6,714 4086 2,628 75.75 Used UU.LU Tinte LUULUUS Wall & Reference Trig More Functions Remove A formas Show Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left off? F77 B E $ plot will upd trendline ya C D 28 The following assumptions apply unless an alternative is specifically given in a question 29 i) The Sales Price per unit 30 11) Direct materials and direct labor costs per unit. 31 ) 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? $ $ 75.75 20.75 15.75 350,000 21% With this tec machine hou $ 69.481 01 39 40 What is the estimated monthly fixed overhead cost? $ 136,952 Q2 41 1,100 $213,382 Q3 193.9833 04 $ 51.9403 Q5 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? 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 So What is your estimate of total fixed cost per month? 51 52 What is your estimate of total fixed cost per year? S 23.8097 486,952 5,843,428 1 54 How many units are required to break-even? 55 56 Hasmananc ind i carn an annual for a confianka 750m 1024 010 V Insert Function AutoSum Recently Financial Logical Used Text More Date & Lookup & Math & Time Reference Trig Functions Romove Arrows Show Formulas Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left off? F77 $ 750,000 1,774 Q10 $ 750,000 592,500 Q11 55 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 60 How many units are required to earn an annual after tax profit equal to 62 What is the before tax profit if the number of units manufactured and sold is 59 $ 750,000 $1,930 Q12 61 150,000 $ (2,271,967) Q13 63 64 What is the before tax profit if the number of units manufactured and sold is 180,000 $ (1,557,674) Q14 65 (843,382) Q15 66 What is the before tax profit if the number of units manufactured and sold is 210,000 $ 67 Part 2 58 In order to complete the remaining questions use the regression capabilities of Excel to estimate 69 the following cost equation: Monthly OH - bo + b1 x labor hours + b2 x machine hours 71 Based on the results, what is the estimate of monthly fixed manufacturing OH costs? 73 What is the estimate of variable OH costs per labor hour? 70 016 72 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? 019 78 79 If output where 10% higher than average what is your estimate of total Mig OH Costs? 020 BO 021 81 If output where 15% higher than average what is your estimate of the contribution margin per unit? 82 Instructions Questions Data F77 E . C 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 s 223,883 7 April 1280 2,205 $ 230,335 8 May 942 2013 $ 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,641s 217,754 13 October 1.196 1,971 $ 221,287 14 November 1,752 $ 194,962 15 December 792 2,229 $ 198,559 16 Totals 13,164 22,986 $ 2,543,313 17 18 MAX 1,344 2,238 $ 230,335 19 MIN 760 1362 189758 20 584 876 $ 40,577 21 VE 22 FC 23 24 These Blue cells may be used for calculations 25 26 Use Data Table i to answer the following Questions 27 28 The following assumptions apply unless an alternative is specifically given in a question 29 ) The Sales Price per unit Instructions Questi Data + Units Mfgd 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 760 6,714 4086 2,628 75.75 Used UU.LU Tinte LUULUUS Wall & Reference Trig More Functions Remove A formas Show Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left off? F77 B E $ plot will upd trendline ya C D 28 The following assumptions apply unless an alternative is specifically given in a question 29 i) The Sales Price per unit 30 11) Direct materials and direct labor costs per unit. 31 ) 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? $ $ 75.75 20.75 15.75 350,000 21% With this tec machine hou $ 69.481 01 39 40 What is the estimated monthly fixed overhead cost? $ 136,952 Q2 41 1,100 $213,382 Q3 193.9833 04 $ 51.9403 Q5 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? 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 So What is your estimate of total fixed cost per month? 51 52 What is your estimate of total fixed cost per year? S 23.8097 486,952 5,843,428 1 54 How many units are required to break-even? 55 56 Hasmananc ind i carn an annual for a confianka 750m 1024 010 V Insert Function AutoSum Recently Financial Logical Used Text More Date & Lookup & Math & Time Reference Trig Functions Romove Arrows Show Formulas Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left off? F77 $ 750,000 1,774 Q10 $ 750,000 592,500 Q11 55 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 60 How many units are required to earn an annual after tax profit equal to 62 What is the before tax profit if the number of units manufactured and sold is 59 $ 750,000 $1,930 Q12 61 150,000 $ (2,271,967) Q13 63 64 What is the before tax profit if the number of units manufactured and sold is 180,000 $ (1,557,674) Q14 65 (843,382) Q15 66 What is the before tax profit if the number of units manufactured and sold is 210,000 $ 67 Part 2 58 In order to complete the remaining questions use the regression capabilities of Excel to estimate 69 the following cost equation: Monthly OH - bo + b1 x labor hours + b2 x machine hours 71 Based on the results, what is the estimate of monthly fixed manufacturing OH costs? 73 What is the estimate of variable OH costs per labor hour? 70 016 72 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? 019 78 79 If output where 10% higher than average what is your estimate of total Mig OH Costs? 020 BO 021 81 If output where 15% higher than average what is your estimate of the contribution margin per unit? 82 Instructions Questions Data