Who doesn’t love a good spreadsheet? Beautiful and clean formats with elegant formulas and cell references, they are the epitome of grace in business. They not only look great but they can help business owners test assumptions and better understand the implications of different business decisions.
Okay, maybe I’m alone in this. But how do you test the feasibility of a new venture or plan for growth? What happens to your profitability if your sales projections are 10% less than anticipated? How much of a loan can you afford to buy a new truck? With a well-constructed spreadsheet, you can do all that!
My colleagues often tease me that I talk wax on about “dynamic spreadsheets.” Too often I see spreadsheets that do not fully benefit from the power of Excel; they are used only use it as a way to list numbers or words, with little regard for formatting much less using formulas. But when I work with clients to help plan out a growth strategy or apply for a loan, it’s not just a neurotic tendency towards tidy spreadsheets. It’s about creating a dynamic tool that can help entrepreneurs understand the different levers in their business (and lenders can easily evaluate the investment opportunity).
So what do I mean by “dynamic” and “levers”?
dy·nam·ic
/dīˈnamik/
Adjective: (of a process or system) characterized by constant change, activity, or progress.
“a dynamic economy”
lev·er
/ˈlevər,ˈlēvər/
Noun: a rigid bar resting on a pivot, used to help move a heavy or firmly fixed load with one end when pressure is applied to the other.
As a business-owner thinks about growth, they consider dozens of different variables. A farmer thinks about how many CSA shares or whole chickens she can sell; she thinks about different price points from $300 – $500 per share or $2 – $4 per pound. What happens if chicken feed increases by 10 cents a pound? A food producer thinks about selling 8 oz jars vs 10 oz jars. These are just a few examples of the many, many variables.
These variables can be adjusted, and with each adjustment comes a different outcome. If you charge $500 for your CSA, then maybe you attract fewer customers. If you sell 10 oz. jars instead of 8 oz. jars then the cost of each jar changes. Each change affects the potential for profits, and becomes a pivot point, or lever, that can be tweaked and adjusted to test the feasibility of your plan.
To understand the different levers in your business, you need a dynamic spreadsheet that allows you to adjust the variables easily and see the impact on profitability (or cash flow or whatever it is that you want to measure). A dynamic spreadsheet, allows you to answer the question, “What if…” What if sales are lower than anticipated, what if expenses are higher than anticipated? What if it takes longer to ramp up my business than I expect?
Here’s a snapshot of a farm business which is trying to understand what it will take to operate profitably (because of space constraints, I hid many of the expense assumptions).
In this example, the farm is not profitable until Year 4 when they have 13 acres in production. This gives the farmers a starting point to test their assumptions and other “what ifs.” What if they can increase production efficiencies? What if they can decrease production costs? What if they can ramp up production to 13 acres more quickly? By setting up the “net profit” as the decision/output variable, with that number calculated as a function of the input variables, the farmer can easily test assumptions.
This is a simplistic example, and spreadsheets can become far more elaborate as you layer in multiple sales channels, expenses, loans and capital improvements.
As you think about the layout of your spreadsheet, consider two components:
- What are the input variables you want to test?
- What is the output, or decision variable?
The inputs can be sales revenue, expenses, loan amount or terms on a loan. The output/decision variable is the net income or ending cash balance.
Formulas and Cell References
The key to creating a dynamic spreadsheet is using formulas and cell references. If you want to calculate net income, the formula (or math equation) is Total Sales Revenue – Total Expenses. The Total Sales Revenue is the sum of all the individual items. Total Expenses is the sum of all the individual expenses.
To best utilize excel, you want to start thinking first about formulas. For example, after you list out all the expenses in excel, use the formula =SUM() to add up all the numbers. Similarly, use a formula to add up all the sales revenue. When you go to calculate net income, use another formula that references the cell with the total revenue and the cell that references the total expenses. Then, if you decide to change one item in the list of expenses, the net income will automatically recalculate.
Here are 9 Tips for building a dynamic spreadsheet.
- The more cell references and formulas you use, the better able you can test assumptions.
- All output/decisions cells should be calculated as a formula based on the input variable cells.
- List all variables together. Format the variable cells differently than other cells so you know which cells you can edit. I like to shade them blue.
- Clearly notate the output/decision cell.
- List expenses in a single column
- Wherever possible, note variable expenses as a percentage of revenue
- The time variable (months/years) should be horizontal across rows. Expense variables should be listed in columns
- Link all output cells to variable input cells.
- Adjust your variables to see the impact on the decision.
Need help testing the feasibility of your growth strategies? Give us a call! We’re here to help.