cornflakegirl
Well-known Member
- Joined
- Nov 4, 2004
- Messages
- 2,023
My company is looking to launch a new product. It will be a "value" version of an existing product, and to create it we will make about 20 changes to the existing product. Some of these will be increased charges for optional features. Others will be removing features to cut costs. The intention is to use these to finance reducing our mark-up on the core product, thereby reducing the price and driving volume.
I need to model this. I have a pricing model for the existing product, and my initial plan was to make the 20 changes, then goal-seek the increased profit back to normal levels by reducing mark-up, then apply a price-sensitivity formula to calculate the volumes to run through the model.
However, I need to able to value the contribution of each of the 20 changes. I normally do this by creating 20 scenarios (each one adds in an additional option) which are controlled from one cell - I then run a data table on this cell to get the value of each scenario. But I can't combine this with calculating my reduced mark-up through goal-seek.
I've also considered trying to calculate the commission reduction directly for each of the 20 changes - and then inputting this into the pricing model. But the calculations are quite complicated, and some of them involve the mark-up (eg finance charges and debt are calculated on total selling price). So I think I'd end up with a fag packet calc that then didn't give the answer expected when put into the proper model.
I'm wondering whether allowing circular references would help? But I'm not sure how the data table would cope with them?
If you've read this far - thank you! I'd really appreciate any suggestions.
I need to model this. I have a pricing model for the existing product, and my initial plan was to make the 20 changes, then goal-seek the increased profit back to normal levels by reducing mark-up, then apply a price-sensitivity formula to calculate the volumes to run through the model.
However, I need to able to value the contribution of each of the 20 changes. I normally do this by creating 20 scenarios (each one adds in an additional option) which are controlled from one cell - I then run a data table on this cell to get the value of each scenario. But I can't combine this with calculating my reduced mark-up through goal-seek.
I've also considered trying to calculate the commission reduction directly for each of the 20 changes - and then inputting this into the pricing model. But the calculations are quite complicated, and some of them involve the mark-up (eg finance charges and debt are calculated on total selling price). So I think I'd end up with a fag packet calc that then didn't give the answer expected when put into the proper model.
I'm wondering whether allowing circular references would help? But I'm not sure how the data table would cope with them?
If you've read this far - thank you! I'd really appreciate any suggestions.
