Hypothetical Shopko Stores capital budgeting decision

Payback, Net present value, Cash Flow spreadsheet, and internal rate of return methods Shopko Stores, Inc. is struggling to find its niche in today's competitive retail merchandising market. Head to head competition with Wal-Mart, Target, Kohl's and Kmart in shared markets have led to a decline in profit margin in these markets. The negative trend to Shopko's bottom line is discerning to management. Shopko's top management team is currently preparing their long-term strategic plan. They wish to explore three mutually exclusive options related to future new store expansion strategies:
1. Expand new stores in cities with populations greater than 25,000 - Shopko has historically built in cities with population areas greater than 25,000. This strategy has served them well over the years. There are currently 134 stores located in these market areas that account for $2 billion in annual sales. The store foot print is 100,000 square feet. Typical store cost per square foot is $255. This includes land, building and equipment to outfit the store. Stores in these markets are still profitable. The pharmacy and optical departments within these larger stores have done well. With competition from Wal-Mart, Target and Kohl's in cities of this size, it is getting more difficult to find profitable new store locations that exceed Shopko's cost of capital.

2. Expand new stores in cities with a population between 10,000 and 25,000 - Shopko had this market primarily to themselves not to long ago. Over the years, competition from Wal-Mart has cut into profits and opportunities to expand into these cities. However, to compensate, Shopko has developed a reduced square footage format for the cities with populations between 10,000 and 25,000 in hopes of reducing expenses, yet maintaining a strong revenue base. The new store footprint is 77,500 square feet at a cost of $255 per square foot all in. Management is excited by the prospects of increasing profit margin with the reduced foot print, yet still offering pharmacy and optical services.
3. Expand new stores in cities with a population less than 10,000 - This last strategy is new to Shopko and top management is very curious how the numbers will play out. Most big box stores have avoided expanding in these smaller cities and towns due to concerns about low revenue to cover the large store foot print expenses. Competition in these markets is mainly Family Dollar and Dollar General. Shopko is interested in building a 35,000 square foot print that will still include pharmacy service but no optical department. Cost per square foot will be $255. These stores will reduce the amount of inventory on hand by limiting the selection. Marketing has indicated that there are 23 states with over 500 communities that fit this description within Shopko's current distribution system. With reduced expenses, management is hopeful that these underserved locations will produce positive returns.
The Shopko CEO wants to know what the new store expansion long-term strategic plan should be. He has asked you to analyze the three new store expansion alternatives identified above. Shopko requires an Internal Rate of Return of 16% to be accepted. Marketing has adjusted the sales revenue projections for each of the three alternatives to account for risk. Shopko's tax rate is 30%. The initial investment is Store sq. foot times cost per sq. foot.
The Earnings Before Depreciation and Taxes (EBDT) are projected as follows:
Pop. 10,000
Pop. > 25,000 to 25,000 Pop. < 10,000
Year EBDT EBDT EBDT
1 $ 9,600,000 $ 7,300,000 $ 3,000,000
2 10,000,000 7,600,000 3,400,000
3 10,500,000 8,000,000 3,900,000
4 11,000,000 8,500,000 4,000,000
5 11,500,000 9,000,000 4,800,000

Depreciation Expenses are projected as follows:
Pop. > 25,000 10,000 - 25,000 Pop. < 10,000
Year Deprec. Deprec. Deprec.
1 $2,000,000 $ 1,950,000 $ 750,000
2 2,300,000 2,250,000 1,000,000
3 2,300,000 2,300,000 1,000,000
4 1,900,000 1,850,000 900,000
5 1,900,000 1,850,000 900,000

A. Calculate and present the Cash flow spreadsheet, Net Present Value, Internal Rate of Return and payback method calculations for evaluating each investment. Each investment scenario should have it's own Powerpoint slide.
B. Summarize each:
Pop. > 25,000 Pop. 10,000 - 25,000 Pop. < 10,000
Payback Period:
Net Present Value:
Internal Rate of Return
C. Give your recommendation, noting potential opportunities and risks. Be clear on your recommendation with written supporting analysis.

Newest update about the analysis:
I just met with the marketing vice-president on the sales trend for alternative 3 - cities with pops less than 10,000. Her original estimate for year 4 was only $4,000,000, a $100,000 increase from year 3. This is unacceptable. Sales should be going up year over year $300,000 - $500,000, not $100,000! I would have expected one of my new analysts to catch that when looking at the sales trends.
The marketing VP and I have agreed to change Year 4 revenue for alternative 3 to $4,300,000. Please update your analysis with this information.
Shopko CEO