"When budgeting, what are some techniques to determine, and to what degree, an expense item is dependent on sales, or COGS, or some other item?"
This question was asked at a recent webinar, now available on-demand:
Please add your thoughts about it below. Thanks!
There are two main sources of data here – historical data where you can see how the expense has varied in the past and discussion with the manager of that operation as to how the costs are driven. The manager’s answer should allow you to construct the historic data which then gives you confidence that you can predict the future. If you can’t construct the historic data, it might be that the manager doesn’t fully understand his/her cost structure in which case working it out together can help him/her do their job better and starts building that collaborative relationship you are looking for because you just showed you can add value.
From a purely
Additionally, Excel's LINEST function gives you the ability to do multiple regression. However, that's a more challenging task.
Note that I'm working on an article about setting up a correlation matrix, which I'll post in several weeks.
When taking a look at correlation as Charley describes, use your discussion with the manager to determine the difference in time periods that you should look at the formula.
For example, an increase in ad spend in March may impact sales in April. If you compared March advertising expenses with March sales, and so on, you might get a low R squared and miss out on the huge financial impact of the ad campaign.
