• 097 03/26/2020

As a continuation of the post titled Modeling a Crisis, the following template has been made available: Budget Revision Template. This template was designed to help small to medium-sized business owners adjust their company's budget for an unexpected change in revenue.

The videos available below provide a walk-through of the income statment and the cash flow worksheet in an effort to make it easier to comprehend. A few text explanations of the worksheets, and some links with additional instruction can be found beneath the videos as well.

Budget Revision Worksheet

Revenue: Input historical revenue as well as the original forecast for revenue. The values for the projected period can be revised with percentages in each corresponding period. After the new revised revenue line item in the model has been calculated, discounts can be applied to arrive at net revenue.

Cost Structure: Each line item under COGS and SG&A can be toggled to reflect either a fixed, variable or semi-variable cost structure. For a better understanding of the formulas on the tab titled Budget Revision please see the link that follows: Forumla's for Fixed, Variable and Semi-Variable Cost Structure. The only line item that is not projected in this manner is payroll under SG&A. This has its own tab to allow for some additional flexibility.

Cash Flows Worksheet

This tab starts with Net Income from the Budget Revision worksheet and makes adjustments to arrive at an estimate for cash flow for each projected period. The template requires some balance sheet information for this calculation. As it relates to working capital, three accounts are included: Accounts Recievable, Inventory and Accounts Payable. Otherwise a debt payments projection is required. The remaining line items pull from the Budget Revision tab.

If you are not familiar with a three-statement model, the two videos that follow will help with some of the concepts discussed in the second video.

• 096 03/24/2020

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

• 095 03/23/2020

Modeling a Crisis (Link to Template)

When crisis strikes two areas require immediate analysis. The company’s ability to control its cost structure, and the company’s access to liquidity. Once you have an understanding of these two variables you can begin to explore the degree to which the business can tolerate a decline in revenue. The time available to perform this exercise is related to the company’s liquidity.

Liquidity:
Regardless of the financial discipline exercised leading up to the crisis, every potential source of liquidity should be evaluated as part of this process. For most small businesses the immediate areas to explore would be as follows:
1. Undrawn capacity under the company’s letter of credit.
2. Relief from lenders.
3. Relief from landlords.

Working capital accounts and cash management practices should also be evaluated. A straightforward example would include an analysis of the company’s accounts receivable balance by account. If customers are likely to be impacted by the crisis as well, they may delay or refuse payment to maintain their own cash balances.

It also helps to be mindful of the fact that aggregate cash across the company’s bank accounts is not likely to accurately represent cash available. Beyond the deposits in transit, a company’s unique cash management practices should be scrutinized. I once overheard a private equity colleague use the term “drawer checks” to refer to the balance of cash represented by checks that had been signed but not sent. It has been my experience working with junior team members that the mechanical and automatic response spreadsheets have to inputs is often assumed to mirror human interaction in a business. But nothing could be further from the truth. It is entirely possible to find a drawer full of checks representing balances due with no corresponding impact to cash reflected.

At the conclusion of this process hopefully the outcome is that available liquidity will cover several months of expenses, and that combined with current revenue the business can survive for the next twelve months. The next step is to thoroughly evaluate each expense by line item because this time horizon will shrink as revenue continues to decline.

Cost Structure:
In many financial models revenues and expenses decline simultaneously in the same period, but the reality is that it is very difficult to cut expenses gradually as revenues decline. Imagine working in an environment where colleagues are let go every week. Employee confidence would evaporate, and morale would drop. The desire to measure the impact of declining revenues with an unknown time horizon before acting can further extend the lag between declining revenue and expense reduction. In discussions with managers and from my own personal experience, the desired approach when cuts are necessary is to make one difficult decision and then announce that it was the only cut required to right-size the business for the anticipated decline (and hope you are right).

A sad reality is that in the event of a macro crisis such as this one, an employer may have more flexibility with these decisions because employees have fewer options available to them and are less likely to leave. That said, I would argue that the effort should be focused on right-sizing the businesses in the fewest number of announced reductions possible. The process should follow a strategic plan developed by thoroughly evaluating each line item on your income statement in as much detail as possible. Anything that the business can do without should be marked, and everything within the cost structure should be identified as either fixed or variable (does it fluctuate with revenue or not?).

[Note: A big part of this exercise is identifying employees that can manage multiple roles. It is very likely that you will not have the luxury of specialization across roles if the business contracts significantly.]

Beyond the income statement, the company’s capital expenditures should be evaluated. To the degree that any capital expenditures can be postponed, they most likely should be. If not, can maintenance be substituted for any upgrades that would otherwise be required? Through whatever means possible, the purchase of new equipment should most likely be delayed.

As it relates to personnel, the senior management is often overlooked in this process. At a private equity controlled entity it is common practice to ask the management team to take a pay cut when the company’s performance is suffering. I would encourage founder-owned businesses to take a similar approach. My advice to CEOs would be to meet individually with the team members that the company could not do without and express the need for savings and the desire to temporarily reduce executive salaries for the duration of the crisis. If these individuals commit to the plan, then it can be circulated to the team at large.

In summary, you should now have the following items identified:
1. The pay cut senior management is willing to accept until the business recovers.
2. Every current expense that the company could do without.
3. Whether or not an expense is fixed or variable.
4. Required capital expenditures (if any).
How Much of a Decline Can the Business Tolerate:
As it relates to modeling a decline in revenue there should be two objectives at a minimum: (1) estimating what the senior management team anticipates, and (2) exploring what the business can tolerate. To begin, input a year of historical revenue data (by segment if available) on a monthly basis and then straight-line revenue across the top of your model for a projected period of 12 months. The next step is to include all expenses in as much detail as possible on a monthly basis in the corresponding periods under revenue. Expenses previously marked as “variable” should be projected as a percentage of revenue, and expenses previously marked as “fixed” should be straight-lined across the projected period. (Video: Fixed, Variable & Semi-Variable Cost Structure.)

Once you have this construct in place you can reduce revenue to determine the point at which the business runs out of cash. When you find this limit eliminate expense line items that the company can do without, and then repeat the process. After a few iterations of this process a hypothetical new lean cost structure and an approximation of the business’s breakeven revenue run rate should present themselves.

This information should help inform the cuts that are required and the timing of these cuts. It will also provide a floor to be aware of. If the senior management believes that actual revenue could drop to the breakeven revenue run rate in the model, it would be prudent to explore potential capital partners immediately. If the business has previously raised capital this might be a simple exercise, but if this is a new process it should be explored immediately.

Concluding Thoughts:
I really want to emphasize two items. The first is that this cannot be an outsourced exercise. Identifying what is critical to the business, which employees are indispensable and which employees can juggle multiple responsibilities will be difficult (or impossible) for an outsider. The knowledge of the executive team will vastly outweigh a stranger’s spreadsheet skills set no matter how much of an Excel nerd they are (that said, working with an Excel nerd is highly encouraged).

Second: If the management team believes that revenue might approach the breakeven revenue run rate at any point in the future, then someone should be put in charge of interfacing with potential capital partners immediately. An entity that has not previously raised capital is unlikely to find a capital partner than can transact in under 30 days and most prefer 90 days. Don’t be intimidated by the process and try to create dialogue with multiple parties.

This was a difficult process to summarize in one article. I reached out to approximately a dozen friends that are successful professionals and feedback varied between two groups in particular. Private equity and investment professionals responded with suggestions for more detail, and entrepreneurs largely responded suggesting that it be reduced or broken down into segments. After a few rounds of edits I thought it would be best to get it out there and initiate dialogue. I hope readers find it helpful.

If you are reading this and have additional insights, thoughts or questions, please feel free to respond via the contact page

Models are:

A) really boring
B) pretty sweet
C) super important
D) somewhat easy
E) kind of hard
F) fun
G) all of the above

*Answers a, b, c, d, e, f and g are all correct.