more-arw search

Q&A Forum

My Excel Budgeting Process For COGs Is Squishy

 

"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:

"Budgeting in Excel"

Please add your thoughts about it below. Thanks!

Answers

Simon Turner
Title: Vice President, Finance
Company: International Finance and Operations Exe..
LinkedIn Profile
(Vice President, Finance, International Finance and Operations Executive) |

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.

Topic Expert
Charley Kyd
Title: Founder
Company: ExcelUser
(Founder, ExcelUser) |

From a purely Excel perspective, the CORREL function can give you the correlation coefficient for two data series. So if you have historical values for sales, cogs, and other items, you can learn how each expense is correlated with those potential drivers.

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.

Topic Expert
Jaime Campbell
Title: Chief Financial Officer
Company: Tier One Services, LLC
(Chief Financial Officer, Tier One Services, LLC) |

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.

2605 views
Topics
Products and Companies

Get Free Membership

By signing up, you will receive emails from Proformative regarding Proformative programs, events, community news and activity. You can withdraw your consent at any time. Contact Us.

Business Exchange

Browse the Business Exchange to find information, resources and peer reviews to help you select the right solution for your business.

Learn more

Contribute to Community

If you’re interested in learning more about contributing to your Proformative community, we have many ways for you to get involved. Please email content@proformative.com to learn more about becoming a speaker or contributing to the blogs/Q&A Forum.