MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Automating a Price Book


Posted by Neil McCann on September 19, 2001 5:48 PM

Need your help & ideas!!.

I?m trying to automate somewhat our pricing system. It presently consists of a book with about 65 pages of prices (Retail & Wholesale). I can combine those 65 pages down to about 12 pages, which includes both the Retail & Wholesale price.

In the past when we priced a home, each homes different line items was retyped onto a Schedule A for the customer to look at, review and then sign. Many errors where made and it was labor intensive. I have currently combined most of the common items into about 12 spread sheets, the total of the 11 are linked to a summary Schedule A. The problem is that every pricing request has 12 pages and when we give it to the customer we are basically passing out our price book, the competition I?m sure will be pleased when they eventually get their hands on one.

We thought about only handing out the summary page but then the customer doesn?t have the detail describing their home and they are paying for (what?s in & what?s could be problem down the road). I can just hear the complaining ---?I thought that was included?..You cheated me!?

What I?m trying to do is select only those items in the 11 spreadsheets that have something other 0 (zero) in a specific column (column B) and move it to the Schedule A for both Retail & Wholesale. I?ve attached a small example for you to see.

In column A would be the internal item #, in column B would be the number of items selected (standard interior doors for example), column C would contain the info on how each is priced, (ea. sf. lf. Pr. Un. ls, etc.), column D would be the description of the item, column M is the retail price of each unit, column N (formula) is the retail extension of column b * column M. Column O is the wholesale price and column P (formula) is the wholesale extension of column b * column 0. Setting this up so far is the easy part!!

Now for the hard part, I want to select only those items that have a value greater then or less then 0 in column B. I want to move columns A, B, C, D, M & N into a orderly list on Schedule A spreadsheet that is totaled, it will detail the retail items that the customer will be getting in their new home & will be the handout the customer takes home with them.

At the same time I want to select those items that have a value greater then or less then 0 in column B. I want to move columns A, B, C, D, O & P into a list on Schedule A that is totaled, it will detail the items that will form the Wholesale pricing of the customers new home.

Both of these lists (both Schedule A?s) will be variable in length, some customers could have 15 items and some could have 50+ items!

I?m not sure how to tackle it, with VLOOKUP & HLOOKUP, Macro?s, some kind of Report Manager or plug-ins, other items have crossed my mind, but nothing jumps out as the solution.

I guess the 1st and most basic question is -- is Excel capable of handling such a problem or should I be trying Access?

Thanks for your thoughts and any help you can cast on this problem.

If anyone has any idea's email me and I'll send a sample collection of spread sheets with some real live data for you to work with.

Neil


Posted by Tom Urtis on September 20, 2001 7:08 AM

Here's my take

Neil,

You've outlined a business operation objective that appears do-able in either Excel or Access.

It sounds like you are headed in the right direction with regards to having source tables using V/HLOOKUP or (my preference) INDEX MATCH in order to return the retail items detail depending on items you are selling. You will almost certainly find data validation useful as well, so only the items listed in an Items table may be entered. The application should also be automated with (for example) custom command buttons to lead any user through the application, so the result would be the same no matter who the salesperson is doing the input.

The easy part will be to utilize this message board for how-to solutions as you progress step-by-step towards project completion. The important thing though is that, given the critical nature of a company product pricing model, you'd likely be well served by finding a good Excel spreadsheet writer who will hear your voice as you describe what you want, listen to end users, and bring to bear their skill to develop a final product you will be happy with.

As projects go this does not sound too difficult, just important that its blueprints and framing are adressed properly from the get go, and for that there's no substitute for consistent client-user interaction.

Good luck to you.

Tom Urtis