Trend Analysis In Sales Forecasting

Plato once said “a good decision is based on knowledge and not on numbers.” 

That stood for approximately 2,400 years, but I do not believe that is the case anymore. We can use numbers coupled with knowledge to guide our actions now.

A large part of my last position as an accountant for a large food service company was sales forecasting and creating operating budgets. If you are creating a sales forecast, trend analysis is probably the easiest method to figure sales and expenses if you have been operating for a while.

Using a restaurant as the example, if you had 100,000 in revenues last May, that would be a good starting point to base this year’s May revenues if nothing else has changed. Most companies take that figure and add a stretch goal, such as 10%. You can also use last years expenses and subtract any unusual or one-off expenses to budget your costs. I typically budgeted costs using sales and multiplying by defined labor targets, food cost and other direct expenses. So if food cost is 28%, labor 27.5% and supplies 10%, I would expect to net $34,500 based on sales of $100,000.

Besides year-over-year budgeting, trend analysis can be used to tell you where you are going.  You can use Excel to build a trailing 12 month sales chart.

Step 1 is to create a date column:

Step 2 is to add a monthly sales column:

Step 3 is to create a column that adds sales year-to-date. You will need to create a formula to add the previous months sales to the current month. In the picture below, I showed the simple formula I used:

The final step, Step 4 will be to chart your sales. The chart below was created using the following order of steps: Highlight the 3 columns -> Select Insert at the toolbar -> From the selections go to the Line Graphs -> Choose Line with markers. Your chart will look like this:

This chart will give you your YTD sales and monthly sales. Examine months where sales are not uniform, such as what caused a great month or a sales miss. Use that knowledge to budget and improve on the future. Then you can prove Plato wrong!

No matter what method you use to forecast sales and expenses, it is important to do so in order to effectively guide you in your daily decisions.

If you need any help creating sales forecasts or financial planning and analysis, join my email list.

This entry was posted in Entrepreneurship, Sales and tagged . Bookmark the permalink.