Lean Business: The Very Model of a Modern Spreadsheet

“Model” is a verb. It traditionally means to fashion or shape an a three-dimensional out of a material such as clay or wax. In business the verb usually refers to tools that are created to make projections of sales or profits, but can also be used to predict anything from website traffic to manpower requirements to employee health care costs. Since the days of the abacus and slide rule, the tool most often used for this purpose is the (not so) humble spreadsheet.

For many, “spreadsheet modeling” can be a daunting phrase and can sometimes cause otherwise intrepid entrepreneurs to run screaming in terror. The term brings to mind hugely complex programming tasks, massive computer files, and nerdy MBA students huddled in dark rooms staring at a computer monitor. In reality, spreadsheet modeling can be as simple as creating a sales sheet for your product line or as elaborate as a dynamic pricing tool with thousands of lines of complex code. If you use a spreadsheet program such as Microsoft Excel, chances are that you are already involved in modeling, you just might be calling it something else.

Just like large enterprises, small businesses need tools to help them create sales and revenue projections, analyze customer data, maintain product lists, and graphically visualize all of this critical information. Every day small business managers use spreadsheets in ways simple and intricate to help run their business and they do it with applications designed specifically for their unique circumstances: these spreadsheets represent a cost-effective and easy-to-use and learn alternative to the powerful and sophisticated enterprise software used by many larger companies.

Below I discuss four commonly used spreadsheet models (and links to downloadable templates where available) that many small businesses employ on a day-to-day basis. Of course there are an infinite number of spreadsheet models that can be created, each specific to a business or a problem the user is attempting to solve, but master these four and you will be on your way to mastering modeling.

1. Project your sales. Lots of small businesses need a reliable, accurate tool to forecast sales and demand for their product or service. Restaurants use this kind of data when ordering ingredients for their dishes and scheduling staff. Manufacturers use sales projections for inventory and supply-chain management. And at crowdSPRING we use sales projections for tasks as diverse as planning customer service schedules to informing our decisions on capital expenditures and marketing budgets. For most businesses a simple starting point is to divide your offerings into categories or groups, to input last year’s (or last quarter’s) sales for each category and then to apply assumptions such as your best guess at a percentage increase for each category. For instance if your “Widget #1” had total sales of $10,000 last year and you believe that sales will increase by 5% this year, you can input that assumption, set up a quick multiplication formula and arrive at your projected sales of $10,500! Easy, right? Microsoft provides a nice template to get you started, here.

2. Analyze A/B testing results. If you run a web-based business you have probably conducted A/B testing and may be using tools such as Google Optimizer to help with the process. We have written a great deal about the importance of developing and testing theories to improve the performance of your pages and one of the critical aspects to this is collecting, organizing, and analyzing the data that testing creates. You’ll need to start by collecting your baseline data on the page or pages you are testing and make sure that the test data is designed to match your baseline. For instance if you are trying to improve conversions, you will need to have a clear understanding of the conversion rates as they currently exist in order to determine whether the test is effective. A nice tool can be downloaded here to assist your analysis.

3. Determine break-even volume. This is as basic as it gets. Every business has certain fixed costs that remain the same from month to month; think rent for office space, salaries for full-time employees, and health care costs. And every unit you sell, whether an hour of services or Widget #3 is made up of the selling price and the variable costs associated directly with that unit. For instance if you are selling brownies, each delicious square of your product may sell for $2, but required ingredients to produce it that cost you 50¢. The simple question is at a unit profit of $1.50, how many brownies will you have to sell in order to cover your fixed costs? Download this here template, input your own specifics and out will pop the answer: we need to sell 1,213 brownies per month to cover our costs of $1,820!

4. Reward top performers. Competition is good right? It can lead to great innovation, efficient processes, and (especially for sales people whose compensation is tied to performance) increased revenue for your company. But determining which of your sales folk is the most effective can be more complicated than simply seeing who has the highest dollar figure. This template uses Pivot Tables to let you drill down and compare your sale people based on any number of criteria, including their region, the number of days or hours they worked, the customers on their list, and the average size of their orders.

Drawing: Leonardo da Vinci’s il Condottiero, 1480. (Note: The word Freelance is derived from “Free Lance” a term for a mercenary. Condottiero meant “leader of mercenaries” in Italy in the Renaissance.)