# Excel as a product configurator



## Sayre

I have created and have been maintaining an Excel based configuration tool for almost 2 years now. Am self-taught with Excel as it helps with my IT sales job quite a bit. Response from my customers is overwhelmingly positive. Its been nothing short of astonishing. Response from anyone in management on the opther hand, is either indifferent or fearful/ignorant. They seem to see only risk, not opportunity. Will not let me take my ideas any further in their org.

I think I've found a large void that needs to be filled in my industry but unsure of what to do next. Can't seem to find much in the way of existing career paths for Excel based product config tools.

Are there any? I have no formal programming background of any kind, just a lot of 'learn as I go'. But I think that's the beauty of it. Did not seem too difficult to go from zero knowledge to where I am now.

Would appreciate any ionfo, advice, etc.... 

Just found this site tonight and its great!

AS


----------



## Smitty

Welcome to the Board!

That's awesome that you've developed a tool that helps your business!  Unfortunately, many of us deal with or have dealt with managers who don't see the benefit in some work and stifle it.  You could try to shop it around on your own, but note that if you developed it on your company's time, then they most likely own the intellectual rights to it, not you.  Unless you reach an agreement with them to try to sell it on your own (maybe offer to give them a cut), you're treading on thin ice with regards to any profits you may generate.

On the other hand, if you can develop a good development proposal with a cost/benefit analysis, you may get somewhere.  You may even be able to get some recommendations from your customers (a lot of times an outside voice can have far more impact than an inside voice).  Sometimes you have to skip-level to reach ears that are receptive, but that also means running the risk of alienating your direct manager.  

Personally I've found that if I come up with a tool that helps generate revenue, I'm given the ball and told to run.  But I've also been able to justify my work by consistently adding $$$ to the bottom line, which helps a great deal.

Good luck!

Smitty


----------



## Bruno_x

pennysaver said:
			
		

> Personally I've found that if I come up with a tool that helps generate revenue, I'm given the ball and told to run.  But I've also been able to justify my work by consistently adding $$$ to the bottom line, which helps a great deal.



Unfortunately this is the only language a manager understands   

Once I had a great idea about XL based solution in our company. Answer from management : "euh, don't know if it's worth to spend hours on it"
Then I've made a time schedule witch indicated the benefit and the answer became "Go for it and start today will you.")


----------



## Nimrod

Hello Sayre:

What do you mean by a "configuration tool" ? 
What exactly does your program do ??


----------



## Sayre

What does it do:
My business is largely based on putting price quotes together for IT solutions. These quotes are made up of Part Numbers and correct qty's of each part #. As you might imagine, the IT world is large and complex so there are many thousands of part numbers, many optional ways to solve for a customer's IT needs. 

The simple answer to what my tool does, is to put these part #'s onto one spread sheet, then allow the user to use Plain English drop down menus to specify the solution they want to quote. When the final solution is configured, the user presses one button and the correct part #'s and qty's appear, with a price for eack line and a grand total at the bottom. Involves a lot of formulas and I have added some macros to automate some "bells and whistles". 

Customers love it and quite literally clamor for it. My management has allowed me some very limited room to play in this area, but they wont let me take it nationwide. Also don't want to pay me anything extra for the value it brings.


----------



## Sayre

"Then I've made a time schedule witch indicated the benefit and the answer became "Go for it and start today will you."

Do you think you could show an example of the kind of benefit analysis you mention in this sentence?

I did put something like this topgether before but it did not get me very far. Am wondering if I am just not emphasising the right things?


----------



## Smitty

I tend to focus on things that emphasize measurable cost saving benefits, productivity gains or revenue generation.

I.E. This method will take 40 hours of development time at $xx/hour, but will save xx man-hours per week at an average of $xx/hour, will amortize development in xx man-hours and return $xx to the bottom line over th course of a year.  In come cases it's pretty easy, in other cases you need to be a salesman.

Smitty

EDIT: Welcome to the Board!


----------



## alispri

Hi, I realise that this is an old post, but I stumbled across it from a Google search. I was wondering if anyone could enlighten me on how to create some sort of excel configuration tool, as I am currently trying to do the same.

I have managed to do it with a load of vba if conditions, but want to make it user maintainable - possibly a tabular data entry format for the conditions and I wonder if anyone has any ideas on this?

Many thanks.


----------



## petermartins

I'v been thinking about building a product configurator (something like a CPQ and a make-to-order application) with EXCEL for a while now but haven't gained the courage to do it yet because it's very complex. I have tried to find books and articles on the subject but it just does not exist.

