General Data Forecasting

[andrewb90]

Hello All,

I have started trying to do 10 -12 year forecasting model for my business. I tried using some various equations but none of them have given me what I am looking for. Starting in 2013 through 2018 I will be opening 30 new stores. 5 one year and six the next. so 2013 - 5, 2014 -6, 2015 -5, 2016 -6, 2017 -5, 2018 -3. That would total all 30 stores.
The way that I have estimated it is by the number of months a store will be providing sales I am estimating 38 months for the 5 store years and 42 months for the 6 store years, as well as 30 months for the final opening year.
Every week I anticipate weekly sales to be 10k Gross and 1K Profit (based on 10% owner profit takehome). Averaged into monthly totals for my equation I have:
Monthly Gross Sales 43,333.33 Monthly Profit: 4,333.33
Now every year I anticipate a 7% Gross sales increase and a profit increase of 0.6%.
I have calculated all of that just fine, however my issue is that I want to cap out a store's Gross sales at 67,166.66 a month (15,500/wk) and a max profit of 15%.
I have included a sample image of the spreadsheet (hosted on photobucket) to give you and idea of what I have so far

Following the year 2018 (years 2019-2022)I will have no new stores add and only increasing sales and profitability to think about, but I want to be able to show the increase in sales from the most recently opened stores without having the very first store opened have sales that are beyond what could be predicted.

I hope everything makes sense so far. I hope somebody can be of some assistance because I am lost at this time.

Thanks in advance,

Andrew

 

[Snakehips]

Hi Andrew,

There has been a distinct lack of response on this so sadly, I could be your only hope!
How desperate is that?

Hopefully, I have understood what you are after correctly.
To be honest, I can't see a simple answer in terms of a formula or formulas that will apply directly year by year, to your posted data set and give you what you want.

The way that you are computing your yearly figures has all the 'Old' shops grouped together for any one year, which is fine until you want to start applying the Upper limit on Sales and Profit. As the years progress each successive launch of shops will have been established for a different number of years and if assumed targets and growth are achieved, they will be generating different figures. It is these separate launch group's figures that need to be tested against your limits.

I believe that I have the means for you to achieve what you want but it will involve the use of a couple of supplementary tables to compute the limited annual figures for each launch group for each year. Sadly, the formulas in these tables, though similar, are such that they cannot be dragged across and down the sheet. So just a bit of a ball ache to establish.
By my reckoning, with your quoted figures there will be 6 instances in the final 3 years where the 'grown' gross sales of any one launch group will exceed your limit and at no time will any profit exceed 15%. If that is the case then do you really need to adapt your spreadsheet to include the limits?
That said, you may wish to adapt to include limits in order to try other scenarios where the effect of the limits is perhaps less marginal?

I'm not sure that illustrating my solution here on the the forum will achieve a great deal for anyone else and it will be a bit heavy on forum real estate.

Hopefully my comments make some kind of sense?

If you wish to see the approach that I feel you need then PM me with an email address and I will gladly send you my file.