In this article we will explore quick formula edits that will facilitate toggling between fixed, variable and semi-variable expense line items. Creating a projection that allows you to select how expenses should be categorized between these three expense types on a line-by-line basis will make the overall model-building process more efficient. The alternative, of modeling each item individually, can become cumbersome.
The first video will demonstrate how to use an =IF() function to build a formula that allows the user toggle between fixed and variable costs. But before viewing the video two concise definitions may help.
Fixed vs. Variable Costs
Fixed Cost: A fixed cost does not fluctuate with a company’s revenue or production. Common examples include leases (e.g. rent expense) and executive salaries. In an Excel model, these expenses will typically either be straight-lined or will grow with inflation.
Variable Cost: A variable cost fluctuates with a company’s production. A common example would be the cost of materials required to manufacture a particular good. For every unit sold the company will require the same amount of material. If unit data is available these costs can be projected on a unit by unit basis (number of units produced x cost per unit). It is also common to see these line items projected based on a historical percentage of revenue.
Semi-Variable Cost: In addition to fixed and variable costs, companies can have semi-variable costs, which include components of both cost categories. A common example is a manufacturer’s cost of electricity. The amount of electricity required to keep the lights on in a climate-controlled environment may be pretty consistent, whereas the amount of electricity required to operate manufacturing-related machinery would fluctuate with units produced.
The degree to which a semi-variable cost is either fixed or variable can at times be difficult to ascertain by looking at recent historical data. As a proxy, percentages can be applied to a fixed component calculation and a variable component calculation.
The video that follows demonstrates how the formula and associated inputs work together.
Whenever possible, I think it is better to distinguish between fixed and variable. If a line item doesn’t fit perfectly in either category, then it may make sense to explore whether or not the line item has supporting detail that could be used for this purpose. When that detail is not available the formula presented in the video can serve as a helpful estimate.