Question

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.
Need some assistance with this tricky excel case! Here is an updated info screenshot!

Answer 1

Below calculation details the Incremental Earnings (Profit after tax):

Description Amount Comments Cost of Goods Sold -5500000 Production cost * forecasted sales Sales 13000000 wholesale price * forecasted sales Depreciation -1875000 cost of equipment/4 Rental for lab | -2800000 Input Data EBIT 2825000 Sum of above items Tax @ 21% 593250 EBIT*21% Incremental Earnings 2231750 EBIT - Tax Depreciation 1875000 Add back Depreciation Net Working Capital Requireme - 1125000 as per calculation Incremental Free Cash Flow 2981750 For year 1 to 4 (for year o refer NPV table

Net Working Capital = Current Asset - Current Liabilities (In this case, Receivables - Payables) = 1125000.

Below calculation of Net Present Value: