Lean Business: The Very Model of a Modern Spreadsheet

“Model” is a verb. It traditionally means to fashion or shapes a three-dimensional out of a material such as clay or wax. In business, the verb usually refers to tools created to make projections of sales or profits. Still, it can also predict anything from website traffic to manpower requirements to employee healthcare 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 daunting and 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.

Like large enterprises, small businesses need tools to help them create sales and revenue projections, analyze customer data, maintain product lists, and graphically visualize 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 many larger companies use.

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

1. Project your sales. Many small businesses need a reliable, accurate tool to forecast sales and demand for their product or service. Restaurants use this 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 inform our decisions on capital expenditures and marketing budgets. For most businesses, a simple starting point is to divide your offerings into categories or groups, input last year’s (or last quarter’s) sales for each category, and then 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 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.

Want a free brand review?

Answer 5 short questions and we will send a custom report with actionable insights and specific actions you can take to build a stronger brand.

We just emailed the info to you.

Email Address

2. Analyze A/B testing results. If you run a web-based business, you have probably conducted A/B testing and may use tools like 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. One of the critical aspects of 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 ensure the test data matches your baseline. For instance, if you are trying to improve conversions, you will need to clearly understand the conversion rates as they currently exist 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 monthly; 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, comprises 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 the required ingredients to produce it cost you 50¢. The simple question is, at a unit profit of $1.50, how many brownies will you have to sell to cover your fixed costs? Download this template, input your 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 salespeople 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 seeing who has the highest dollar figure. This template uses Pivot Tables to let you drill down and compare your salespeople 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.