My data will be separated hierarchicaly in multipe sheets and books. Basicaly it needs to organize, calculate, and produce lists of the following data:
1. The first set of data holds the basic product models, that I call for example B,BG,and BB. Based on the over all dimensions of the product I need to produce data for each part to be produced (materials and production time). And to complicate things even more there will be 3 paralel sets based on diferent material quality and production methods.
2. The next set of data joins the basic product models to another set of components (each paralel set will have it's one type of components) that create a more complex product down the production line. So I'll have BxxP1, BxxP2, BxxG4, BxxG13, BBxxP1, and BBxxP2. The "xx" is the X dimension while the Z and Y dimension are previously established as standard at the begining of the production. At this level I'll need to add some more production time.
3. And as a result I'll get a 3 price quote for the sum of a list of products, for example:
BN30P1
BN40P1
BN45P1
BN90P2
BB90P2
B60G4
And I'll get a list of parts, components and production time.

Does this look feasible? Is there any literature on this kind of project?


----------



## kevinsbennett

Once upon a time I tried to create a product configurator based on excel for a semiconductor equipment manufacturing company.  While it worked, it was not the right system to perform this task.  There are now many sophisticated product configurator applications that are built for this exact reason.  They can automate quoting, BOMs, CAD models and drawings, and even connect to your other business systems like Salesforce CRM and ERP systems.

Here's an example of such a product configurator.
Best check it out lest you waste alot of time using the wrong tool for the job.

Good luck!


----------



## Sayre

I have created and have been maintaining an Excel based configuration tool for almost 2 years now. Am self-taught with Excel as it helps with my IT sales job quite a bit. Response from my customers is overwhelmingly positive. Its been nothing short of astonishing. Response from anyone in management on the opther hand, is either indifferent or fearful/ignorant. They seem to see only risk, not opportunity. Will not let me take my ideas any further in their org.

I think I've found a large void that needs to be filled in my industry but unsure of what to do next. Can't seem to find much in the way of existing career paths for Excel based product config tools.

Are there any? I have no formal programming background of any kind, just a lot of 'learn as I go'. But I think that's the beauty of it. Did not seem too difficult to go from zero knowledge to where I am now.

Would appreciate any ionfo, advice, etc.... 

Just found this site tonight and its great!

AS


----------



## hellebore08

Hi Sayre,

Just to let you know, there are many companies offering incentives for their initiatives (tools, automations, etc) like your configuration which can be very useful.  Here in Philippines, Maersk Global Service Centre (Phils.) Ltd. and Accenture are two of what I have known recently that offers these benefits.

These infos are not critical to the image of the said companies, so I'm sharing it with you.

They appreciate and welcome every opportunity that comes with employees' proposals by giving credit and money to them.  Unfortunately, whatever you made within the companies' premises becomes their property.

I'm actually from Maersk (but not anymore), and not from Accenture.  I just posted the two that I know can pay you for what you do.  (Pay = not just money).

So that's it.

Cheers!


----------



## JHall

There are lots of things that you can do with Excel.  Ifyou are looking to use Microsoft Office automation to create proposals or configure products -- our company, analysisplace.com does this.  You can check out our free IT Project ROI Tool to see how it works. Yourcustomer profile, solution configuration, benefits/ROI calculations, pricinglookups, etc. are modeled in Excel.  Itthen produces customized Word proposals or business cases.  The Word report templates are embedded in theExcel file.  The system also enablessharing/collaboration, currency switching, language switching, change tracking,and scenario analysis.

Julie


----------



## Prevost

Alright, so now this post is really old, but I thought it might be worth it to add something (I am relatively new VBA but I think the idea is relatively solid). I wanted to create a product configurator for the company I worked for as I saw time being wasted by relying on one order desk worker to tell the other two order desk workers 'Yes that part can go with that' or 'No, you can't put those two parts together'. I am still in the midst of working on it so any suggestions are appreciated. 

I created a list of all the different product configurations that existed and put that into Excel. The user then selects the configuration from the top and works their way down. So the first decision they would have to choose which controls the rest of the product is in the first combobox. Then, based on that first combobox value, all the products containing that initial value are stored in an array (it's actually their row numbers for later use). I then use that array to create the drop down menu in the second combobox which displays all the possible choices for the next parameter (it's fairly easy to create a array to display a non duplicated alphabetical/numerically organized list) for which the user will select. And basically, the process continues on until there is only one selection left.

The only snag is that this works only when the user chooses the product parameters in the order that has been created. It does not give the possibility to search for all products that contain parameter 'A', where 'A' could be any of the parameters that are chosen.

I just wanted to post this to stop people from doing a million IF statements! That is incredibly time consuming and not robust at all. If one thing changes, then all the IF statements have to be changed. While my code does contain some IF statements, these are not the lines that create the possibilities. The arrays are.

My Excel data sheets have headers on them and my code looks for those headers to get the column number. That way, if someone wants to reorganize the columns, not all relationships are lost. I try to do this with all my codes as I am the only one in my organization that knows how to use VBA.

Just thought I would add that comment, as when I posted a similar question someone posted and said 'Just create IF statements', which is not always the case.


----------



## kozd87

Smitty said:


> I tend to focus on things that emphasize measurable cost saving benefits, productivity gains or revenue generation.
> 
> I.E. This method will take 40 hours of development time at $xx/hour, but will save xx man-hours per week at an average of $xx/hour, will amortize development in xx man-hours and return $xx to the bottom line over th course of a year.  In come cases it's pretty easy, in other cases you need to be a salesman.
> 
> Smitty
> 
> EDIT: Welcome to the Board!



Hi Smitty.
I know that this i an old post. But what you exactly are mentioning above, is what I might need in a small project i am doing with a company, while i am studying. 
Is it possible that you have made this in an Excel sheet, or? 
I want to make a "product configurator", that can tell i.e. this product type will take 20 hours to produce in the production at a cost of xx$/hour, but will save xx man.-hours per week or day at an average of xx$/hour, will amortize development in xx man-hours and return xx$ to the bottom line for a exact production of one product type? 

Hope it did´nt get to long :D

Best regards

Kerim


----------



## Smitty

Welcome to the Board!

I might look at the link JHall provided above.


----------

