Need some assistance with this tricky excel case! Here is an updated info screenshot!

Mini-case study 3 Capital budgeting- using incremental cash flow Suppose you recently got an offer from Cisco. You work as a financial manager of the router division of Cisco Systems. On your first day, your boss talks about a project that Cisco is considering now: the development of a wireless home networking appliance, called HomeNet. He asks you to value this project, and then submit a valuation report to indicate your suggestions. You learned capital budgeting from the finance class, so you know you should work with the forecasted Incremental Free Cash Flows, then recommend to take the project if you calculate a positive NPV based on your estimated cash flows. Forecasting these cash flows is challenging. You need to rely on different experts within the firm to obtain estimates. So you communicate with the marketing department, operations manager, and the firm's engineers to obtain those information. Finally, you are able to list all information as following: 1. Based on extensive marketing surveys, the sales forecast for HomeNet is 50,000 units per year. Given the pace of technological change, Cisco expects the product will have a four-year life and an expected wholesale price of $260 (the price Cisco will receive from stores). Actual production will be outsourced at a cost (including packaging) of $110 per unit. 2. To verify the compatibility of new consumer Internet-ready appliances, as they become available, with the HomeNet system, Cisco must also establish a new lab for testing purposes. It will rent the lab space, but will need to purchase $7.5 million of new equipment. The equipment will be depreciated using the straight-line method over a four-year life. Cisco's marginal tax rate is 21%. 3. The lab will be operational at the end of one year. At that time, HomeNet will be ready to ship. Cisco expects to spend $2.8 million per year on rental costs for the lab space, as well as marketing and administrative support for this product. Forecast the incremental earnings from the HomeNet project. 4. Suppose that HomeNet will have no incremental cash or inventory requirements (products will be shipped directly from the contract manufacturer to customers). However, receivables related to HomeNet are expected to account for 15% of annual sales, and payables are expected to be 15% of the annual cost of goods sold (COGS). Fifteen percent of $13 million in sales is $1.95 million and 15% of $5.5 million in COGS is $825,000. What are HomeNet's net working capital requirements? How does this requirement affect the project's free cash flow? 5. You also seek help from experienced colleagues to obtain market data, then you estimate that the cost of capital is 12%. You will use it as the discount rate in your valuation process. With all inputs information available, you are ready to start your work! Prepare a pro forma financial statement, estimate the project free cash flow, and calculate the NPV, then make your recommendations on whether Cisco should proceed with the project. Case study 3. Capital budgeting- using incremental cash flow 1. Based on extensive marketing surveys, the sales forecast for HomeNet is 50,000 units per year. Given the pace of technological change, Cisco expects the product will have a four-year life and an expected wholesale price of $260 (the price Cisco will receive from stores). Actual production will be outsourced at a cost (including packaging) of $110 per unit. 2. To verify the compatibility of new consumer Internet-ready appliances, as they become available, with the HomeNet system, Cisco must also establish a new lab for testing purposes. It will rent the lab space, but will need to purchase $7.5 million of new equipment. The equipment will be depreciated using the straight-line method over a four-year life. Cisco's marginal tax rate is 21%. 3. The lab will be operational at the end of one year. At that time, HomeNet will be ready to ship. Cisco expects to spend $2.8 million per year on rental costs for the lab space, as well as marketing and support for this product. Forecast the incremental earnings from the HomeNet project. 4. Suppose that HomeNet will have no incremental cash or inventory requirements (products will be shipped directly from the contract manufacturer to customers). However, receivables related to HomeNet are expected to account for 15% of annual sales, and payables are expected to be 15% of the annual cost of goods sold (COGS). Fifteen percent of $13 million in sales is $1.95 million and 15% of $5.5 million in COGS is $825,000. What are HomeNet's net working capital requirements? How does this requirement affect the project's free cash flow? 5. You also seek help from experienced colleagues to obtain market data, then you estimate that the cost of capital is 12%. You will use it as the discount rate in your valuation process. With all inputs information available, you are ready to start your work! Show your work below, and make your recommendations on whether Cisco should proceed with the project. Inputs: Incremental revenues are: Incremental costs are: Additional units sold price Additional units sold production costs Selling, general, and administrative Depreciable basis/Depreciable life Depreciation is: Marginal tax rate is: T T T Outputs: Net working capitale Year Net working capital forecasts Cash Requirements Inventory Receivables (15% of Sales) Payables (15% of COGS) Net Working Capital Change in NWC Cash Flow Effect 0 1 L2 L 3 Incremental Free Cash Flows Year Revenues Cost of Goods Sold (COGS) Selling. General and Administrative L Depreciation T T Income Tax at 21% Incremental Earnings Add back Depreciation Purchase of Equipment Changes in NWC Incremental Free Cash Flows Use NPV function to calculate the Net Present Value: NPV=