How to Prepare a Sales Budget with Example in Excel

Tired of keeping the sales record? Looking for a way to create sales budget in Excel for your company? Hang on! You have come to the right place. In this article, I will show you sales budget example in Excel. At the end of reading the article, you will find it easy to create a sales budget format.

What Is Sales Budget?

Sales Budget is a financial statement that describes the total revenue of a company or organization over a certain period of time. In the sales budget statement, there are two fundamental factors.

Units of products sold
Price of the products

This is an important accounting tool that helps to evaluate the overall performance of a business organization and evaluate the revenue made from a specific product over a certain period of time. This format is also considered a planning tool to set a specific standard for the business. Evaluating the budget also helps to maximize the revenue from sales and achieve a goal.

Stepwise Demonstration of Preparing a Sales Budget in Excel

Let’s say, we want to create a sales budget format for XYZ Company Limited over a certain period of time. In this section, you will find some easy and useful steps to create sales budget example in Excel for this company. I will demonstrate them one by one here. Let’s check them now!

✒️ Step 1: Assigning Factors of Sales Budget

First of all, decide a time limit over which you want to create the sales budget format. I have shown the procedure for half of a year for simplification. Our Excel file contains the budget format for the whole year.
Now, assign the Sales Budget factors to your budget sheet. The factors are:

Unit Sales
Unit Sales Price
Total Sales Revenue

Then, put the number of products your company has sold in the respective months (i.e. from January to June).

After that, set the price of the unit sold products. It may be the same or vary from time to time according to your company policy. I have assumed the price of the unit products same over the considered time period. Apply it according to your sales.

✒️ Step 2: Calculate Sales Revenue

Now, you have to calculate the total sales revenue, Proceed as below.

Multiplying unit sales with the per-unit price results in the total sales revenue. So, type the following formula in a selected cell to calculate the total sales revenue.

=C5*C6

Here,

C5 = Unit Sales for the month of January
C6 = Unit Sales Price for the month of January

Now, press ENTER and the cell will give you the total sales revenue for the month of January.
After that, drag the Fill Handle tool to the right-end corner to Autofill the formula for every month you want to get the total sales revenue.

As a result, you will get the expected result for the applied time period.

✒️ Step 3: Input Cash and Credit Sales Percentages

Cash Sales refers to the amount of cash received when a sale is made. On the other hand, Credit Sales means the amount that will be paid after a certain period of time provided to the customer. While running a business, you may have to make both types of sales. In our sales budget format, we will now input these two types of sales.

Here, in our Excel template, I have assumed the Cash Sales as 40% of the total sales and the Credit Sales as 60% of the total sales over the whole time period. It may vary within your time as there is no restriction that you must have to make 40% cash sales over the whole time period. You should follow the required strategy on the basis of experiences and insights that which percentage of sales will help you make larger profits.

In order to get the total Cash Sales for the month of January, type the following formula.

=C7*C10

Here,

C7 = Total Sales Revenue considering unit sales and price
C10 = Cash Sales Percentage

Now, press ENTER to get the total Cash Sales for January.

💭Note: The cell will show the result in percentage. Make sure to change the format from Percentage to Accounting or Currency.

Similarly, apply the following formula to get the total Credit Sales.

=C7*C11

Here,

C7 = Total Sales Revenue considering unit sales and price
C11 = Credit Sales Percentage

✒️ Step 4: Total Revenue Considering Cash and Credit Sales

Now, it’s time to calculate the total revenue considering cash & credit sales and compare it with the revenue considering unit sales and price. This will help you to estimate profit and better insight into your business from different perspectives.

Now, apply the SUM function to calculate the total sales revenue of cash and credit sales.

=SUM(C14:C15)

Here,

C14 = Total Cash Sales of January
C15 = Total Credit Sales of January

Pressing ENTER will result in the Total Sales Revenue. One thing you should keep in mind that, this revenue must be equal to the previously calculated sales revenue considering the unit sales and price.

Now. select the 3 cells of total Cash, Credit, and Revenue of January and drag the fill handle tool to the right end to copy the formula over the whole time period.

See, you have got the sales revenue for each month you are considering. It is noticeable that this revenue matches the venue matches previously calculated revenue.

Now, calculate the grand total of the previously calculated monthly total revenue for the whole time period by applying the formula below.

=SUM(C7:H7)

Within the formula,

C7 = Revenue of January
H7 = Revenue of June

Again, calculate the grand total of the revenue considering cash and credit sales by applying the following formula.

=SUM(C17:H17)

Here,

C17 = Revenue of January
H17 = Revenue of June

In the end, both revenues must match with each other. These two different perspectives will give you a better insight into your business.

Download Practice Workbook

You can download the practice book from the link below.

Sales Budget Format.xlsx

Conclusion

In this article, I have tried to show you some steps of sales budget example in Excel. I hope this article has shed some light on your way. If you have better methods, questions, or feedback regarding this article, please don’t forget to share them in the comment box.

Have a great day!