category as a percent of total sales. You can test your formulas by looking at the percentages for total sales, and they should say 100%. Every other category will usually represent some part of 100%. In other words, the product sales category number might be 50-60% of your total sales number, whereas your utilities expense might be only 1.5% of your total sales number (Figure 1, Columns H through K). This side-by-side comparison identifies the relationship be- tween various line items to your total sales number. It also facilitates identification of large year-over-year changes that may or may not be of concern. If you add additional columns of data from prior years (let’s say 2018 and 2017 - Figure 1, Columns B and C, as we did in the model), you will be able to more easily spot trends. So, now you can review an array of four years’ worth of numbers for each income statement category (Figure 1, Columns B, C, E and F — yes, we omitted Column D because it represents only a partial year of 10 months) and also the percentages of total sales represented by each. See the four-year array in Figure 1, Columns H through K. Obviously, if your business is new, you won’t have the historical data from prior years to guide you, but you will later on. Spreading Annual Numbers Among the 12 Months of the Year Once we have established our annual numbers for each year in Figure 1, we need to spread or allocate those annual numbers among each of the months in that year, as illustrated in Figures 2a and 2b. This is known as “seasonality” and is oſten associated with such retail categories as lawn and garden or pool/swim- ming supplies and trim-a-tree. Your business likely has some peaks and valleys, so sales volume for some months is more robust than for others. December is oſten slower. Because of space limitations, we are unable to present an array of monthly numbers for each of our income statement categories for each of our four years. Figure 2a is an abbre- viated look where only the income lines of our 2017 income statement are displayed, but the principle is the same for other income statement categories. Immediately following Figure 2a, you will also see the corresponding monthly percentages displayed in Figure 2b. You can view the digital edition of the magazine or the ALOA Tech Link app to see and use the entire model with all four years. However, if you do so and go directly into the various Figures 2a and 2b numbers in the spreadsheet model, you will see that on the leſt side, we have Figure 2a, which spreads the 2017 year- end numbers into 12 months of that year. These monthly num- bers come directly from your 2017 monthly historical income WWW.ALOA.ORG statements. On the right side is the corresponding Figure 2b, where our formulas calculate the distribution percentages for each category, for each month. Remember, these model num- bers and percentages are bogus and should be replaced by your actual numbers. If you insert your actual 2017 monthly num- bers into the spreadsheet model, the formulas will calculate the annual totals, and on the right side of the model, formulas will also calculate the monthly percentages for you. If you use the actual spreadsheet model, you will see in Figures 2a and 2b that below the 2017 annual numbers, the same template is presented for 2018, 2019 projected and 2020 budget years. Review Monthly Numbers (But Pay Even More Attention to Quarterly Data) Keep in mind that once you capture data from past years, you won’t need to do that again for those same years. Although your monthly seasonality for each year will be different (especially where you have large projects), this historical data can be very informa- tive. Hint: Capture the monthly numbers, but pay more attention to your quarterly numbers, since the somewhat erratic patterns of our monthly numbers tend to smooth out by the end of each quarter. Our model does not calculate quarterly numbers, but this can easily be done once you have the monthly numbers captured. Recapping the Primary Steps 1. Set up your model or use the one provided digitally, modi- fied to match your income statements. 2. Create a column of actual income statement YTD data for the first 10 months of the current year. 3. Create a second column and estimate where you are likely to end this year (2019 projected year-end). 4. Create a third column and generate your budget numbers for 2020. Revise as necessary. 5. Create additional columns and add actual annual income statement data from recent prior years. 6. Calculate the percentage of each line item, relative to total sales. 7. Capture the historic seasonality patterns of your business. Calculate the monthly percentages for each line item of your income statements. Next, let’s move on to look at how you can use your new bud- get to help you manage your business. Using Your Budget as a Management Tool If you have used a spreadsheet or some other tool to develop your budget, you will need to upload (import) that data into the FEBRUARY 2020 KEYNOTES 25
