Your support helps keep our site running! We might earn a small referral fee when you purchase from links in this post, at no extra cost to you, which we REALLY appreciate. All opinions remain our own as always.
The Microsoft Excel Trick Every Small Business Should Know
Forecasting your business needs has never been so easy! With Microsoft Excel 2016 (or newer) you can understand your business like never before!
What a click-bait-y title, right?! *GASP* But it constantly amazes me how many small business owners haven’t heard of or used forecasting before. Forecasting is such a powerful way to get leverage in your business! This article will explain what you can do with forecasting, and how to do it.
In the world of business, it is no mystery that you need to know what the future holds for sales. There have been many studies, and various techniques have been created to help businesses with this problem.
This is called forecasting- or the ability to predict the future with minimal error.
For small business owners, forecasting gives us the ability to look at our past data (sales, supply purchases, etc) and make an educated prediction of what the business will do or need in the future. This helps us more accurately prepare for inventory and other business needs!
There are many forecasting techniques that the massive retail behemoths like Walmart and Amazon use in their business on a daily basis.
But did you know forecasting can be quickly done with a few clicks in Excel? This is one of my favorite features of Excel- that might often be overlooked.
This feature is called the “Forecast Sheet” in the “data” ribbon on the menu. Only available in Excel 2016 or newer.
The Forecasting Sheet feature in Excel is easy for anyone to use!
This feature does most of the mathematical processes (for the forecasting model building process), making it an easy option for most small businesses to use.
You just click a few buttons, and voila! You’re done.
This feature is even great for those who have some statistical background. It provides a 95% confidence interval. (If you don’t know what this means, do not worry, I will explain this further in the post as a cool business management tip!)
What type of data do I need to use Forecasting for MY business?
The forecasting feature works with time-series data. This is data that is gathered with a time data point.
This could be a report of your sales every month (or daily sales). It could also be a report of the supplies you have purchased over the past quarter.
These are both examples of ‘time series’ data, because it has the element of time (months, days, years, etc).
The forecast sheet needs at least three periods or more to make a prediction. If you do not have enough past data, you will get a warning that there is not enough data.
For example, if you want to figure out how many sales you are likely to have next month, you will want to have the sales reports for the past three months to use this feature more accurately.
How can forecasting help me with my business?
This feature can help you do TWO fundamental analyses for your business:
- Predict your future sales.
- Help with your inventory to prevent stock-outs and overstocking.
(There are more uses for forecasting, but for most small businesses, this is an excellent place to start.)
Great Scott! We did it!
Forecasting is meant to help business owners make smart decisions about the future by using their past data.
This is called data-driven decision making, so once you use the forecasting tools, you will be applying data and using data to grow your business.
Congratulations, you have now entered the data-driven realm of business practices!
Welcome to the future, running your business just got a little bit easier and a whole lot smarter (like, computer smart).
So HOW do I use the forecasting sheet?
I’m going to walk you through this entire process in the video below. But here’s a quick summary for you also. *wink wink*
First you are going to need your data, in a spreadsheet form. So download three months of your sales data (for example) and open it up into an Excel sheet. You can delete any extra information, you really only need to know the dates and the numbers (units sold, for example).
Now push the magic ‘Forecast Sheet’ button.
When you push the button, Excel makes this amazing chart that shows your data, with the far right side looking something like a fork-type of display.
This fork is the Forecast Zone, with a 95% confidence metric.
What this means is that you can be 95% sure that your next forecast will be in between these areas inside of the fork.
Now look at that dark middle line in the center of the fork: this is the average spot where the estimates are most likely to fall.
Keep in mind this data is showing your forecast while ASSUMING everything else will remain the same as it has been during the period of data that you used. It doesn’t predict unusual circumstances.
For example, if you plan on closing your shop for a month, the graph will not include that in the prediction. Excel has no idea you are way overdue for a vacation. Another example might be a spike of holiday sales. If you use October, November and December sales to predict January sales, you are probably not going to get an accurate prediciton. Excel has not discovered the chaos of Black Friday.
You need to be aware of these types of outside factors when choosing your data and using your forecasts.
If you are forecasting for future months but don’t have three months of past data, try breaking that data down into days or weeks.
This will help you predict the future period in the same timeframe.
So if you have 70 days of sales, you can predict 70 days into the future.
You can use forecasting to create a sales bonus or performance reward for your team.
Just use the top line of the forecast as their goal to earn their reward for the period.
The top line is the highest point of your forecast within that 95% confidence level.
In the rare case that your future results end up being way outside of the forecasted results, this might be something you should research.
You may have made a change in your operations that had a considerable impact on your prediction.
Understanding what you did differently will help you grow and prevent disasters!
Okay, now that we have a basic understanding of what forecasting does and why every small business owner should be using it, let’s dig into HOW to use the Forecasting Sheet feature.
Video Walkthrough For Using The Forecasting Sheet in Microsoft Excel:
The forecast sheet can help provide powerful information for small businesses to grow. Even if you hate math, you really just need to be able to download your business data (sales, supplies, whatever) and paste it into an Excel sheet to use this amazing feature.
We live in a tech enriched era, where ANY business owner can make data-driven decisions. Forecasting is a great feature that Excel offers so that you can do those time-consuming computations with only a few button clicks, even if you aren’t a huge company like Walmart.
Using the Forecast Sheet can help you control your inventory and prevent overstock or running out of stock, and improve your cash flow. It also enables you to make reasonable predictions on your future sales to create SMART goals for your business.
Welcome to the future of your best business ever.