How to Prepare a Sales Budget with Example in Excel: 4 Methods

✒️ Step 1 – Assigning Factors of Sales Budget

• Decide a time limit over which you want to create the sales budget format. Our Excel file contains the budget format for the whole year.
• Assign the Sales Budget factors to your budget sheet. The factors are:

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

• Set the price of the unit sold products. It may be the same or vary from time to time according to your company policy. We assumed the price of the unit products same over the considered time period. Apply it according to your sales.

✒️ Method 2 – Calculate Sales Revenue

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

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

• Press ENTER and the cell will give you the total sales revenue for the month of January.
• 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.

• Get the expected result for the applied time period.

✒️ Method 3 – Input Cash and Credit Sales Percentages

• We 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 about which percentage of sales will help you make larger profits.

• Get the total Cash Sales for the month of January, type the following formula.

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

• Press ENTER to get the total Cash Sales for January.

• Apply the following formula to get the total Credit Sales.

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

✒️ Method 4 – Total Revenue Considering Cash and Credit Sales

• Apply the SUM function to calculate the total sales revenue of cash and credit sales.

• 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 is that this revenue must be equal to the previously calculated sales revenue, considering the unit sales and price.

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

• Got the sales revenue for each month you are considering. It is noticeable that this revenue matches the venue matches previously calculated revenue.

• Calculate the grand total of the previously calculated monthly total revenue for the whole time period by applying the formula below.

Within the formula,

• C7 = Revenue of January
• H7 = Revenue of June

• Calculate the grand revenue total considering cash and credit sales by applying the following formula.

• C17 = Revenue of January
• H17 = Revenue of June

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

Download Practice Workbook

You can download the practice book from the link below.

Related Articles

• How to Create Bi Weekly Budget in Excel
• How to Create Uncertainty Budget in Excel
• How to Make Food and Beverage Budget in Excel
• How to Prepare a Vacation Budget in Excel
• How to Create a 50-30-20 Budget Spreadsheet in Excel
• Create Retirement Budget Worksheet in Excel

<< Go Back to Budget Template | Finance Template | Excel Templates