In excel, please complete the following
2. The Less is More" company manufactures swimsuits. The company is considering expanding to the bathrobe market. The project will last 6 years. The proposed investment plan includes the following: Purchase a new machine: The cost of the machine is $1,600,000 and its expected life span is 8 years. The machine will be depreciated using straight line depreciation over its 8-year life. The CFO of the company plans to sell the machine after 6 years for $250,000. Advertising campaign: The head of the marketing department estimates that the advertising campaign will costs $500,000 annually The fixed cost of the new department will be $400,000 annually. Variable costs are estimated at $20 per bathrobe but because of the expected rise in labor costs, they are expected to rise at 3% per year. Bathrobes will be sold at $45 in the first year. The company estimates that is can raise the price of the bathrobes by $4 in each of the following years. The discount rate is 10% The tax rate is 21%. O You can receive tax credits for losses. a. How many robes must the bathrobe department sell to break even? [20 points] i. Hint: you may use goal seek to find the break-even point. b. Plot a graph in which the NPV is the dependent variable of annual bathrobe sales. Vary the production in units from 30,000 to 450,000 (in increments of 1000s). [5 points) 2. The Less is More" company manufactures swimsuits. The company is considering expanding to the bathrobe market. The project will last 6 years. The proposed investment plan includes the following: Purchase a new machine: The cost of the machine is $1,600,000 and its expected life span is 8 years. The machine will be depreciated using straight line depreciation over its 8-year life. The CFO of the company plans to sell the machine after 6 years for $250,000. Advertising campaign: The head of the marketing department estimates that the advertising campaign will costs $500,000 annually The fixed cost of the new department will be $400,000 annually. Variable costs are estimated at $20 per bathrobe but because of the expected rise in labor costs, they are expected to rise at 3% per year. Bathrobes will be sold at $45 in the first year. The company estimates that is can raise the price of the bathrobes by $4 in each of the following years. The discount rate is 10% The tax rate is 21%. O You can receive tax credits for losses. a. How many robes must the bathrobe department sell to break even? [20 points] i. Hint: you may use goal seek to find the break-even point. b. Plot a graph in which the NPV is the dependent variable of annual bathrobe sales. Vary the production in units from 30,000 to 450,000 (in increments of 1000s). [5 points)
