How to Calculate Commissions in Excel
By Robin Reichert
Microsoft Excel can keep track of and calculate your sales commissions. You can also set up a number of different scenarios with different commission rates to calculate your earnings depending on the selected markup. Calculating your commissions in Excel can help you determine the markup that will maximize your earnings without pricing your product or service too high. You can save, modify and print your Excel spreadsheets. Reports and graphs can also help you to visualize your earnings and potential earnings based on different commission rates.
Simple Calculation
Step 1
Open Microsoft Excel. Enter the product number or description in column A on row 1. Enter the unit price in column B. Type the number of units sold in column C.
Step 2
Calculate the total sales in column D by clicking cell D1 and then clicking the function box. Type an equal sign (=). Click cell B1 to pick up the value of each unit sold. Type an asterisk (*) for the multiply function and then click C1. Click the green check mark to complete the function. Excel will calculate the total value of goods sold by multiplying the values in B1 and C1 together.
Step 3
Type the commission percentage as a decimal amount in cell E1. For example, if the commission is 12 percent, enter .12.
Step 4
Click cell F1 and type the equal sign (=). Click D1 to enter the total value of sales in the function bar. Type an asterisk (*) for the multiply function and then click E1 to enter the commission percentage. Click the green check mark to enter the completed formula and view the result.
Scenarios
Step 1
Open Microsoft Excel. Open an existing spreadsheet or create a new spreadsheet that contains a product description, unit cost, markup percentage and sales price. Save your spreadsheet.
Step 2
Click the Data tab of the Ribbon. Click the "What If Analysis" button and select "Scenario Manager" from the drop-down menu. When the "Scenario Manager" dialog box opens, click the "Add" button. Type a name for the new scenario in the "Scenario name" field of the "Add Scenario" dialog box.
Step 3
Press "Tab" to move to the "Changing cells" field of the "Add Scenario" dialog. Select the cells that you want to change. Hold down the "Ctrl" key and click each cell that you will change. Change items such as the number of units sold, the markup and the commission rate. Click "OK" to continue.
Step 4
Enter the new values for the new sales commission scenario. Change the number of units sold, the commission percentage or the markup. Click "OK" to save the values and continue. The commission rate will change depending on the value you placed in the commission percentage.
References
Tips
- If the commission rate is the same for all sales, you can calculate the total commission for all sales by adding all sales and the number of units at the bottom of each column and then multiplying all sales by the commission rate.
Writer Bio
Robin Reichert is a certified nutrition consultant, certified personal trainer and professional writer. She has been studying health and fitness issues for more than 10 years. She holds a Bachelor of Arts in psychology from the University of San Francisco and a Master of Science in natural health from Clayton College.