A sales analysis can ensure that your sales are meeting your expectations. For example, performing a sales analysis can help you compare your actual sales to a minimum quota or a sales forecast. You can use almost any spreadsheet software for this job, including Microsoft Excel and Google Docs. A sales analysis requires only basic mathematical functions like adding, subtracting and dividing.

Preparing the Spreadsheet

Gather your data. This includes sales for the time period being analyzed, as well as the sales quota or forecast for that same time period. Sales data should include volume (number of units sold), sales revenue, margin (profit) and the number of orders for all clients. The time period can be annually, quarterly, monthly or weekly.

Type the following column titles in the first row of the spreadsheet, beginning in the second cell, or B1: "Actual," "Plan," "Difference" and "Percent."

Type the following row titles in the first column, beginning in the second cell, or A2: "Volume," "Revenue," "Margin," "Margin Percent," "No. of Orders" and "Average Order Value."

Type the forecasted sales or quota in the appropriate cells of the "Plan" column. This should include revenue at minimum. If you have a planned margin percentage or minimum number of units, you can enter them as well.

Type the actual sales figures you gathered in the "Actual" column.

Adding Functions

Click on the "Margin Percent" cell in the "Actual" row, or cell B5. Type "=SUM(B4/B3)" and press "Enter." This function divides sales margin by total revenue to calculate the margin percentage.

Place the cursor in the "Average Order Value" cell of the "Actual" column, or cell B7. Type "=SUM(B3/B6)" and press "Enter." This divides the revenue by the number of orders, calculating the average order value.

Click the first empty cell in the "Difference" column, or cell D2. Type "=SUM(B2-C2)" and press "Enter." This cell subtracts planned sales volume from actual sales. A negative value in the cell represents a shortfall, while a positive number represents higher sales than planned. Do the same for each of the cells in this row. In cell D3, for example, type ""=SUM(B3-C3)" to calculate revenue difference.

Click the first empty cell in the "Percent" column, or cell E2. Type "=SUM(B2/C2)" and press "Enter." This divides actual sales volume from planned volume, giving you a percentage. Repeat this for each of the other cells in the "Percent" column. In cell E3, for example, type "=SUM(B3/C3)" to calculate the percentage of overage or shortfall in revenue sales.

Analyze the numbers to determine what measures you need to take for the next time period. This spreadsheet identifies if sales have exceeded or come short of your expectations. It also shows if this is caused by a change in the number of sales or a change in the size of the average sale.

Tip

Reformat the spreadsheet as desired. It helps, for example, to put either shortfalls or overages in a different color. You can also change the format of cells showing percentages to be displayed as perctanges rather than decimal numbers.