Question

Consider the following scenario:

About a little over a year ago I started a coffee shop business. I do have some data related to sales figures for the last 12 months. The business is promising. By the end of the first year I paid myself a monthly salary of $4,000 and the business also had an end-of-year $42,000 after tax profit of which I decided to write myself a check for half of the amount and the rest decided to keep in the business to grow it further.

What I have in mind is a second location for which I will need an initial investment of $75,000. It seems I will have to borrow upwards of $50,000 from a bank. When I apply to my current bank, they told me they want to see my business plan with a section dedicated to forecasting sales figures since my business have only a 12-month sales history.

Answer the following questions each in at least 1 paragraph:

How do you suggest I should come up with these forecasted values?  

How far into the future should I go in my forecast?  

How can I convince the bank that my numbers are reliable?  

How can I identify patterns (trendlines, periodic ups and downs) in the dataset? Do they matter at all?

Here are some numbers if you’d like to play with if you want to:

Month	Sales	Before Tax Monthly Profit
Jun 2017	$22,000	$4,000
Jul 2017	$22,500	$4,500
Aug 2017	$23,000	$5,000
Sep 2017	$23,000	$6,000
Oct 2017	$20,000	$5,000
Nov 2017	$21,000	$4,500
Dec 2017	$19,000	$4,000
Jan 2018	$19,500	$3,500
Feb 2018	$18,000	$3,000
Mar 2018	$19,000	$3,500
Apr 2018	$21,000	$4,000
May 2018	$22,500	$5,000

Hint: Think of yourself as the owner of that coffee shop. That will help.

Answer 1

Greetings,

To forecast sales for future, one needs to always analyse the past as well. From the numbers furnished by you, it seems that there are Lot of fluctuations in the monthly sales figures which implies that business is in the nascent stage, hence prediction becomes very subjective. One may simply calculate CAGR of sales and apply it to end month's sales to predict future sales.

CAGR = (22500/22000)^1/11 = 0.1875%

So Next Year first month sales can be taken as = total sales of May, 2018* (1+CAGR)

Instead of this, one may simply take average sales of last year on monthly basis *(1+CAGR) to be next year's one month sales and grow it at a rate of CAGR for every month.

Regarding the time period of forecast, I would say it depends upon the requires of the bank and usually banks demand financials for 2-5 years. Since business is recently started and is in the nascent stage, hence it is always difficult to predict sales. So shorter the period, greater the confidence of the bank on reliability of your projections.

Usually banks get more confidence over financials if forecasting is made with two to three approaches like past data based or future data based on simulations. Further the source of various data should be cited in the report to enable the banks to rely upon the inputs used. Assumptions or Caveats should be clearly stated in the report, so that bank can judge objectivity of the judgements made.

Technical Analysis of data include studying graphs like for ups and downs or the d in sales. Banks keep an eye on the same. Banks have internal policy guidelines based upon which they judge whether to sanction credit oo not. If yes, then how much to sanction. For instance, a dip in sales on YOY basis > 25% may not be acceptable at all. If sales dip in a particular month say by 40%, then banks would be more cautious and think that business is more volatile. Specifically in a business of coffee shop, primarily coffee seeds are subjected to weather problems , hence due to bad harvest, your coffee sales may get hampered, hence the use of technical analysis becomes of paramount importance.