Sales Spreadsheetselling table
Performing a Sales Analysis on a Spreadsheet
Sales analyses can help make sure that your sales meet your expectation. A sales review, for example, can help you benchmark your sales against a baseline or sales outlook. Any spreadsheet application can be used for this task, as well as Microsoft Excel and Google Docs. Sales force analyses only require fundamental arithmetic operations such as addition, subtraction and division.
The sales figures for the analysis periods and the sales ratio or prognosis for the same periods are included. The sales figures should contain the volumes (number of sales units), turnover, margins (profit) and number of orders for all customers. Enter the following headers in the first line of the spreadsheet, beginning in the second cubicle, or B1: "Actual," "Plan," "Difference," and "Percent.
In the first colum, begin with the second colum or pass the following line title A2: "Volume", "Revenue", "Margin", "Margin Percentage", "Number of Orders", and "Average Order Value". In the Plan columns, insert the forecast sales or quotas in the corresponding rows. At least this should at least cover the revenues. You can also specify a budgeted margins percent or a min. number of unit if you have one.
In the " Current " field, fill in the real sales numbers that you have collected. Select the "Margin Percent" cells in the "Actual" line or B5 cells. Input "=SUM(B4/B3)" and push "Enter". You use this feature to divide the sales profit by the total revenues to determine the profit percentage. Position the pointer in the "Average order value" cells of the "Actual" columns or cells C7.
Input "=SUM(B3/B6)" and push "Enter". Subtract the budgeted sales from the real sales. If the value in the cells is minus, it means there is a shortage, while if it is plus, it means sales are higher than budgeted.
For example, specify ""=SUM(B3-C3)" in the D3 field to determine the revenues differential. Select the first empty row in the Percent row or select row E2. Input "=SUM(B2/C2)" and push "Enter". In this case, the real sales are separated from the budgeted sales so that you receive a percent. Do the same for each of the other rows in the Percent columns.
For example, enter "=SUM(B3/C3)" in column F3 to determine the percent of over- or under-absorption in sales. The table below shows whether sales have surpassed or fallen below your expectation. This also shows whether this is due to a variation in the number of sales or a variation in the magnitude of the mean sales.