question 16-21 please
EXCO V AutoSave A@E a311 A1 Cost Estimation (2) Insert Draw Home Page Layout Formulas Data Review View Tell me fx Show Watch Remove Arrows Formulas en Windo Insert Autosum Recently Financial Logical Text Date & Lookup & Math & More Function Used Time Reference Trie Functions Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left off? F61 These Blue cells may be Labor Hours 1,262 962 1 2 Data Table 1 3 Month 4 January February 6 March 1 April 8 May 9 June 30 July 11 August 12 September 13 October 14 November 15 December 16 Totals 17 18 MAX 29 MIN 20 21 vc 22 FC 1.318 1,280 942 1.188 1,114 1,006 1,344 1,196 Machine Hours 1,752 $ 1,362 $ 2.238 2.205 2013 2,2055 1,845 1778 1,641 $ 1.9715 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 Mig'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 760 Use the light blue cells of running a linear regre 792 13,164 1,344 760 584 2.238$ 1362 876 $ 230,335 189758 40.577 6,714 4086 2.628 Before you answer there set of data that is related see a X-Y scatter graph th It is always a good idea to also to see if there are an 24 These Blue cells may be used for calculations 25 26 Use Data Table 1 to answer the following Questions You can change the X valu Menu Data>> Char This will bring up a pop up plot will update automati trendline you will also se 27 28 The following assumptions apply unless an alternative is specifically given in a question 29 The Sales Price per unit Instidos Questions Data + 5 25.75 Ly el les Date & Lockups Math More Function Used Time Heference The Functions Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left off? F61 A Forms Watch Windo Calcun Ooo $ $ $ $ plot will update automatically. Since I set the ple trendline you will also see the results of a simple OH - Intercept slapeX 75.75 20.75 15.75 350.000 21% With this technique you can look at the pair wie machine hour & units manufactured $ 69.481 01 41 $ 12952 Q2 23 The following assumptions apply unless an alternative is specifically given in a question 29) The Sales Price per unit 30 H) Direct materials and direct labor costs per unit 31 ) Variable General and Administrative costs per unit, 12 iv) Fixed General and Administrative costs per month 33 ) Marginal tax rate on Income 34 3 Part 1. Answer these questions by applying the high-low method. Asume that the 36 Overhead costs are estimated using labor hours 37 38 What is the estimated variable cost per labor hour! 39 40 What is the estimated monthly fixed overhead cost? 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? 46 What is your estimate of the variable overhead cont 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? 53 54 How many units are required to break-even? SS S6 How many units are required to earn an annual before tax profit equal to Instructions Questions Data Ready Calculate 1.100 $213.262 Q3 45 193.9833 Q4 $ 51.9403 S 23 8097 Q6 $ 456,952 07 5 5.843428 Q 1 09 $ 750,000 1.724 Q10 6 otv A MacBook Air AutoSave a 2 BE Page Layout Home a311 A1 Cost Estimation (2) Insert Draw Formulas Data Review View Tell me fx V Insert Autosum Recently Financial Logical Text Date & Lookup & Math & Function Used More Time Reference Trig Create bome Functions Open recovered workbooks? Your recent changes were saved Do you want to continue working where you left off? F61 f Show Watch Remove Arrow Formulas Car Windo Calculation Gutions F 1,774 Q10 592,500 Q11 $1.930 012 2,271,467 Q13 CLASZ,674) Q14 C D 56 ow many units are required to earn an annual before tax profit equal to 57 S 750,000 sa What is the after tax profit required to earn a before tax profit of 59 $ 250.000 sow many units are required to earn an annual after tax profit equal to 5 750.000 61 62/hat is the before tax profit if the number of units manufactured and sold 150.000 64hat is the before tax profit if the number of units manufactured and sold 180,000 $ 65 66 That is the before tax profit if the number of its manufactured and sold is 210,000 67 art 2 68 order to complete the remaining questions use the regression capabilities of Excel to estimate 69 e following cost equation: Monthly OH 0b1 x labor hours b2 x machine hours 70 71 sed on the results, what is the estimate of monthly fived manufacturing OH costs 72 73 What is the estimate of variable OH costs per labor hour 74 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 output where 10% higher than average what is your estimate of total Mig OH Costs? (543.221 Q15 016 017 Q18 019 020 11 output where 15% higher than average what is your estimate of the contribution margin per unit? 021 Questions Data Instructions Calculate Ready otv *** EXCO V AutoSave A@E a311 A1 Cost Estimation (2) Insert Draw Home Page Layout Formulas Data Review View Tell me fx Show Watch Remove Arrows Formulas en Windo Insert Autosum Recently Financial Logical Text Date & Lookup & Math & More Function Used Time Reference Trie Functions Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left off? F61 These Blue cells may be Labor Hours 1,262 962 1 2 Data Table 1 3 Month 4 January February 6 March 1 April 8 May 9 June 30 July 11 August 12 September 13 October 14 November 15 December 16 Totals 17 18 MAX 29 MIN 20 21 vc 22 FC 1.318 1,280 942 1.188 1,114 1,006 1,344 1,196 Machine Hours 1,752 $ 1,362 $ 2.238 2.205 2013 2,2055 1,845 1778 1,641 $ 1.9715 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 Mig'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 760 Use the light blue cells of running a linear regre 792 13,164 1,344 760 584 2.238$ 1362 876 $ 230,335 189758 40.577 6,714 4086 2.628 Before you answer there set of data that is related see a X-Y scatter graph th It is always a good idea to also to see if there are an 24 These Blue cells may be used for calculations 25 26 Use Data Table 1 to answer the following Questions You can change the X valu Menu Data>> Char This will bring up a pop up plot will update automati trendline you will also se 27 28 The following assumptions apply unless an alternative is specifically given in a question 29 The Sales Price per unit Instidos Questions Data + 5 25.75 Ly el les Date & Lockups Math More Function Used Time Heference The Functions Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left off? F61 A Forms Watch Windo Calcun Ooo $ $ $ $ plot will update automatically. Since I set the ple trendline you will also see the results of a simple OH - Intercept slapeX 75.75 20.75 15.75 350.000 21% With this technique you can look at the pair wie machine hour & units manufactured $ 69.481 01 41 $ 12952 Q2 23 The following assumptions apply unless an alternative is specifically given in a question 29) The Sales Price per unit 30 H) Direct materials and direct labor costs per unit 31 ) Variable General and Administrative costs per unit, 12 iv) Fixed General and Administrative costs per month 33 ) Marginal tax rate on Income 34 3 Part 1. Answer these questions by applying the high-low method. Asume that the 36 Overhead costs are estimated using labor hours 37 38 What is the estimated variable cost per labor hour! 39 40 What is the estimated monthly fixed overhead cost? 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? 46 What is your estimate of the variable overhead cont 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? 53 54 How many units are required to break-even? SS S6 How many units are required to earn an annual before tax profit equal to Instructions Questions Data Ready Calculate 1.100 $213.262 Q3 45 193.9833 Q4 $ 51.9403 S 23 8097 Q6 $ 456,952 07 5 5.843428 Q 1 09 $ 750,000 1.724 Q10 6 otv A MacBook Air AutoSave a 2 BE Page Layout Home a311 A1 Cost Estimation (2) Insert Draw Formulas Data Review View Tell me fx V Insert Autosum Recently Financial Logical Text Date & Lookup & Math & Function Used More Time Reference Trig Create bome Functions Open recovered workbooks? Your recent changes were saved Do you want to continue working where you left off? F61 f Show Watch Remove Arrow Formulas Car Windo Calculation Gutions F 1,774 Q10 592,500 Q11 $1.930 012 2,271,467 Q13 CLASZ,674) Q14 C D 56 ow many units are required to earn an annual before tax profit equal to 57 S 750,000 sa What is the after tax profit required to earn a before tax profit of 59 $ 250.000 sow many units are required to earn an annual after tax profit equal to 5 750.000 61 62/hat is the before tax profit if the number of units manufactured and sold 150.000 64hat is the before tax profit if the number of units manufactured and sold 180,000 $ 65 66 That is the before tax profit if the number of its manufactured and sold is 210,000 67 art 2 68 order to complete the remaining questions use the regression capabilities of Excel to estimate 69 e following cost equation: Monthly OH 0b1 x labor hours b2 x machine hours 70 71 sed on the results, what is the estimate of monthly fived manufacturing OH costs 72 73 What is the estimate of variable OH costs per labor hour 74 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 output where 10% higher than average what is your estimate of total Mig OH Costs? (543.221 Q15 016 017 Q18 019 020 11 output where 15% higher than average what is your estimate of the contribution margin per unit? 021 Questions Data Instructions Calculate Ready otv ***
