Thread: Excel Pricing Model (Monte Carlo?)

Hi.
I am putting together a pricing model and I need a bit of help please (maybe alot saying that)...

On the attached sheet the user would input specific customer data using the scroll bars and the number of units.

I need help with a formula which will then work out what the prices should be to achieve a target return on sales % (cell C37) based on the position of the scroll bars. (these prices would go in the light red shaded cells)

However the added complication is I would also like the prices calculted above to factor in the best, expect and worse case scenarios for the scroll bars and the cost data.

Has anyone got any suggestions for how I achieve this or experience of other models I can learn from? I have googled pricing models but couldn’t find anything relevant.

I have attached the spreadsheet for reference.

I have updated the file - if anyone knows how I can incorporate the best/expect/worst data I would be grateful.

I was thinking of using three scenarios (Best/Expected/Worst) but would prefer if it would take into account multiple variables e.g. Best Annealed/Toughened, worst Textured, expected Georgian etc.

Since designing the model I have realised that it wouldn’t be the selling prices that would change but in fact the total sales and total costs - highlighted in orange.

Paul