Decision Tree and Multipliers

[mikebw]

I’m generating a spreadsheet to help with service pricing. I would like to make categories and sub-questions for each specific category (some questions only apply to some answers). For each answer, I would also like to store a price modifier.


For simplicity, let’s say I want to paint. My initial fee is 100.
First I might ask: Interior or Exterior?
If it’s interior, I might increase my initial fee by 10%.
Then, if Interior, I might ask the total square footage of rooms (less than 500 ft^2? 500 – 1000 ft^2? 1000 – 10000 ft^2?).
If it’s <500 ft^2, I might add 20%.
I might then ask if there are windows in the room (because I won’t need to paint that section).
Subtract 10%.
Then I might ask if there’s wallpaper.
Add 5%.
Then I might ask if the wallpaper is before 1940.
Etc……


Unfortunately, the questions are different depending on which decision path you take. Some questions apply to 90% of the paths, and some questions apply to only a few.

8 categories, 8 questions deep, with 8 options per question gets unmanageable pretty quickly.

Does anyone have any suggestions for implementation?

At first I made many excel worksheets within one workbook. The various answer categories were all hyperlinked to the next worksheet of questions for that specific path. This became unmanageable very quickly.

Now I’m thinking something more like a master sheet and a pivot table making expanding and shrinking sections easier, but I'd love to hear any suggestions first.

I also wonder if there isn’t a tool that I’m not familiar with that might be much better suited for this.

Thanks for any suggestions you might have,
Mike

[Speshul]

Maybe something like....
See yellow cells. Using Data Validation lists for dropdown boxes.

options.xlsx

[Speshul]

Doublepost, oops.

[mikebw]

Speshul, that looks great, thanks! Do you think there's a way to limit the drop-down boxes conditionally so that the previous 'answers' influence the remaining options? Many answers/questions won't be appropriate based on the previous answers and the drop-down list would grow very very quickly.

Thanks again,
Mike

[newdoverman]

Write out your problem on a large sheet of paper and have it organized like an ORGANIZATION chart. Wherever on the chart a list of options is required, write out the list and if there are choices associated with those options, write them off to the side of each option. Draw boxes or colour the lists so that they stand out. In your Excel workbook, you will dedicate a worksheet for all the lists that you require.

Now, looking at your chart, will one worksheet with questions satisfy both interior and exterior jobs. Layout the order of the questions and answers as you would like them to appear. If you require only one worksheet then you will be ready to start building your Excel workbook. If you require two worksheets repeat the question and answer layout for the second worksheet until you are satisfied with the result.

Now, start to build your worksheets by typing in the questions and placing them according to your planned layout leaving space for the answers. Create all the lists that you have identified (on a worksheet for lists). When you have that done, it is time to write the formulae required to bring in the data from your lists required to answer the questions posed and to make the calculations according to the answers.

[Speshul]

Speshul, that looks great, thanks! Do you think there's a way to limit the drop-down boxes conditionally so that the previous 'answers' influence the remaining options? Many answers/questions won't be appropriate based on the previous answers and the drop-down list would grow very very quickly.

Thanks again,
Mike

Yes, in a way. It will be ahrd to set up for all of your data but once set up it should be easy to maintain.

I would look into named ranges if you do it this way, it will be very complicated to set to if you have 8^3 possible combinations... Named Ranges will make writing formulas significantly easier and more intuitive for this type of application.

See the attached for an example of one way to do it. Changing Selection in B14 will adjust the possible Category values.


options3.xlsx