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 so easy to create a sales budget format.


Download Practice Workbook

You can download the practice book from the link below.


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.

  1. Units of products sold
  2. 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 as 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

factors for Sales Budget Example in Excel

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

Number of products in sales Budget Example format

  • 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.

Read More: How to Make a Budget in Excel (2 Easy Methods)


✒️ 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

calculate Revenue in Sales Budget Example in Excel

  • 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.

Revenue Formula in sales Budget Example in Excel

Read More: How to Calculate Remaining Budget Using Formula in Excel


Similar Readings:


✒️ 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 which 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.

Cash and Credit Sales in Sales Budget

  • 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.

Cash sales in Budget Format

💭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

Read More: How to Prepare Budget for a Company in Excel (2 Suitable Examples)


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

Now, it’s time to calculate the total revenue considering cash & credit sales and comparing 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, 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 with the venue matches the previously calculated revenue.

Monthly Revenue in sales Budget Example in Excel

  • 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

Sales Budget Example in Excel

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


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. For more queries, kindly visit our website ExcelDemy. Have a great day!


Related Articles

Rafi

Rafi

Hey there! I am Md. Rafiul Hasan. Currently I am working as an Excel & VBA content developer. I like new ideas and want to explore the field of innovation. Excel has saved our worktime and made it easy for us to quick calculations. I am trying to make it easier for you to overcome the obstacles you face while working on Excel. Stay connected!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo