Please help me to complete this excel assignment. And there's a sample at the bottom.

Please give me an answer before 11:00 pm tonight.

Thanks a lot!

(only look at incremental cash flows) Test Market Equipment Investment Installation Costs Life Book Value Salvage Value NWC Increase Additional Sales Increase in COGS Increase in Selling Exp. Depreciation per year Tax Rate Required Rate 1500 13000 2000 4 600 800 1600 14000 4000 2000 3100 35% 12% Scenario Summary Current Values: Worst Expected Best Changing Cells: Unit_Sales 25,000 10,000 25,000 40,000 Tax_Rate 35% 32% 35% 36% Result Cells: NPV $492,810.97 ($933,368.68) $492,810.97 $1,862,188.68 IRR 23% -14% 23% 52% Notes: Current Values column represents values of changing cells at time Scenario Summary Report was created. Changing cells for each scenario are highlighted in gray. Data Section Initial Investment Variable Costs (per ton) Fixed Costs Selling Price Depreciation (per year) Book Value Change in NWC Unit Sales Salvage Value Discount Rate Tax Rate $ $ $ $ $ $ $ 1,500,000.00 90.00 300,000.00 130.00 $280,000.00 100,000.00 90,000.00 25,000 50,000.00 12% 35% Salvage Value 50,000 60,000 70,000 To change or delete cell name Formulas --> Name Manager Initial Outlay Initial Investment $ Increase in NWC $ Initial Outlay Time 0 1 2 3 $ OCF (1,500,000.00) Sales Revenue $ (90,000.00) - Variable Costs $ - Fixed Costs $ -Depreciation $ EBIT (=EBT) $ - Taxes @35% $ NI $ + Depreciation (1,590,000.00) OCF $ Terminal CF 3,250,000.00 Recovery in NWC (2,250,000.00) After-tax Salvage Value (300,000.00) (280,000.00) 420,000.00 (147,000.00) 273,000.00 $280,000.00 553,000.00 Terminal CF $ $ $ $ CF (1,590,000.00) 553,000.00 Data --> What-If Analysis --> Data Table Combo Box Two Cases for Discout Rate Case 1 Case 2 Indicator Cell 12% 13% 1 Insert Select the combo box and put it next to cell B11 Right-click on the combo box Choose Format Control Input Range: Text we want to see in the drop down menu Cell Link: The indicator cell Drop down lines: Number of cases Option button and Group Box Three Cases for Market Value Case 1 Case 2 Case 3 Indicator Cell 50000 60000 70000 1 Insert Select the option button and put it next to cell B8. Do it three times to make three option buttons Right-click on the option button and choose Edit Text. Change option button names into 50,000, 60,000 and 70,000. Right-click on the opiton button and choose Format Control Cell Link: The indicator cell Go to Developer > Insert Select group box and include all three option buttons Scenario Analysis Three Scenarios for Sales and Tax Rate unit sales tax rate Worst 10000 Expected 25000 Best 40000 0.32 0.35 0.36 Create a scenario summary for the NPV and the IRR. Data ---> What-If Analysis ---> Scenario Manager Create each scenario by inputing name, changing cells values (input cells that change) To create the summary, click on Summary and select the output cell (F31, F32 in our example) CF $ $ 90,000.00 67,500.00 $ 157,500.00 $492,810.97 23.47% FIN 641-Excel Assignment This assignment is due by 10 am on Thursday, March 10. You need to use the Excel Template I posted. The assignment should be submitted on Blackboard. No late assignment will be accepted. Enjoy the problems! Capital Budgeting Ingersoll Bakery wants to introduce a new product, pepperoni bread. This product was test marketed six months ago at a cost of $1,500 with promising results. Ingersoll needs to buy a machine to produce this new product. The price of the machine is $13,000 and installation cost is $2,000. The machine will be depreciated using the straight-line method to a book value of $600 over the four-year life of the project. The machine has an expected salvage value of $800 at the end of its four-year life. To produce this new product Ingersoll will have to invest an additional $1,600 in net working capital. Current annual product sales are $45,000. Pepperoni bread sales are projected at $15,000, but $1,000 will be sales from customers who switched from other Ingersoll products. Cost of goods sold will increase by $4,000 and selling expense will increase by $2,000. The tax rate is 35% and the required rate of return is 12%. 1. Calculate the initial outlay, annual operating cash flow and terminal cash flows. Complete the table with the timeline and calculate the NPV and IRR of the project. (8 points) 2. Create a Data Table that shows changes in NPV, when required rate changes (8%, 10%, 12%, 14%, 16%). (8 points) 3. The required return is estimated 12% or 16%. Create combo box for the two different required returns. (8 points) 4. The company expects the equipment to be actually sold for $800, $1,200 or $2,000 after 4 years. Create option buttons for the three different market values and group together the option buttons. (8 points) 5. Create a scenario summary table (using the scenario manager tool) to show what happens to the NPV and IRR for the following three scenarios for sales and tax rate. (8 points) Three Scenarios for sales and tax rate: Worst Expected Best sales 9000 14000 19000 tax rate 0.31 0.35 0.38 FIN 641-Excel Assignment This assignment is due by 10 am on Thursday, March 19. You need to use the Excel Template I posted. The assignment should be submitted on Blackboard. No late assignment will be accepted. Enjoy the problems! Capital Budgeting Ingersoll Bakery wants to introduce a new product, pepperoni bread. This product was test marketed six months ago at a cost of $1,500 with promising results. Ingersoll needs to buy a machine to produce this new product. The price of the machine is $7,000 and installation cost is $1,000. The machine will be depreciated using the straight-line method to a zero book value over the five-year life of the project. The machine has an expected salvage value of $0 at the end of its five-year life. To produce this new product Ingersoll will have to invest an additional $1,500 in net working capital. Current annual product sales are $45,000. Pepperoni bread sales are projected at $15,000, but $5,000 will be sales from customers who switched from other Ingersoll products. Cost of goods sold will increase by $4,000 and selling expense will increase by $2,000. The tax rate is 34% and the required rate of return is 15%. 1. Calculate the initial outlay, annual operating cash flow and terminal cash flows. Complete the table with the timeline and calculate the NPV and IRR of the project. (8 points) 2. Create a Data Table that shows changes in NPV, when required rate changes (12%, 13%, 14%, 15%, 16%). (8 points) 3. The required return is estimated 15% or 16%. Create combo box for the two different required returns. (8 points) 4. The company expects the equipment to be actually sold for $0, $1,000 or $2,000 after 5 years. Create option buttons for the three different market values and group together the option buttons. (8 points) 5. Create a scenario summary table (using the scenario manager tool) to show what happens to the NPV and IRR for the following three scenarios for sales and tax rate. (8 points) Three Scenarios for sales and tax rate: Worst Expected Best sales 8000 10000 12000 tax rate 0.33 0.34 0.35 Scenario Summary Current Values: Worst Expected Changing Cells: SALES $10,000.00 $8,000.00 $10,000.00 TAX 34% 33% 34% Result Cells: NPV $1,691.08 -$2,713.65 $1,691.08 IRR 22% 3% 22% Notes: Current Values column represents values of changing cells at time Scenario Summary Report was created. Changing cells for each scenario are highlighted in gray. Best $12,000.00 35% $5,961.73 38% (only look at incremental cash flows) Test Market Equipment Investment Installation Costs Life Book Value Salvage Value NWC Increase Additional Sales Increase in COGS Increase in Selling Exp. Depreciation per year Tax Rate Required Rate TARIQ NASSIR ALDOSSARY 0909480 $1,500.00 $7,000.00 $1,000.00 5 $0.00 $0.00 $1,500.00 $10,000.00 $4,000.00 $2,000.00 $1,400.00 34% 15% Salvage Value $0 $ 1,000 $ 2,000 15% Initial Outlay OCF Initial Investment Increase in NWC Installation Cost -$7,000.00 Sales -$1,500.00 COGS -$1,000.00 Selling Exp. Depreceiation EBIT Taxes @ 34% NI Depreceiation -$9,500.00 OCF Initial Outlay Time 0 1 2 3 4 5 CF -$9,500.00 $3,116.00 $3,116.00 $3,116.00 $3,116.00 $4,616.00 Required Rate 12% 13% 14% 15% 16% NPV $1,691.08 $2,583.62 $2,273.83 $1,976.53 $1,691.08 $1,416.87 Combo Box Two Cases for Compouding Case 1 Case 2 15% 16% Indicator Cell Option button and Group Box Three Cases for Market Value 1