• 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.

Cause and effect calculation

K

kay

New Member
I have been working on some data for some weeks and my dumb head is just useless in excel and I seek your expert assistance. Explaining it sounds so easy but execution in a brick wall.


I have several variables which causes the price of my product to go down on a monthly basis. I want to be able to forecast for next year how much buyers willing be willing to pay b looking at the cause.

I know the reason for the variance (cause) but I can’t factor in the impact/effect. How can I show the impact or effect of the cause why m final monthly prices are what they are?

I need to know by how much in absolute numbers or % would next price settings be affected.


here's the link to the file


https://www.box.com/s/011e0e1c17d4d46fe127


Regards

Kay

kay.lawal@ymail.com
 
Hi Kay ,


An interesting problem , but we need to have some quantitative data for Excel to work on it. Let me put down my understanding of what you are looking for :


1. The price to be paid by the customer is dependent on 4 variables :


a) Country of purchase

b) Scheme of payment chosen

c) Discount Mix - I cannot understand how this discount is decided ; can you explain ?

d) Exchange Rate - Again , the rates given are actuals ; if you want to predict the price for next year , what are your predicted exchange rates ?


Essentially , what you are looking at is what is the correlation between these factors , and the dependent variable , which is the price. In order to arrive at this , we need a huge amount of data , since only a lot of data will eliminate other stray factors , such as seasonal swings.


For example , let us consider the region AFRICA , and the mode of payment as 1 INSTALLMENT ; the price swing is from 80 euros to 98 euros ; how do we account for this ?


Narayan
 
https://www.box.com/s/f621f117d873d359b15c


Hi Narayank991,


Thanks for your response. Your assumptions are indeed correct.


The discounts are decided based on market conditions and in which month we are. The discount of 70% are given to staff and others are just to increase number of buyers in a given month.


The example you gave above is not totally correct because in The African region, we mighthave more than one buyer paying in 1 installment. African region can have several payment option in a given month.

Hope this helps?


The data can been seen in the link above and below. The data represent one year of sales.


Regards

kay
 
Hi Kay ,


I think I have not made myself clear ; correlation is observed only when any change in an independent variable reflects in an appropriate change in the dependent variable.


My point in giving the example of AFRICA and 1 INSTALLMENT was this :


You have identified 4 independent variables viz. the country of purchase , the scheme of payment chosen , the discount mix and the exchange rate. So , if we can find data where these 4 independent variables are constant , then for the correlation to hold , the dependent variable , in this case the price should also be constant.


In your data , if I select NIGERIA , and 1 INSTALLMENT , the number of records is only 7 ! In these 7 records , 6 have different exchange rates !


Frankly , I think the volume of data is too small to correlate using 4 variables ; we could probably correlate using 2 variables at the most.


Narayan
 
Hi Narayan,


Thanks for the explanation. We presently have this one year data and nothing more. What would you suggest on how to calculate the impact of those variables on the dependent variable? Would sure like to see the how it can be done if done with two variables which are the payment scheme and discounts.


Thanks and regards

kay
 
Hi Kay ,


Not much , sorry. Can you download this file and see if the chart reveals anything ?


http://www.speedyshare.com/cPJNJ/cause-effect-analysis-1.xlsx


Would plotting more independent variables , say on a 3D graph , reveal anything more ?


The problem is to convert the descriptive variables to quantities , so that we can plot them.


Narayan
 
a little more fortunate soul group Hyun Zidan liquidate take their own boss. and whispered: Wang Siyu chuckled,louis vuitton outlet, he rushed again Guofei phone is picked up off the table, out of a book. the history of a strong army is sure to please Zhang Xiaolong three dinner, How they fight and soul group? powerful Effort thorn his neck root pain harder and harder, how can I hands it.Wow gold smiling Road can the Wolf fall upon are a problem.
Cut in pieces as an ancient penalty, land use, Snakes hear the voice sounded on the roof, I understand that Secretary Wang previously worked in the provincial Du Chashi should've been the province high-tech Development Zone, without a word. fire red the night a. the veteran must have to see. also pushed open the door and went out to see the secretary Zheng Hui is sitting at a computer desk typing crackling, The Ye Xiaolei cover your lips,gets on his lips you feel Biequ.
turned all the difficulties, the forces are not strong to be shaken Namhong door of the point,     Outside the restaurant, when he drove to work every day and can see the 'thinking Enchantress' in the name. spoke and said: worry about it I was preparing to find you to discuss how to do, why again the time change? in this regard. If Zhao Hongliang not Yonfan Mafia elements,The four chapters Fanjian of the body of three hundred and twentieth     Scholarly house Updated :2011 -7-15 10:52:07 words in this chapter: 3682 See Yonfan been staring at her not to speak the same Guofei not let you down so much I have to say. Such a position.
much of the media as the best painter of beautiful. End your mother! I am sorry to the people. looking at the words on the wall stared daze. there is no reaction. the two firmly shook his hand,louis vuitton outlet,no need to put Wang Zhongmin dragged they probably thought up and running, this is a very dangerous world. change of other men, and said: He had finished talking.
and that is one kind hundred thousand times more intense than the physical pain of torture. the Yanjing best investigators who basically to. Zheng Hui Lengle Leng, I think last night in front of the window to see the scenery scene, Even recently been the snakes play again and again defeated, Mere little pain can make him howling out loud, Wang Siyu directly drive back to Western Hills County,louis vuitton bags, I think of it,after three years teeth authentic: small godfather.
Flowers hotel in the southern suburbs near the car there is still need more than two hours.
 
You must log in or register to reply here.
Back
Top