Cash Flows & Project Viability in MS Excel – Should Walmart undertake the Project?

I took a test and got the answer wrong even on the second attempt. Please help!!!!!

“Walmart is considering opening a small experimental store in New York city. A store is expected to have a long economic life, but the valuation horizon is 10 years. The store in New York is likely to generate revenues of $33M in the first year and then it grows at 5%. but the costs of running the business is high because the margins on all the products sold are low (it is a volume business!) The cost of goods sold are $12M in year 1 and they are expected to grow at 4% per year thereafter. Selling and administration costs are likely to be $1M every year as it is a small store. The tax rate is 35%. Walmart is so good at managing its stores that working capital increases can be assumed to be negligible. But since New York city is an expensive place, Walmart will have to invest $200 million in purchasing a building (with land) even though it is a much smaller property than a usual Walmart store. The good news is that this outlay can be depreciated straight line over 10 years. Also, Walmart has estimated that the terminal value in year 10 dollars is $100 million. This value is the value of all cash flows in year 11 and beyond. Should Walmart undertake this project? Hint: Calculate the NPV or Net Present Value of the project if the discount rate is 5%.”

George

We solve George’s problem in MS Excel by first calculating the total Revenues, Cost of goods sold, Selling and Admin expenses and depreciation. Now we subtract COGS, Selling and Admin costs and depreciation from the revenues to get our profits after depreciation. Next we deduct taxes from our profits after depreciation to get our profits after depreciation and tax. Now we add back the depreciation to the calculated profits and get our true cash flows. We then calculate the present value of our cash flows for the 10 years. To calculate the NPV or Net Present Value we subtract the initial investment of 200 million dollars to the present value of the sum of cash flows and also add the present value of the ‘terminal’ amount. Since the NPV is a high positive value we can now say that Walmart can go ahead with the project.

Watch the training video below to learn about the MS-Excel solution: