Sales 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

 

[PaddyD]

Hi.

"I have started trying to do 10 -12 year forecasting model..."

You should stop, or at the very least not base any capital expenditure on the results - the uncertainties that far out or too large to make any estimates meaningful.

Details notwithstanding, can I ask why you're running this model - what are you trying to achieve?

 

[andrewb90]

I am just trying to get an idea of what sales would amount to based on similar figures from existing locations. We are just trying to figure out what growth could be. I am using figures from active stores to start the data. The reason for the cap on the figures is that realistically the numbers don't go that high so any sort of prediction that exceeded that would be useless.

 

[andrewb90]

BUMP^^^

Thanks!

 

[PaddyD]

HI. Sorry - not been around much lately, and no-one else seems to have picked this up. are you still in need of help?

 

[andrewb90]

I think I have finally figured it out. I just ended up deleting everything and starting over. I had originally just used one of Microsoft's templetes, I realize now that was my downfall, I should have created my own from the beginning.

 

[PaddyD]

Glad you managed to get it sorted, and sympathise re microsoft-inspired blind alleys !