attempt all

Mountain Goat Cycling Company The company is currently making the heavy-duty gear shifter that it installs on its most popular line of mountain bikes. The company's accounting department reports the following costs of making 8,000 Shifters each year. Rubric for Excel Lab #5 - 4 points for typed numbers (cell refe -2 points for incorrect formulas - 2 points for an incorrect IF statemen - 4 points for not using an IF statemet 4 points for an incorrect filename--s 20 points for submitting an Excel as Per umit S 12 10 5 Direct materials Direct labor Variable overhead Supervisor's salary Depreciation on special equip. Allocated general overhead Total cost Sonool 8,000 units $ 96,000 80,000 40,000 40,000 48,000 80,000 $ 384,000 6 10 48 $ The decision before management: An outside supplier has offered to sell 8,000 shifters a year to Mountain Goat Cycling Company for a price of only $31 each-or a total of $248,000. Should the company stop making the shifters internally and buy them form the outside supplier? Additional information for cell referencing: Per unit purchase price $31 Number of units 3,000 Total $243,000 Part 1 -- identify relevant costs. Place a "Y" next to any of the following costs that are relevant to the decision Part 1 -- identify relevant costs. Place a "Y" next to any of the following costs that are relevant to the decision Place an "N" next to any of the following costs not relevant to the decision Cost category Relevant? Hint Allocated general overhead Direct labor Direct materials Outside purchase price Special depreciation Supervisor's salary Variable overhead Part 2 -- cost analysis Of the relevant costs identified above, complete the cost analysis below of making or buying the shifter. Cell reference relevant costs Total relevant costs @ 8,000 units #1 above to complete table MAKE BUY Direct labor Direct materials Outside purchase price Supervisor's salary Variable overhead Profs Pro Tip: =IF statements Total cost S signs. In our case, we need to evaluate cell E49 but there are a few ways to think about it--mainly, is it positive or negative? Part 3 - decision making Should Mountain Goat buy the shifter? Write an =IF statement to make the decision in cell F54. We say: Excel, IF cell E49 is positive, return to me". ", if it is negative, return". How to translate the above statement in Excel language: =IF statement to answer question Part 4 -- opportunity cost =IF(LOGICAL_TEST, [value_if_true], [value_if_false]) Part 4 -- opportunity cost If the space now being used to produce the shifter would otherwise be idle, then Mountain Goat should continue to make its own shifters and the supplier's offer should be rejected. But, what if the space now being used to make shifters could be used for some other purpose? Assume the space now being used to make shifters could be used to produce a new cross-country bike that would generate a segment margin of $60,000 annually. New segment margin $60,000 What should be done? Considering idle space MAKE BUY Total annual cost (referenced from above S $ Opportunity cost--segment margin earned forgone on a potential new product Total cost Differential financial advantage or S disadvantage from outside supplier Prof's pro tip-opportunity cost can either be positive or negative, depending on where you put it. It should either be in MAKE or