I’m not a finance guy, never have been, never will be. In fact I’ve always made sure to either have a finance type as a co-founder or early hire, or saving that, having an experienced part-time CFO on call. I’ve relied on these finance experts to help me develop the financial statements that investors insist upon – or at least they used to – income statement, balance sheet, and cash flow.
But this hasn’t stopped me for recommending that founders in the B2B market build spreadsheet models. My experience with business customers is that they, and their managers, are focused on the ROI of their purchases. What is the return on investment on your software or technology solution? My recommendation to founders is not to talk about that, nor to create a slide in their pitch deck. It is to put matters into the hands of their customers by providing them with an ROI spreadsheet model. That model needs to show the cost side: initial cost plus the cost of maintenance and/or upgrades, and any ancillary costs, such as needing to buy more powerful hardware or invest in training. Nailing down costs is not that hard.
What is more difficult is demonstrating the R of ROI – the return. There are basically two types of return: cost savings or increases in revenue – if you can show both you really have a winner. Suppose you have a new program that optimizes the supply chain for widget vendors. Generally speaking optimizing supply chains is done in three ways: speeding up the supply, which in turn speeds up production, which in turns, generates revenues and profits sooner. The second function to optimize is quality. Perhaps your software is able to detect counterfeit parts extremely well. Then to operationalize that, meaning express that benefit in terms of operations – what’s the cost of counterfeit parts to your customer today? What would they save by eliminating them with your product? The third variable is time. Today parts may come from half a dozen countries, many thousands of miles from your headquarters. Perhaps your program is based on operations research and can instruct shippers how to best batch, package, and ship their parts – saving them and you money.
Once you have the costs and benefits into your model you need to add the important variable of time. How long will it take your customer to break even on investing in your product, taking into account both the initial cost and any ongoing maintenance costs.?Generally somewhere between 12 and 18 months is desirable. Obviously the shorter the better, but if your customer’s breakeven on investment fits within their fiscal year they are going to look very good to their management. And helping them look good is your job!
But how do you take these general guidelines and turn them into a compelling Excel model? That’s where the Forbes article by Brett Whysel 8 Ways To Make More Powerful Excel Models will help you. Brett was a quantitative investment banker who learned how a spreadsheet model could help his clients make decisions. Your goal with the ROI spreadsheet model is to get your customer to make the decision to buy your product.
- People tend to find quantitative models and their numerical output inherently objective, compelling and trustworthy.
- Building a model can teach you, and your customer something. Building a model forces you to understand deeply the decision you are facing.
- Modeling differentiates you and is a competitive advantage. Few entrepreneurs can do it, and in my experience even those with the skill spend all their time polishing their PowerPoint slides. Excel is only dusted off for the good old standby, the financial statements.
Mr. Whysel lists some model building best practices that I would urge you to follow:
- Use design thinking. Most founders associate design thinking with developing the product, especially it’s front end. the UI/UX. But testing and iterating your Excel model from the user’s viewpoint is critical. I highly recommend you recruit some potential customers and observe them using your model. Where do they get stuck? Confused? Keep iterating until you have a model you can hand to a non-finance type like me to actually use hands-on, much like test driving a product prototype.
- Separate the inputs, calculations, and outputs. By separating the inputs you reduce the risk that a user will enter bad or mistaken data and blow up your model. Equally important, by separating these three elements finance types can more easily audit your model’s output and understand your logic – the rules that govern how inputs turn into outputs.
- Check for errors. By their nature, spreadsheets hide their functions behind numbers. Your a guesstimate of results should past the sniff test. Do they seem in the ballpark? Second you can use a calculator app or heaven forbid, a hand calculator, to check your calculations. Review the formulas (with Control-’), their precedents and dependents. Build in some error checking, such as making sure assets equal liabilities. Use conditional formatting to highlight extreme results. and don’t forget to spell check – Excel doesn’t have a spell checker! Having others on your team review your model can not only catch errors but help insure the model is easily used. Because you want your customer to use it, not just look at a print out.
- Format for clarity. Excel provides a wealth of formatting options. Make sure you use them consistently. And please, no more bottom lines like $2,49,782.32. I’ve yet to meet the investor or customer who cared about numbers to the right of the decimal point. Make sure that all entries in your columns are correctly aligned.
- Build minimally and flexibly. Here I’quote Mr. Whysel in full: Use named ranges so you (and others) can better understand your model in the future. Don’t use constants (numbers) in your formulas. What if these numbers change in the future? Put constants in a separate table that you can reference from the formula. Use the right functions. Absolute references can save a lot of time and space.
- Choose the right charts for the data. As with functions, Excel provides a plethora of chart and graph types. Avoid what design guru Edward Tufte calls chart junk.
- Be honest. If you are taking my advice and providing an ROI model to your customer, don’t hide your assumptions. In fact, I advise founders to create a column to the far right of the model to list all assumptions. Finally, use sensitivity analysis to show how important the major assumptions in your model are.
- And here’s one for the spreadsheet jocks amongst you. Use macros to save time and reduce errors. Make sure you know what you are doing! I advise avoiding macros unless you have the experience and expertise to use these powerful features safely. You don’t want to have to provide tech support for your customer’s ROI model that you built for them!
Again I have to quote Mr. Whysel in full in his conclusion, as it’s so eloquent and is such a good fit for founders doing pitches. And of course he recommends the same starting point for building your Excel model as I do for pitches: In both writing and modeling, you begin with your end in mind. And as I’ve recommended to founders, a model is just like a pitch deck in that it has to tell a story.
Creating a spreadsheet model is a lot like telling a story. You are communicating a certain perspective on the world, a preference for making decisions a certain way and persuading people that a particular choice is best because your model is trustworthy. In both writing and modeling, you begin with your end in mind. And you end by making sure you’ve achieved that end effectively, honestly, elegantly and with respect for the people who will benefit from your work. Then, you have given your model its voice.