Tips on using this forum..

(1) Explain your problem, don't simply post "This isn't working". What were you doing when you faced the problem? What have you tried to resolve - did you look for a solution using "Search" ? Has it happened just once or several times?

(2) It's also good to get feedback when a solution is found, return to the original post to explain how it was resolved so that more people can also use the results.

Using pertmaster to assess cost savings

3 replies [Last post]
Neil Brady
User offline. Last seen 10 years 31 weeks ago. Offline
Joined: 4 Jan 2007
Posts: 35
I have been asked to model some cost savings, and I am struggling to think about how to model the answers, so was wondering if anyone could help.

For example
Cost Saving

Cost Saving 1
£974,000 min
£1,640,000 mean
£1,949,000 max

Cost Saving 2
£880,000 min
£880,000 mean
£1,208,000 max

Cost Saving 3
£546,000 min
£780,000mean
£819,000 max


How would I go about modelling this? I am thinking its almost the opposite of a normal simple cost risk as really you want the probability of a potential extra saving.

Does this mean that I should input them as negative costs?

Struggling with this one, as if I plug it in as normal I get a 99% chance of 3300000, P80 of say 3000000 as an example, which is kind of meaningless ? I have a 99% chance of reaching my saving, when really I need to know what the chances are of saving more (Or less)

Any help you can give as always greatly appreciated!!!!

Replies

Neil Brady
User offline. Last seen 10 years 31 weeks ago. Offline
Joined: 4 Jan 2007
Posts: 35
Hi

Many thanks for the reply.

The problem was a missing piece of the puizzle.

When you try and visualise cost savings, the maximum cost saving is actually the minimum cost. What I didnt have was the original cost of the activity that the savings were based on. Once I had this i was able to extrapolate the minimum, most likely and maximum costs, allowing them to be plugged in to pertmaster as normal.

Cheers!

Neil
Safak Vural
User offline. Last seen 2 weeks 5 days ago. Offline
Joined: 12 May 2008
Posts: 117
Dear Neil,

If you have the constraints about the "cost savings" and the situation as you mentioned. You can use solver add-in of excel (it is a numerical method). You can find solver add-in from. Tools-Add-Ins (click solver then OK:). Then Tools--solver.

Then at a empty sheet formulize all of the savings and total savings just for one case (Lets say, 100 for CS1, 200 for CS2 and 150 for CS 3). Formulate the total saving. Then write the constraints. (Amount of materials or just management tendancy, you can use some KPI’s). Then open the solver Tools solver.

Target cell (Total savings)
Maximize (or others!!)
By Changing cells (the qtys or % of Cost savings)
Constraints
Then ok.

You will have the maximized cost saving in your calculation sheet. And you can have detailed reprots of the analysis also.

I read my thread again.A little bit confusing(!). If you have any problems mail me with details and I can reply with a example.

safakv@gmail.com

Best Regards,

Safak
Gary Whitehead
User offline. Last seen 27 weeks 1 day ago. Offline
I don’t think Monte Carlo is the right software package for this. It sounds like a standard binomial distribution probability problem.
Excel has the functionality you need, as I recall. And I’m sure wikipedia can give you the theory.
I’d explain it here, but my stats was always appalling, so i need to look up the formulaes each time i use them.