Determining Values to be paid

[jimbolove]

Since you all were so helpful on my last question, maybe you can help point me in which direction I should go on this one. It's way more complex so I thought about using access or excel and am leaning toward excel. I want to set up a worksheet/table for Sales commissions and billings. We may have multiple sales done on different dates, each with a differnt commission percent on each. But we are only paying commmissions once we receive payment from the customer and it may only be for partial payment of some of the invoices. But I want to apply the amounts to each sale in date order until each is sale maxed out. And then any amount left over to the next sale.

Period Cust# Sale % Payment
1 81243 50,000 50% 125,000
2 81243 35,000 75%
3 81243 78,000 63%
3 78643 100,000 28% 35,000
2 12897 24,000 56% 10,000
4 49874 58,000 87% 62,000
5 49874 13,000 71%

 

[SkipVought]

Hi,

Why don't you have a reference ID for each sale, like an invoice? The payment ought to be applied to the sales transaction.

Why is ther no Sale date?

What is Period?

I'd suggest a different table for payments that includes payment date, customer and sales reference.

Skip,
[sub]
[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion! [/sub]

 

[firefytr]

It also sounds like you need a seperate sheet/table of each unique customers and their totals, respective per job and respective total (for any overages).

Personally, I think this would work much better in Access, or even QuickBooks. Can it be done in Excel? Of course, and I might enjoy it more there, but it's probably not the *right* place for it. Most people will still choose Excel for it's ease of manipulation.

Everything you need done should be quite possible in either application.

-----------
Regards,
Zack Barresse

 

[SkipVought]

Not a good idea to segment source data. May be okay on a VERY LIMITED basis, but multiple sheets -- bad idea!

Best suggestion is an application like QuickBooks. Get something that already works.

If you choose to design an application, you have a pretty significant job ahead of you. You really need to understand relations (tables)

http://www.alphabetcitydataworks.com/FundamentalsOfRelationalDatabaseDesign.doc

Skip,
[sub]
[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion! [/sub]

 

[firefytr]

Hmm, not sure why that's such a bad suggestion. Would you mind telling me why you think so?

-----------
Regards,
Zack Barresse

 

[SkipVought]

What if I need to know the total payments received for a particular month or quarter.

What if I need to know what percent of of my sales is customer a?

The scope is endless for analysing history or forecasting

Skip,
[sub]
[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion! [/sub]

 

[firefytr]

Maybe you misinterpreted me. I was specificaly talking about a table only, not necessarily for data capture/entry. Having this unique list is much like how you would have a table structure in Access, housing various parts of information and structured so in your relationships.

Keeping all data together is paramount, yes. This way you could get what you want easily with autofilters or pivot tables/charts.

-----------
Regards,
Zack Barresse

 

[jimbolove]

I put the table in to simplify things the listing is quite cumbersome as it's got more than 2000 entries on the sales side (there could be as many as 20 for each customer) and yes there are invoice numbers associated with each sales entry but when the customer submits payment they don't reference any specific sale or date, just a lump sum amount. I went through and sorted the list by period the sale occured and then did a v-lookup on the amount paid from each customer. Then did a series of IF statements (the first being to check above to see if there were multipe sales) so that a customer had to pay for the first sale and as well they couldn't overpay on anyone entry. Then apply any overpayments to the following sale (if multiple) in the list which eventually to apply overpayments to each subsequent sale.
I was hoping possibly someone had some experience in using the MAX function.

 

[firefytr]

Also take a look at the SUMIF and COUNTIF functions.

Post away your questions about MAX.

Take a look at Pivot Tables as well.

-----------
Regards,
Zack Barresse

 

[SkipVought]

Isn't that what he's talking about?

I want to set up a worksheet/table for Sales commissions and billings.

...I want to apply the amounts to each sale in date order until each is sale maxed out. And then any amount left over to the next sale.

Are you referring to REPORTING? I wouldn't do that on separate sheets either. I'd do that on one sheet with one or more combo box controls. Otherwise I'm changing multiple sheets each time my reporting requirement changes.

So what would be a good design approch, a customer per sheet?


Skip,
[sub]
[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion! [/sub]

 

[firefytr]

Are you asking me? In Excel? I would keep all raw data centralized to one sheet. All relevant data should be housed here in table format. As for reporting, I would keep one sheet for a report that is interchangable for different customers. If need be, I would create a form for selecting which customers to view reports on. After the data entry phase, I would (personally) probably lace it heavily with VBA automation.

-----------
Regards,
Zack Barresse

 

[SkipVought]

??? Okay ???

Skip,
[sub]
[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion! [/sub]

 

[firefytr]

You lost me Skip.

-----------
Regards,
Zack Barresse