Simple Product Revenue Forecast Spreadsheet

Posted: September 17, 2019 Comments

If you’re selling a product it’s important to be mindful of your financial situation. Whether that be to handle payroll, marketing costs, or building up an emergency fund, having a pulse on cash flow will help you to make more informed (read: better) decisions over time.

There are a ton of tools out there, some of which are quite literally beyond my comprehension as I regret not paying more attention in my accounting classes. 😬

In an effort to get a more hands-on understanding of what I have going on I began a simple collection of monthly revenue year over year, just so I could have a cursory glance at how things were going on an extremely high level. This worked very well, I could begin to notice seasonal trends for sales, and begin to have better expectations month to month.

I’ve collected monthly revenue data in a spreadsheet for the past few years and began thinking about what it would look like to start using that historical data to make an educated guess about revenue for this month. Again, there are tools out there for this, but I wanted to take a closer look for myself to see the actual calculation.

As it turns out, the tool I was looking for was right under my nose; spreadsheets have a FORECAST formula! 👍

This may sound obvious to anyone who has taken an introductory Excel class, but personally I rarely go to spreadsheets to do much of anything (to a fault it appears) so I was glad to find a feature within the tool I was already using to collect my data.

The FORECAST formula isn’t complex as it does not take into account your expenses or any outside influence. I’m not after those details though, I want a straight-to-the-point linear projection of revenue for this month based on revenue from this month in past years. FORECAST is well suited for that job.

Forecast your revenue

After putting this together I thought you might find it useful as well, so I’ve populated an example spreadsheet with dummy data:

https://d.pr/vTMvsy (Google Sheets link)

This spreadsheet is a recreation of what I use, with revenue broken down by month year over year. There’s a column for a monthly forecast for this year, and next to that a column that calculates the variance between the forecast and actual performance.

To reiterate: I’ve neglected most of what I’ve learned in my accounting classes (and I don’t fully grok the inner workings of the FORECAST function (yet?)) but this appears to be the information I was looking for. This information is by no means made available to utilize in making business decisions, simply an exercise in better understanding your revenue trends.

Using the value in the forecast column you’ll be able to see an estimate for revenue this month, and once this month is over you can check out the variance to see whether you exceeded the projected trend.

I imagine there is a spreadsheet veteran that would be able to critique this implementation or perhaps even integrate the variance into the FORECAST itself to obtain more accuracy, but that’s all I’ve got for now!

Get my newsletter

Receive periodic updates right in the mail!
  • This field is for validation purposes and should be left unchanged.

Leave a Reply

Your email address will not be published.