• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sensitivity Analysis

K

kay

New Member
Dear Freinds,


I have been strugling with an issue for the past one week. I have a set of historical data containing number for students in a program and the average prices for the programs per month, quater and year. I want to see if price increase will have any effect on the number fo student for the new month. What is the best way to do this. I already have the data but dont know how to send it
 
Hi,


Simply upload ur data on an online file sharing site so that any one can reach it.. Your problem looks interesting can u send me the file at faseeh10@hotmail.com


Best Regards,

Faseeh
 
https://rapidshare.com/files/4273617159/pricing_analysis.xls


This is the link to the sheet.

Kindly send back to kay.lawal@ymail.com
 
Hi, kay!


It's very difficult to get a key indicator useful for every price fluctuation, because of many parameters additional to simple law of supply and demand, like seasonality, periodicity, and more particular those of the real case/market/business.


The business owner is precisely the guy who knows all this variables and the relative effects since those could skew the KPI.


From a very simple point of view, you can do the job in two steps:


1) Make a comparison between the fluctuations of students and fees, and display them in a single graphic. Give a look at this file:

http://dl.dropbox.com/u/60558749/Sensitivity%20Analysis%20%28for%20kay%20at%20chandoo.org%29.xlsx

There are two tables with coeficients related to media=1,0000 for both students and fees, and a ratio of the ratios. It's just simple and not fully adjusted but if that's what you're looking for, you would see that there isn't a direct impact of cost on quantity of students.


2) Using these previous values, make a projection using Excel statistical functions to find out what would happen when there's a fee change.


Hope have aided a little.


Regards!
 
Hi Kay,


Sorry for the late reply, I hope you have found my email. Here is the file i have worked out:


http://dl.dropbox.com/u/60644346/pricing_myworkout.xlsx


But before you browse this file let me tell you some important points!


Whenever it comes to reaching some result or deciding something on the basis of a data set, i.e. whenever you head for decision making or forecasting, the most important thing is that "Whether two variable show some correlation or not?" If they are positively correlated then your forecast can be amazingly accurate. This is a text book fact.


When i started examining your file, I found that "Correlation" between no. of students enrolled and your tuition fees approaches zero. That is a very bad omen when it comes to forecast what could it result into if you changes fees by 4%.


I used Book Operation Management by Render & Heizer that i studied in Production Engineeing to use "Associative Forecasting" for the data. But the variation in data is is clearly noticeable. Hence i resorted to "Moving Averages" that i assumed will smooth-en the curves. Hence you will find two sheets, one with actual values and second with averaged values. Enter the "Fee" and see what is the expected no. of students.


Lastly, It was more then just laying out an excel sheet. If you are really interested in your approach for fee change, please consult a teacher who can advise you on which type of statistical model or technique will best represent your data so that you can make a forecast on its basis. Once this issue is decided, making an excel sheet is just matter of click!!! Let us know about it.


I hope i have explained myself!! :)


Thanks

Faseeh
 
SIRJB7 and Faseeh, Thanks a bunch. Your input has greatly helped. Even though you both approaches it form different angel, the result is the same.
 
@kay

Hi!

Welcome back whenever you want.

Regards!


@Faseeh

Hi!

After reading your post, I feel obliged to recognize that it's more technically sustained than mine, even from the very first glance at the data the probability of a reasonable correlation was nearly negative. Even though, while I stopped and stayed with that preliminary analysis while you keep on going with serious efforts trying to find out a way.

Very well done. And go on!

Regards!
 
@Faseeh


When I enter a fee I do not see any change in the corressponding chart. Is this normal or am i missing something?
 
You must log in or register to reply here.
Back
Top