Costco is going to open a urben Costco, in thinking about the potential size of
ID: 2710276 • Letter: C
Question
Costco is going to open a urben Costco, in thinking about the potential size of the market, Costco believes that critical factors are the size of the population, portion of the population that will visit the store and how much they will spend. The current population of Seattle is 700,000 and it is expected to grow at a rate of 1.5% / year. During the first year the UC is open, Costco forecasts that 4% of the population will visit the store and that will increase by 1% / year (i.e.: in the 3rd year the store is open 6% of the population will visit). Each visitor will spend an average of $1,500 / year and that is estimated to increase by 2.5% / year. To help manage risk, the company plans to lease the store space and the planned duration of the lease is 5 years which will be the end of the evaluation period for sales of the pilot UC.
The variable cost of goods sold is forecast to be $1,300 per visitor during the first year the UC is open and is forecast to increase by 2% / year based on inflation estimates. Selling, General and Administrative expenses are forecast to be 9% of revenue each year.
Costco will need slightly more than one year from now (November 2015) to decide on a location and get the site ready. Prior to opening the UC, Costco will need to spend an additional $5 million on UC design and marketing and $10M on equipment (shelving, carts, check-out registers) in support of store. The equipment purchase and marketing promotions will be done approximately six months prior to the start of sales. The equipment will be depreciated using the five year MACRS schedule. The equipment will have a $3 million salvage value at the end of the pilot period and might be able to be used in a future company project. Also, the company will need to invest $2.5 million in inventory prior to the opening of
the store. Once sales begin the company will plan to maintain 5 days of accounts receivable, 30 days of inventory and 25 days of accounts payable.
The corporate tax rate is 30%.
Your work is to make a financial statement, include income statement, balance sheet, payback period, NPV, IRR, MIRR and profitability index. Then estimating the WACC for Costco.
Explanation / Answer
Sales Variable Cost Total Selling Depreciation NetIncome Income after Operating Cash flow Year No of Customer Per customer Total Sales per customer Variable cost Expenses Tax @30% (add back Depreciation) 1 28000 1500.00 42000000 1300 36400000 3780000 2000000 -180000 -126000 1874000 2 35000 1537.50 53812500 1326.00 46410000 4843125 3200000 -640625 -448438 2751562 3 42000 1575.94 66189375 1352.52 56805840 5957043.75 1920000 1506491 1054544 2974544 4 49000 1615.34 79151460.94 1379.57 67598949.6 7123631.484 1152000 3276880 2293816 3445816 5 56000 1655.72 92720282.81 1407.16 78801061.25 8344825.453 1152000 4422396 3095677 4247677 Total 15293600 Working Solvage Present Value at Year Cashflows Capital Value Cashflows 1% 1.23% 9% 0 $ (15,000,000.00) -2500000 $ (17,500,000.00) -17500000.00 -17500000.00 -17500000.00 1 $ 1,874,000.00 $ 1,874,000.00 1855445.54 1851139.90 1719266.06 2 $ 2,751,562.50 $ 2,751,562.50 2697345.85 2684841.76 2315935.11 3 $ 2,974,543.87 $ 2,974,543.87 2887062.98 2867010.92 2296893.64 4 $ 3,445,815.90 $ 3,445,815.90 3311361.35 3280731.54 2441102.85 5 $ 4,247,677.28 3000000 $ 7,247,677.28 6895916.24 6816275.37 4710492.94 Total 147131.97 -0.51 -4016309.40 Year Cashflows Payback amount Payback Year 0 -17500000.00 1 1874000.00 1874000.00 1 2 2751562.50 2751562.50 1 3 2974543.87 2974543.87 1 4 3445815.90 3445815.90 1 5 7247677.28 6454077.73 0.890502913 Total 17500000.00 4.89 NPV of the project $ (4,016,309.40) IRR of the project 1.23% Profitability Index (Net Cashflow/Investment $ 1.02 Payback period 4.89 years