31 points 5 Bikes "R" Us makes three different types of bicycles: Mountain Bikes, Road Bikes, and Dirt Bikes. Bikes "R" Us is laced with making two important decisions -- one decision will impact the company in the short-term, and the other decision will impact the company in the long-term. PART 1: The shortterm decision is regarding an order they received from a company, NZ Bikes, in New Zealand. Bikes "R" Us normally sells its Mountain Bikes to retailers for $1,000 each, its Road Bikes to retailers for $1,500 each, and its Di rt Bikes to Retailers for $500 each. Below is the manufacturing cost information for each type of bike: Mountain Bike Road Bike Dirt Bike Number of Bikes Produced 2,950 2,000 500 Materials $150,000 $200,000 $85,000 Labor $240,000 $280,000 $100,000 Variable Overhead $90,000 $60,000 $50,000 Fixed Overhead $ $m $w Total $530,650 $596,000 $265,000 NZ Bikes would like Bikes "R" Us to manufacture 10,000 Mountain Bikes and sell the Mountain Bikes at a price of $135 per bike. Currently, Bikes "R" Us sells it's Mountain Bikes to its regular customers for $1.000 per bike. If Bikes "R" Us accepts the offer, NZ Bikes would sell the bikes under its own name in New Zealand and would only impact markets not served by Bikes "R" Us. Bikes "R" Us is currently manufacturing its Bikes at full capacity and accepting the offer would cause its xed costs to increase from $50,650 to $60,000 to pay for the expansion of the factory and extra machinery to manufacture the 10,000 additional Mountain Bikes. In addition, if Bikes "R" Us accepts the order, they will have to pay $120,000 in shipping costs to ship the Bikes to New Zealand from Columbus, OH. If the order is accepted, Bill Gates [the top salesman for Bikes " R" Us) would also have to be paid a commission of 10% of the sales revenue from the order, because NZ Bikes is Bill Gates' top client and came to Bill Gates with theoffer. The CFO of Bikes "R" Us knows that you are the best Managerial Accountant in the company and would like your advice on whether to reject or accept the offer. The CFO of Bikes "R" Us knows that you are the best Managerial Accountant in the company and would like your advice on whether to reject or accept the offer. Use the information above to answer parts fol - ldlfor Part 1. (a) Using Excel, prepare an incremental analysis for the special order. NOTE: in order to receive full credit, you must create an incremental analysis table in eccel and utilize formulas in your table. You must use at least 4 formulas. Each formula will be worth 1 point each, up to 4 points. [b] Answerthe 'll in the blank' question under this problem related to 1(a). (c) Assume that NZ Bikes changed the offer to where they would pay for the shipping costs and Bikes "R" Us would not have to pay for the shipping costs. Using Excel, prepare an incremental analysis for the special order with the new condition. NOTE: in order to receive full credit, you must create an incremental analysis table in accel and utilize formulas in your table. You must use at least 4 formulas. Each formula will be worth 1 point each, up to 4 points. [d] Answerthe 'll in the blank' question under this problem related to 1(c]. PART 2: The longterm decision is regarding the three Bikes currently produced by Bikes " R" Us. CostVolumeProt (CVP) information about the three bikes is given below: Mountain Bikes Road Bikes Dirt Bikes Sales 3,000,000 3,000,000 252 .000 Variable Expenses 480,000 540,000 235,000 Contribution Margin 2,520,000 2,460,000 17,000 Fixed Expenses gm EM Net Income 2,469,350 2,404,000 (13,000) When the CFO saw the CVP information above he immediately wanted to discontinue the Dirt Bikes line because he believes the company would increase their total Net Income if they discontinue the Dirt Bikes. The CFO came to you asking you to confirm if dropping the Dirt Bikes line of bikes would result in extra Net Income. NOTE: If the Dirt Bikes line is dropped, none of the fixed costs for the Dirt Bikes would be eliminated. Use the information above to answer parts (a) - (b) for Part 2. (a) Using Excel, prepare an incremental analysis for the elimination of the Dirt Bikes Product Line. NOTE: In order to receive full credit, you must create an incremental analysis table in excel and utilize formulas in your table. You must use at least 7 formulas. Each formula will be worth 1 point each, up to 7 points. (b) Answer the 'fill in the blank' question under this problem related to 2(a). Instructions: Using a spreadsheet with formulas, saved in Microsoft Excel format complete Parts I and 2; where applicable, answer the 'fill in the blank' questions related to Parts I and 2.3 points Based on your incremental analysis for Part 1(a). Bikes "R" Us should choose your answer... V the special order because Net Income would choose your answer... V 3 points Based on your incremental analysis for Part 1(c), Bikes "R" Us should choose your answer... V the special order because Net Income would choose your answer... V 3 points Based on your incremental analysis for Part 2(a). Bikes "R" Us should choose your answer... V producing and selling the Dirt Bikes line because Net Income would choose your answer... V by type your answer... if the line is eliminated. (Do not use commas. decimals, or dollar signs in your answer. For mmple for 5 5,000 type 5000}