I am looking for the correct formatting to reconcile two spreadsheets. I am
looking to reconcile sales with commission payout. Any suggestions on how to
format the worksheet to reconcile numerous cells in a row?
I have a few books on how-to with excell and have attended many training
sessions but am not finding what I need, so even a point in the right
direction will help. Thanks
--
Dee

Dee wrote:
I am looking for the correct formatting to reconcile two spreadsheets. I am
looking to reconcile sales with commission payout. Any suggestions on how to
format the worksheet to reconcile numerous cells in a row?
I have a few books on how-to with excell and have attended many training
sessions but am not finding what I need, so even a point in the right
direction will help. Thanks

Hi Dee,

You're not giving us much to go on here. We can't see your data, and
it's not clear what it means to "reconcile sales with commission payout".

When I think of commissions I would imagine a formula that adds up sales
by agent in a certain period (day, week, or what have you), looks for a
minimum sales volume, then computes a commission according to sales,
perhaps tiered in such a way that higher sales have a higher commission
rate.

To do the analysis in my scenario, I would have the following data entry
columns in a worksheet:
Date
Agent
Sales

In another worksheet, perhaps using a pivot table, summarize the results
by Agent, compute the minimum sales volume flag.

I might need a separate table that maps sales amounts to commission
rates, if this is a tiered benefit. Then I can compute the commission by
looking up the commission rate in the tier table, and multiplying this
by the sales amount.

Now, the reconciliation part. I imagine I would have a table of
commissions that have already been paid. I would take my calculated
(expected) commissions, look up the actual, and compare them
side-by-side, perhaps subtracting expected - actual to see any
over-(under-) paid commissions.

Hope this gives you some ideas.

Dear SMartin,

Thanks so much for your post but what I want is somewhat simpler than all of
that. I am looking at a spreadsheet of what I sold and then I am comparing
it to what I was paid by the company.
I need to know the function/formula I need to look at one spreadsheet and
compare it directly to the payout spreadsheet.
For example, I have recoreded 6 sales of tv's, recorded by customer name,
product sold and commission paid.
I need to take my payout for commissions that the company gives me and check
to see if the sale to John Smith for the 19" tv was recoreded as such and if
the $45 commission I am owed was paid... so I am looking for a function that
compares several cells on a line with several cells on another line on
another spreadsheet or worksheet and will simply return true or false to the
data if it matches or not...
Am I making any sense? and please tell me this is a fairly simple fix... :)
It will make my home based business run so much faster!! thanks for your help
--
Dee


"smartin" wrote:

Dee wrote:
I am looking for the correct formatting to reconcile two spreadsheets. I am
looking to reconcile sales with commission payout. Any suggestions on how to
format the worksheet to reconcile numerous cells in a row?
I have a few books on how-to with excell and have attended many training
sessions but am not finding what I need, so even a point in the right
direction will help. Thanks

Hi Dee,

You're not giving us much to go on here. We can't see your data, and
it's not clear what it means to "reconcile sales with commission payout".

When I think of commissions I would imagine a formula that adds up sales
by agent in a certain period (day, week, or what have you), looks for a
minimum sales volume, then computes a commission according to sales,
perhaps tiered in such a way that higher sales have a higher commission
rate.

To do the analysis in my scenario, I would have the following data entry
columns in a worksheet:
Date
Agent
Sales

In another worksheet, perhaps using a pivot table, summarize the results
by Agent, compute the minimum sales volume flag.

I might need a separate table that maps sales amounts to commission
rates, if this is a tiered benefit. Then I can compute the commission by
looking up the commission rate in the tier table, and multiplying this
by the sales amount.

Now, the reconciliation part. I imagine I would have a table of
commissions that have already been paid. I would take my calculated
(expected) commissions, look up the actual, and compare them
side-by-side, perhaps subtracting expected - actual to see any
over-(under-) paid commissions.

Hope this gives you some ideas.