In response to increasing competition, a major paper distributor in the Washington metropolitan area with headquarters in Cheverly, Maryland wants to expand its presence along the east coast. The objective is to have one warehouse facility in a major metropolitan area in Virginia, North Carolina, South Carolina and Georgia. The company has two potential strategies for this growth. In the first strategy, the company will start by establishing a warehouse facility in Richmond, Virginia with plans to move into the Raleigh-Durham area of North Carolina and then move into South Carolina and Georgia in about two-three year intervals. The second is to move into Atlanta, Georgia area right away and then move up the east coast establishing facilities in South Carolina, then in North Carolina and finally in Virginia.
The cost of establishing a warehouse facility in Richmond would be $500,000. The other net cash inflows for the first year can be expected to be as high as $200,000 or as low as $100,000. In the second year net cash inflows could increase by either 25% or 10%. The third year's net cash inflows would increase by either 30% or 8%. The initial investment and annual cash flow projections for locations in North Carolina and South Carolina are considered to be consistent with those for the Richmond facility and are not expected to change much over the next two to three years. The cost of a warehouse in Atlanta, Georgia would be $750,000. If the company acted now, the initial year net cash flows are expected to be as high as $400,000 or as low as $150,000 with annual increases likely to be either 35% or 15% for the first three years. If the company waits two or more years, the initial year net cash flows in Atlanta would be only about $200,000 with annual increases of either 25% or 15%.
Take the time value of money into account by your analysis. The most common way is to calculate a Net Present Value.
Make estimates of cash flows for the best case and the worst case for both Richmond and Atlanta. for 10 years. Use your 10% discount rate and calculate the Net present Value of each of these 4 alternatives. Be careful because Excel has no spot for CF zero (the cash outflow for the investment).
This solution shows step-by-step calculations to determine the cost of establishing a warehouse facility in an Excel file. It also takes into account the time value of money, net present value and the best and worse case scenario.