Excel spreadsheet for Tom which will help him decide whether or not he should open the new pizza shop.

Tom is considering expanding his growing pizza shop empire. Having spent $12,500 to hire a group of students to locate a new location, and another $6,000 to assess demand, Tom is now faced with the decision of whether he should develop a second location to open up an additional pizza shop.

Besides the assessment costs previously mentioned, the other upfront costs to open the new pizza shop would be $275,000 for a new building, which his student consultants found in a highly desirable location next to a rebuilt student ale house and near a well-known (traveling) hot dog establishment. Tom figures that the proposed building will require $75,000 of upfront repairs, which he will pay for immediately, and which will be completed in one years time. Tom will also need to purchase a new pizza oven, chairs, and cutlery etc. for the new restaurant. These capital costs are estimated to be $22,000, and will be paid in 1 years time from now. Assume for simplicity that the building and the repairs are Class 3 for CCA purposes (CCA rate of 5%), and that the pizza oven et. al. are Class 8 (CCA rate of 20%).

If things go to plan, Tom's pizza shop will be ready to open by the end of the year. Since he hates to be short on pepperoni, and his competitive advantage is that he puts more mozzarella cheese on his pizza than any other pizza shop, Tom figures he will need to have $6,500 of food inventory in stock on opening day, which will need to be paid for at that time.

Tom sells two sizes of pizza, the Large, which retails for $17.00 (and costs $4.50 each to make), and the small, which retails for $6.75 (and costs $0.43 to make).

Sales for the Large in the first year of operation are expected to be 3,500 units, while the Small will have anticipated initial sales of 800 units. Fixed costs are expected to $45,000 in the first year of operations and then grow at the rate of inflation. Miscellaneous costs for the year that the new shop is under construction are expected to be $4,000.

Tom also sells beverages which retail for $3.00 per serving and which have a cost to Tom of $0.85. Experience has shown that Tom will sell 3.4 beverages on average for each Large sold and 1.2 beverages for each Small. Maintenance costs are expected to be 2% of gross sales.

Assume for simplicity that the cash flows from sales and operations occur at the end of each year of operation. This means that the first cash flows from sales will occur in two years.

Pizza unit sales are expected to grow 5% per year for the first two years of operations, after which sales growth will flatten out to be 2% per year. (Beverage sales units remain a constant ratio of pizza unit sales). Tom is expecting that he will lose sales of 350 Large and 30 smalls at his current pizza shop as some of his existing customers will choose to eat at his new shop.

Days receivable are expected to be 20 days of sales, while days A/P are expected to be 60 days of total costs (fixed costs and variable costs). Days inventory will be 50 days of sales (which is too high for efficient operations, but Tom is known to sometimes sneak a snack from his own inventory). Cash on hand for the opening day of the new shop will be $1,000 and will remain so throughout the first year of operations and then is expected to grow at the same rate of growth as overall sales.

Inflation is expected to be 1.5% per year for the first 10 years of operations and 2% thereafter, although Tom will increase his pizza and beverage prices 9% per year for the first 5 years of sales and 3% thereafter to keep them in line with tuition cost increases.

After ten years of operations, it is expected that Tom will need to replace his pizza ovens, chairs and cutlery etc. The replacement costs for these items are expected to grow in line with inflation. The old items will have to be scrapped at no value. (Ignore Terminal Loss or Recapture effects on taxes.) These items will be bought at the beginning of year 12 (and thus subject to 11 years of inflation from the original price).

Tom's holding company has a bank loan of $5MM at prime plus 225 bps (assume current prime is 3%). There are 10,000 shares of stock of Toms company outstanding. Although they are closely held, a few shares have recently traded at a price of approximately $800 per share. Book value of the shares is $2MM. The current risk-free rate is 1.5%, and the estimated beta for Toms stock is 1.25 . Tom's effective tax rate is a relatively high 42%.

Having just examined his retirement savings, Tom expects to be able to retire in 23 years. Tom figures at that time the building and equipment will be worth $950,000, and the rights to his secret sauce and trademarks will be worth $2 MM.