Sales Forecasting Methods with Spreadsheets in Excel

by ryan on December 20, 2016




Sponsored

Sales Forecasting Methods with Spreadsheets in Excel

 

From my experience, the easiest way to prepare a financial budget and forecast is to start with sales forecasting.

There are various sales forecasting methods used by different companies to achieve their objectives. All sales forecasting methods aim to produce one objective. That is to produce an accurate sales forecast.

In my previous jobs, I do not use any of the sales forecasting softwares. I used microsoft excel to do the sales forecasting.

excel-budget-template-sheet

Preparing sales forecasting is most probably the most interesting of the budgeting and forecasting process. Everything in a company is dependent on the sales. If a company does not has sales, there is no need to do the rest of the budgeting and forecasting as most likely the company will not be able to sustained.

Still using the earlier example, I now breakdown the forecasted sales numbers into more details like by products.

Assuming you have four sales products. Product 1, 2, 3 and 4 made up 50%, 30%, 15% and 5% of the total sales of $1,200,000. The yearly increment remained at 20%.

When you perform sales budgeting and forecasting, it is logical to also prepare the cost of sales or sometimes refer as purchases budgeting and forecasting at the same time. Assuming that your cost of sales is 50% of the sales for all the products, your gross profit margin would be 50% respectively.

Create a new sheet in your excel spreadsheet and plot in the following formulae.

Building Sales budgeting and forecasting spreadsheet

1. Breakdown of sales $1,200,000 by product 1, 2, 3 and 4 with 50%, 30%, 15% and 5% respectively.

Workings :

Product 1 = Total Sales x 50% for Year 1 to Year 5

Product 2 = Total Sales x 30% for Year 1 to Year 5

Product 3 = Total Sales x 15% for Year 1 to Year 5

Product 4 = Total Sales x 5% fro Year 1 to Year 5

2. Cost of sales is 50% of Sales for all products.

Workings :

Cost of Sales Product 1 = Product 1 Sales x 50%

Cost of Sales Product 2 = Product 1 Sales x 50%

Cost of Sales Product 3 = Product 1 Sales x 50%

Cost of Sales Product 4 = Product 1 Sales x 50%

Upon completion, you will get a spreadsheet that look like this:

Upon completion, remember to counter check the total sales, total cost of sales and gross profit to ensure they tally with the income statement.

If you are lost in between the above steps, you can download the below spreadsheet for your easy reference.

Sample Templates

excel-budget-template-sheet

excel-budget-templates

 




Sponsored

Related Post

Sponsored

Previous post:

Next post: