Bikes "R" Us is a company located in Columbus, OH that makes three different types of bicycles: Mountain Bikes, Road Bikes, and Dirt Bikes. 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 Dirt Bikes to Retailers for $500 each. Below is information for manufacturing each type of bike: Number of Bikes Materials Labor Variable Overhead Fixed Overhead Total Mountain Bikes 3,000 $150,000 240,000 90,000 50,650 $530,650 Road Bikes 2,000 $200,000 280,000 60,000 56,000 $596,000 Dirt Bikes 500 $85,000 100,000 50,000 30,000 $265,000 Bikes "R" Us is faced 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 (20 points) The short-term decision is regarding an order they received from a company, NZ Bikes, in New Zealand. NZ Bikes would like Bikes "R" Us to manufacture 10,000 Mountain Bikes and sell the Mountain Bikes at a price of $185 per bike. Currently, Bikes "R" Us sells it's Mountain Bikes to its retail 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 fixed 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 $100,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 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. (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 excel 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) Based on your incremental analysis, would you recommend to the CFO of Bikes "R" Us to reject or accept the order? Why? (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. Usina Excel prepare an incremental analysis for the special order with the new condition. (d) Based on your incremental analysis with the new shipping condition, would you recommend to the CFO of Bikes "R" Us to reject or accept the order? Why? PART 2 (20 points) The long-term decision is regarding the three Bikes currently produced by Bikes "R" Us. Cost-Volume-Profit (CVP) information about the three bikes is given below: Mountain Bikes Road Bikes Dirt Bikes Sales 3,000,000 $ 3,000,000 $ 250,000 Variable Expenses 480,000 540,000 235,000 Contribution Margin 2,520,000 2,460,000 15,000 Fixed Expenses 50,650 56,000 30,000 Net Income $ 2,469,350 $ 2,404,000 $ (15,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 by $15,000. The CFO came to you asking you to confirm it dropping the Dirt Bikes line of bikes would result in extra net income of $15,000. NOTE: If the Dirt Bikes line is dropped, none of the fixed costs for the Dirt Bikes would be eliminated. (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) Based on your incremental analysis, would the Net Income of Bikes "R" Us increase by $15,000 if they drop the Dirt Bikes line of bikes? If it will not increase by $15,000, by how much would the Net Income increase or decrease