Worksheet to Evaluate Whether to Buy a Franchise

This spreadsheet analysis was created at the bequest of Michael Webster and Mr. Blue MauMau in order to benefit members of this community. In particular Mr. Blue MauMau spent hours correcting my fumbling attempts at presenting this in html, so give him a round of applause. The purpose of this spreadsheet is to demonstrate how you can use industry data and franchisor specific information from a UFOC to do a screening filter of a franchise opportunity. I believe this level of analysis is appropriate after you have done a ‘quick” screen of an opportunity. Stated differently, I would not bother to do this type of check on a first vetting pass, but rather perform it after I had performed quick concept screen and a macro trend analysis.

The numbers used in the spreadsheet are from the NOLN survey and Exhibit J. You can find links to these in the post entitled “Financial Sniff Test”. Additional details were taken from the UFOC, such as the build out cost and the duration of the franchise agreement.

The expected return for this franchise offering is $34,427.51.

If you examine the data from NOLN you will not that the average configuration is for 3 bays. In examining the UFOC, note that the initial investment is given as a range between $136,068 and $241,033 for 2 bays. The UFOC recommends that you add an additional 20-30% for the additional bay or 25% on average. Therefore the inital investment should be between $170,085 - $301,291 (see footnote #2 below).

The duration of the franchise agreement is 15 years. Conveniently, since 1926 there has not been any 15 year holding period for the S&P 500 that exhibited a loss. Historically, the S&P 500 has returned 10.33% (1926-1993) per year. Over the 15 year holding period of the franchise agreement you would anticipate an equivalent investment in the S&P 500 to return $4.37 for every dollar invested with no expectation of loss (see footnote #3 below).

The expected return for the franchise opportunity is $34,427.51.

In summary you are "investing" a figure that realistically exceeds 300K for an anticpated profit of 34K a year less franchise obligations, less your salary, and taking a 40% chance of either a total loss (depending on your financing) or working 15 years for no profit on your inital investment.

Footnotes:

#1 These numbers were extracted from a sample where 85% or more of the units were non-franchise. Therefore you would need to correct for the royalty and advertising fees. Stated differently, in a franchise, your anticipated profit would be less. In addition, these numbers assume an owner operator.

#2 Further investigation would reveal these numbers are very optimistic.

#3 As a passively managed portfolio using asset allocation you would anticipate a significantly higher return with less "risk". For example a simple 60/40 allocation between the S&P 500 and the Shearson Lehman total bond market would be anticipated to return 13.21 % as tested over a 20 year period. A more sophisticated asset allocation could provide an expected return in the 17% range with even less risk.

See the full spreadsheet here to download.

Related reading:

• Financial Sniff Test to Quickly See If It's Worth Buying a Franchise