Question

Could I get this with the formulas as well?

Instructions Enter your name and Access ID 'aa1234' format) in the cells above. The data required for this case will not appear until you do so. >> You are a financial manager for Zoom Corp., which manufactures bicycles. In the most recent fiscal year, > Zoom manufactured and sold 20,000 bicycles. Wheels, seats, and brake calipers are three components of the bicycles currently manufactured by Zoom. Three different vendors have proposed to provide those components to Zoom, and quoted prices (including shipping) for their delivery. Your task is to determine which, if any, of these proposals should be accepted Prepare a make vs. buy incremental analysis for each possible course of action in an Excel worksheet. Your >> grade will be based on the correctness of your answers, as well as the use of Excel. That is, where possible, you should use formulas to get your answers, rather than keyed-in values. See your instructor for help with Excel basics if you need it In a Word document, prepare a memo stating which of the proposals you suggest accepting, as well as the basis for your conclusions. Also identify any nonfinancial factors you should consider before accepting any of the outsourcing proposals. Attach both files to the Canvas assignment space by the due date of November 6th Below is cost data for Zoom's production of wheels, seats, and calipers. Outside suppliers have offered to provide wheels for $6.96, seats for $7.82, and calipers for $2.56 per piece. Both wheels and seats are branded with the Zoom logo, and that logo will need to be added at the Zoom factory at a cost of $0.50 each for any of these components that are outsourced. For all three components, 75% of the fixed costs are avoidable, and will be eliminated if the component's production is outsourced. In addition, seats and calipers are both produced out of the same small factory space. If both seats and calipers were outsourced, Zoom could lease the space out and increase net income by $6,000 per year, while eliminating all fixed costs for the two components. Wheels Seats Calipers Cost category $141,000 $53,000 $90,500 Direct materials Direct labor 94,000 73,000 41,500 Variable overhead 21,000 14,000 16,000 Fixed overhead 59,800 29,600 40,200 $315,800 $180,200 $177,600 Total cost 20,000 $9.01 Units produced 40,000 $7.90 80,000 $2.22 Cost per unit Hints: Prepare incremental analyses for each component separately. Make wheels vs. buy wheels, etc. Since there are additional implications to outsourcing both seats and calipers, do a make vs. buy analysis assuming both are outsourced. A correct solution, then, will likely have at least four incremental analyses. You may use the second tab of this workbook to prepare your solutions, or create a new file. Since you will be attaching this file to the Canvas assignment page, there are no file naming conventions you need to observe.

Answer 1

Statement showing the Relevant cost of Manufacturing

Particulers     Wheels Seats Calipers     Total

Direct Materials                                                                                        141000         53000       90500          284500

Direct Labor                                                                                               94000          73000        41500          208500

Variable over head                                                                                    21000          14000         16000         51000

Fixed overhead                                                                                          59800           40200        29600         129600

Total Cost of Manufacturing                                                                    315800           180200       177600     673600

Statement showing relevant cost of outsourcing                               

No of units per year                                                              (A)                  40000              20000      80000 140000

Purchase price per unit                                                        (B)                   6.96                 7.82          2.56

Branding cost per unit                                                          (c)                    0.50                 0.50            -

Total purchase price       (A*B)                                  (D)                  278400          156400     204800      639600

Total Branding cost                     (A*C)                                  (E)                  20000             10000            0            30000

Fixed Cost =Total Fixed cost (100%-75%)                          (F)                   14950             10050      7400           32400

Total cost of outsourcing   (D+E+F)                                                              313350           176450     212200     702000

Savings/Loss(Cost of Mfg-Cost of outsourcing)                                          2450              3750         -34600       -28400

Explanations:

if three products are outsourced, wheels and seats are resulting in to savings whereas the calipers is resulting into loss.

moreover total cost if outsourced three products is resulting in to increase of 28400.

Calculation of total cost if wheels is Manufactures and seats and calipers are outsourced

Total Manufacturing cost of Wheels                                                              315800

Cost of purchase of seats (20000*7.82)                                                    156400

Cost of Branding of seats(20000*0.50)                                                       10000

Cost of purchase of calipers (80000*2.56)                                                  204800

Total Cost                                                                                                         687000

Less: Lease income of Space                                                                      6000

Cost                                                                                                                 681000

Total cost if Manufacture all three internally                                                 673600

Net increase in Cost                                                                                      7400

Therefore it is advisable to Manufacturing all the three products internally instead of outsourcing partially or fully which are generating losses.