Anonymous
8

# Sales Forecasting

• ### CONTRUCTION OF BICYCLE PATHS EXEMPT ?

Hi All,

I was just wondering if someone could help me out. I am looking at introducing cash flow statements and sales forecasting into the company I work for, but am struggling to work out future sales. Now I know a sales forecast is just a predicition and will never be 100% bang on, but I still want the numbers coming up to be realistic. I work in the construction industry so the business doesn't sell products, we sell a service, which varies from job to job. Now I was just wondering what would be the best way to produce this sales forecast? - I have the last 3 years sales data and this years current sales. I am looking at putting this all together on Excel and have tried using their forecast functions, but they are bringing up really low numbers, like half of what we have ever done, which doesn't make sense. Now I know that with the pandemic our sales were hit dramatically in April and May but June was a very decent month and this month looks to be the same. What calculations do I need to do to work this out? I think simplicity is key so I literally just need it to forecast sales for this year and bring up relatively realistic numbers.

Any help on this would be greatly appreciated, thanks!

### Replies (8)

By tom123
21st Jul 2020 21:17

Look at percentage of annual sales in each month over the year.

Multiply your annual budget by this amount.

Think about vat if you are forecasting cash.

For each month, assume x percent in 1 month, y in 2 etc. Same thinking for deposits.

Think about how opening debtors unwind at the start of the year.

Set up your spreadsheet to cover 36 months, so you can easily move projects outward

Thanks (1)
By tom123
21st Jul 2020 21:17

Look at percentage of annual sales in each month over the year.

Multiply your annual budget by this amount.

Think about vat if you are forecasting cash.

For each month, assume x percent in 1 month, y in 2 etc. Same thinking for deposits.

Think about how opening debtors unwind at the start of the year.

Set up your spreadsheet to cover 36 months, so you can easily move projects outward

Thanks (1)
By paul.benny
21st Jul 2020 22:54

Build up your sales from first principles: by project, by customer, by market type – whatever makes sense for your business without getting into microdetail. If you have those building blocks you can understand why your actuals vary from your forecast and so improve your forecasting.

Don’t try to forecast by extrapolation of totals. It doesn't deal with the messiness of reality and doesn't enable you to understand why your forecast was wrong.

Thanks (1)
By tom123
22nd Jul 2020 08:23

I agree, this is of course also useful to put into the mix.

If we ignore the virus (for now) there might be useful trends in historical data too.

Alternatively park the whole thing until January..

Thanks (1)
By Richard Grant
22nd Jul 2020 08:42

You can build in functions to project forward but concentrate on the margins and costs then ask the people booking the work/contracts for their best guess. If you have the costs and overheads buttoned down you won't be to far out.
Don't forget to allow for VAT as Tom123 says but possibly stock build up as well and how long it takes to get paid.

Thanks (1)
By Simpleton2
23rd Jul 2020 11:04

Agree with this, focus on building a good underlying model, then you can prepare multiple possible scenarios.

As much as possible make the underlying assumptions explicit and where feasible easily modifiable, so they can be challenged. This can be a really good time to focus on those costs that may have been allowed to stealthy creep upwards during fatter times, but aren't providing value for money.

With so much uncertainty at the moment flexibility and an in-depth understanding of the relationships are the key.

Thanks (1)
By ireallyshouldknowthisbut
22nd Jul 2020 09:18

So lets get this right, you are trying to forecasts future sales from past data for business based on projects, the margins for which vary from project to project, at the point of probably the biggest disruption to the economy for many years, and concerned the automated tool you are using cant get it right?

If you think that is a reasonable approach i would get you coat and walk out the door.

Start again,.

Go talk to your sales team. See what projects are on.
What margins they are asking for.
See what you might or might not win. What is on hold, and when those projects might be scheduled.
Scurry back to your s/sheets and type them in.
it will still be largely a waste of time, but at least you will have *some* idea instead of juggling past data about.

me? Id spend my time knowing what our break even points are so be on it in terms of when we need to make decisions about hiring/firing and reducing out costs base set against known future revenues + how much blue sky we need to squeak through this period. The past is not relevant.

Thanks (1)
By [email protected]
23rd Jul 2020 13:59

I partly agree with ireallyshouldknowthisbut

you need to button down the cost assumptions for your fixed and variable costs
the past "can" be usefull to indicate seasonality, and average margins etc. but I would use the current order book and the views of your senior management team to build out the future.
Paramaterise where you can and make detailed notes about the assumptions made in the model. And make sure you have a rolling cashflow going that will help to warn you about pinch points and cash shortages.
Once you have a happy working model you can use it as a business tool and update it each month with actuals and new forecast data. That way it becomes a powerful tool

Thanks (1)