Commission.xls

Create a spreadsheet that calculates the individual salesman’s • sales commission. • percentage of sales within the group. • and required payment due a t the end of each week. (The running total of sales commissions minus retainers already paid) By using the totals of each salesman’s sales figures available from our point of sales software. Our POS S/W creates reports that show the profit from sales by each sales person and the total opening expenses of the business. The agreement with the sales people is they receive 50% of the profit of sales they make as individuals, minus a percentage of the business expenses in proportion to how many salespeople there are at that office. The business expense percentage is calculate by how may salespeople there are. Eg if there were 5 salesman, 100% of the expenses would be divided by 5. So the calculation of expenses for each salesperson would be 20%. Each sales person is given a retainer. This is a loan given in advance against future commissions made. I would like to shows this amount for each of the salesman as a running total. I have tried to create this spreadsheet but lack the expertise. See the attached file [login to view URL] The problem I cannot work out is as in week7. (Cell F10) The formula doesn’t work if the sale profit is less than the expenses. This negative figure is still divided by 2 and shows the loss as 50% of what it should be in the cell and the column total sum formula. This is incorrect, 100% of the loss should be included in the column sum total.