Updated: Sep 15, 2020
You have tried to create a spreadsheet to manage your business, but it has been difficult, or you aren't sure how to go about it. You want something that is easy to use, easy to modify, and that can scale as your business grows. How do you do that?
Building a good spreadsheet tool does take a bit of work but if you follow a few basic guidelines you will save yourself a lot of time and frustration later. Essentially, you need to start at the end and keep the different sections separate.
Rule 1: Start at the end by answering some basic questions. Why do you need this report? How will you use this report? What are the sources of data for the report? What should the report look like?
Rule 2: A good spreadsheet report has four basic sections. They are the data set, the variables, the calculations and the report.
Rule 3: Each section must be contained in its own tab/worksheet in your file. If you try to put two sections on one tab then it will be more difficult to make changes later.
Rule 4: Enter data in one place, one time. You do not want your spreadsheet to allow a user to enter the same value - number of units, for example - in multiple locations. If you do, then the spreadsheet will always take a lot of time to update because you must ensure that all cells are updated with the same value every time. Otherwise your report will produce faulty information, and you may be unable to reconcile critical data.
Rule 5: There is a flow to the data - arrange the tabs of your file accordingly.
5(a) - The Original Data Set is the first tab. Don't use the original data set for any calculations. Instead, make a copy of the original data set and only use the copy for calculations and reporting. If you do this, then you will always have a clean original data set for reference or for use if you make an error and have to start over.
5(b) - The Variables tab is next. Variables are the factors you use when doing calculations on the data set to produce your report - growth rate, percentage of sales, just to give two examples.
5(c) - Next is the Calculations tab. This is where you do all calculations, where you have the formulas which apply your variables to the data set.
5(d) - Finally, the Reports tab. On this worksheet you place the results from the formulas you created on the Calculations tab.
So, spend a little extra time up front to think through the purpose of your report, then follow the guidelines above. If you do, you will spare yourself a lot of unnecessary wasted time and frustration later on.