Welcome back.  In today’s assignment, we’ll cook up a sales forecast.  Using the cooking metaphor, think of the sales forecast as the sauce we’re preparing to put on one of the main dishes.  In this case, the main dish will be the income statement.  But before we do the income statement, we need a forecast—also known as an “educated guess” or “informed estimate”—of what our sales will be over the first year.  Once we have that pattern set for the first year, we will increase it by a reasonable amount in the next two years to reflect growth of the business.  If there are any additional changes over those two years, we’ll make mention of it in the notes at the bottom of the spreadsheet.

So the ingredients for the sauce we’re cooking will be the projected sales for each month and the related cost of services to those sales.  We’ll go ahead and add another ingredient just to give it a little more zip.  That important ingredient is the gross profit—also known as the contribution margin.  It’s the amount of profit we have left after we’ve made a sale and covered the costs of providing that individual service.  Whatever is left will be used to contribute to covering the operating expenses of the business, the capital expenses, taxes, loan and interest payments, and any other expenses we incur in running the operation.  Gross profit is really important because it has to be large enough to cover all those expenses of running the operation day-to-day regardless of what our sales are.  If the gross profit is small, we have very little money to contribute to those expenses.  See the connection?  That’s why gross profit is also called the “contribution margin.” 

You’ll get a better idea of all these connections as we build these statements.  Okay, ready to go?  Open up Excel and follow the 20 steps below.  Once you’ve followed these 20 steps, you’ll be able to build the Big 3 Financial Statements everyone looks for: Income Statement, Balance Sheet, and Cash Flow Statements.  As a great Chinese sage once said, “Every great journey starts with a step.”  Let’s get started:

20 STEPS TO COOKING UP A SALES FORECAST

1. Open the file that contains your Notes and Assumptions worksheet. Right-click the new worksheet at the bottom, and choose Rename, and then type Sales Forecast Y1 over the existing name. This will now be the second worksheet in the financial statement file you started in the last assignment.

2. Build the titles in A1:A3 (meaning A1 to A3). In A1 type: Doggy Grooming on the Go; in A2: Sales Forecast; in A3:  For the Year Ended Dec. 31, 2020.

3. Now center the titles by selecting A1:C3 (click and drag). Choose Format Cells, select the Alignment tab, and then select “Center Across Selection” under Horizontal. 

4. We’re going to build the categories in Column A and our estimates in Column B, so in B4 type: January; in C4: February; in D4: March; until you’ve typed December in M5.

5. Now let’s add the categories in Column A. In A5 type: Units Sold (#); in A6: Mutt Packages; in A7: Prestige Packages; in A8: Westminster Packages; in A9: Total Units Sold (#); in A10: Sales ($); in A11: Mutt Packages; in A12: Prestige Packages; in A13: Westminster Package; in A14: Total Sales ($); in A15: Cost of Services ($); in A16: Mutt Packages; in A17: Prestige Packages; in A18: Westminster Package; in A19: Total Cost of Services ($); in A20: Gross Profit ($); in A21: Contribution Margin (%).

6. Let’s populate the Units Sold(#) categories because we will use these numbers as the basis for the Sales Forecast worksheet. That makes since, right?  After all, the units sold IS the ultimate Sales Forecast.  The rest of the numbers are created off these estimates, which Excel will do for us once we create the formulas for the remaining boxes in the worksheet.  And keep in mind, you can change these numbers later to see how different sales would affect the business.  But that is a strategy and financial analysis issue we’ll get to later.

So let’s say, we think the following numbers make sense as estimates for Doggy Grooming on the Go for its first year of operations:

For B6, type: 8; C6: 12; D6: 16; E6: 16; F6: 24; G6: 24; H6: 24; I6: 24; J6: 24; K6: 16; L6: 16; and M6: 16. 

For B7, type: 8; C7: 12; D7: 20; E7: 28; F7: 30; G7: 30; H7: 30; I7: 30; J7: 24; K7: 20; L7: 20; M7: 20.

For B8, type: 1; C8: 2; D8: 4; E8: 4; F8: 4; G8: 4; H8: 4; I8: 4; J8: 4; K8: 4; L8: 4; M8: 4.

These numbers reflect: 1) steady growth after the business opens, 2) increased sales during warmer months, and 3) a limited number of high-end customers in this market.

7. One of the handy functions of Excel is that it will do any necessary calculations in our worksheets for us.  All we have to do is provide the formula for each box needing a calculation.  You’ll notice that we’ve already entered the addition formula in the previous worksheet when we used the SUM calculation.  For this worksheet, we’ll use that function along with a couple of easy modifications. 

So for the A9 row of Total Units Sold, put the cursor in B9 and click the box.  Now go to the formula box in the ribbon at the top of the page.  Remember?  That’s the box next to the fx symbol.  Type: =SUM(B6:B8)   and hit the Enter key.

We could type formulas in all the boxes in that row, fortunately Excel has a shortcut to fill in those commands easier.  Simply click on the B9 box again and click Copy in the Edit command.  Put the cursor in the shaded area and drag it to the end of the row and click Paste in the Edit command.  Excel has now adjusted the calculation to work on all the other columns. 

8. For the A11 row of Mutt Packages, put the cursor in B11 and click the box. Now go to the formula box in the ribbon at the top of the page.  Type: =(B6* and then go to the Notes and Assumptions worksheet and click on B5, then hit the Enter key and put  )  at the end of the formula in the formula box and hit Enter.  The * is the multiplication symbol for Excel formulas.  This box captures the units of Mutt Packages sold that month multiplied by the $50 price for that service that you set in the Notes and Assumptions.  By taking this approach of integrating the Notes and Assumptions worksheet and the Sales Forecast worksheet, you can later change the price of a package and see how it affects the Sales Forecast.

So that we can duplicate that formula for all of the months in our worksheet, in the Sales Forecast worksheet, click on the B11 box again and click Copy in the Edit command.  Put the cursor in the shaded area and drag it to the end of the row and click Paste in the Edit command.  When you do this, you may see 0 appear in the new boxes.  To correct that figure, go to the formula box and change the last letter of the formula to B in each new box.  When you integrated the two worksheets to make the formula in B11, Excel made an assumption that you wanted to continue to go down the Notes and Assumptions worksheets to make the new formulas across the row in the Sales Forecast worksheet.  That’s not what you exactly wanted, but it’s an easy fix (even though it does get a little monotonous when you do it each time). 

Now do the same steps for Prestige Packages and Westminster Packages, except change the formulas with each service’s price.  So in B12 type: =(B7 and click on B6 in the Notes and Assumptions worksheet and hit Enter and then add ) to the formula in the formula box and hit Enter. For B13 in the Sales Forecast, type =(B8* and click on B7 in the Notes and Assumptions worksheet and hit Enter and then add ) to the formula in the formula box and hit Enter.  Duplicate those formulas for all of the months in our worksheet.  Click on the B12 box again and copy.  Put the cursor in the shaded area and drag it to the end of the row and paste.  And click on the B13 box again and copy.  Put the cursor in the shaded area and drag it to the end of the row and paste.  And make the corrections in the formulas in the new boxes by changing the last letter in each new formula to B.  That should fix the 0 problem.

Now we need to calculate the total sales of all the packages, so for the A14 row of Total Sales, put the cursor in B14 and click the box.  Now go to the formula box in the ribbon at the top of the page.  Type: =SUM(B11:B13)   and hit the Enter key.  Click on the B14 box again.  Put the cursor in the shaded area and drag it to the end of the row to replicate the formula for all the months. 

9. Now we need to calculate the Cost of Services.  For the A16 row of Mutt Packages, put the cursor in B16 and click the box.  Now go to the formula box in the ribbon at the top of the page.  Type: =(B6* and click on B13 in the Notes and Assumptions worksheet and hit Enter and then add ) to the formula in the formula box and hit Enter. This box captures the units of Mutt Packages sold that month multiplied by the cost of that service found in the Notes and Assumptions.  Again, you may decide you want to change the cost of services and you can see what that would like in the Sales Forecast later.

So that we can duplicate that formula for all of the months in our weeksheet, click on the B16 box again and copy.  Put the cursor in the shaded area and drag it to the end of the row and paste.  Fix the 0 problem by changing the last letter in the formula to B to adjust for the Excel assumption.

Now do the same steps for Prestige Packages and Westminster Packages, using the Notes and Assumptions worksheet to include the Cost of Services for each package.

Now we need to calculate the total cost of services of all the packages, so for the A1 row of Total Cost of Services, put the cursor in B19 and click the box.  Now go to the formula box in the ribbon at the top of the page.  Type: =SUM(B16:B18)   and hit the Enter key.  Click on the B19 box again and copy.  Put the cursor in the shaded area and drag it to the end of the row and paste to replicate the formula for all the months. 

10. Now we’ll calculate the Gross Profit, which is what we have left over from our sales after we cover our cost of providing those services. Gross Profit is not the “bottom line” you often hear about.  That illustrious title belongs to Net Profit, which we’ll calculate in the income statement.  So Gross Profit is what we “gross” after we cover our costs of services or goods sold, and Net Profit is what we “net” after EVERY cost and expense has been addressed.

Calculating Gross Profit is easy.  We simply subtract Total Cost of Services from the Total Sales.  So click on the B20 box and then move the cursor to the formula box.  In the formula box type: =SUM(B14-B9). Duplicate those formulas for all of the months in our weeksheet.  Click on the B20 box again and copy.  Put the cursor in the shaded area and drag it to the end of the row and paste.

11. Okay, we just have one calculation left. We’re going to convert the Gross Profit into a Contribution Margin.  This number shows us the percentage of profit from a sale we can contribute to the company’s expenses and taxes.  It’s another way of looking at the Gross Profit.  We do this by clicking in the B21 box.  Go the formula box and type: = B20/B14   

Note: (/ is the division sign in Excel).

12. To get a better idea how much money we might make on this business, let’s create a few more sections in the Sales Forecast.  For example, maybe you want to see how much you’ll make in hourly wages (after all, you can pay yourself a wage for each customer you service). In A23, type Wages($); A24: Mutt Packages; A25: Prestige Packages; A26: Westminster Packages; and in A27: Takehome Wages($). In A29, type: Annual Takehome($) and in A30: Annual Gross Profit($).  This will give you a better idea of how much you make from wages and how much you have to consider paying yourself from the annual gross profit.  Keep in mind though, the annual gross profit also has to be used to pay your operating expenses and capital expenditures.  And you may want that money to reinvest in the business for future growth. But you’ll consider those topics further after you’ve built an Income Statement, Balance Sheet, and Cash Flow Statements.  For now though, it’s nice to see what you have coming in after covering Cost of Services.  To calculate the amounts in each box of these new categories, you’ll use the same approach as you did from the sections above by integrating worksheets (the number of packages sold in the Sales Forecast by the wage amount in the Notes and Assumptions worksheet), and summarizing the rows and columns as before.

To calculate Annual Takehome($)in A29 and  Annual Gross Profit($) in A30, you’ll sum the amounts in  B27:M27 for Annual Takehome($) by typing in B29: =SUM(B27:M27) and hit Enter; and sum the amounts in B20:M20 for Annual Gross Profit($) by typing in B30: =SUM(B20:M20) and hit Enter.

13. To get a better idea how much time we spend grooming dogs in this business, let’s create a Labor Hours Per Month section. For example, maybe you want to see how much time you’re devoting to each package and the amount of labor allocated each month on grooming. In A32, type Labor Hours Per Month; A33: Mutt Package; A34: Prestige Package; A35: Westminster Package; and in A36: Total Labor Hours. This will give you a better idea of where you spend your time in the business.  Keep in mind you’ll also have hours you devote to the business beyond the grooming that you don’t receive money for.   

To calculate the amounts in each box of these new categories, make a formula (=) (the number of packages sold in the Sales Forecast for each package and multiplied by (*) .5 for the Mutt Package, 3 for the Prestige Package, and 5 for the Westminster package), and summarizing the rows and columns as before. So for example, in B33, you would type the formula  =B6*.5  In Row 36, you’ll SUM each month’s Total Labor Hours.  So for January that would be =SUM(B33:B35).

14. Let’s add one more column to the Sales Forecast. In N4, type Total and center it.  For rows 6, 7, 8, 9, 11, 12, 13, 14, 16, 17, 18, 19, 20, 24, 25, 26, 27, 33, 34, 35, and 36 let’s SUM up the totals of all the boxes in each row.  We can do this by typing in each box: =SUM(B6:M6) for N6, for example; =SUM(B7:M7) for N7; etc. for the rest of the rows listed above.  For N21, type the formula: =N20/N14  Let’s also shade this column, by clicking and dragging from N4:N36 and clicking on the FILL COLOR “bucket” on the command ribbon at the top of the page.  Pick a shade of grey to fill the column.  While we’re at it, let’s also do the same with the rest of Row 4.  Click and drag from A4:M4, and shade the row with the same color as you used in the N column. 

15. Now let’s further fine tune the worksheet’s appearance to make it look better.   Remember that we can improve the appearance of the worksheet by bolding, italicizing, SHADING, and underlining Labels, Categories, and Columns as needed by clicking on the command boxes on the ribbon at the top of the screen.  Let’s bold these column entries: A1:C3 (meaning A1 to C3; so click in A1 and drag to C3 for one big shaded area and bold); A5; A9:10; A14:A15; A19:A21; A23; A27, A29:A30; A32; and A36.  Bold the months at the top of each column too if you haven’t done so yet.  Let’s italicize: A9; A14; A19; A20; A21; A27; A29:A30; and A36). Let’s underline with the Bottom Border button on the top ribbon for: A3:N3; A4:N4; A8:N8; A13:N13; A18:N18; A26:N26; and A35:N35.

16. Now let’s add some Notes at the bottom of the spreadsheet to explain to the reader why the numbers are what they are. In A38 type: NOTES: and in A39 type: Note 1: Sales increased during warmer months to reflect the need to groom dogs who get outside more. In A40 type: Note 2:  Westminster dogs receive the same care yearround.  In A41 type: Note 3: Takehome wages was calculated by taking the wages made on each package and subtracting out 30% for taxes and benefits. In A42 type: Note 4: The actual number of units sold of each package does not match the estimated percentages sold in the Notes and Assumptions worksheet.  The estimated percentages provided a rough aid for estimating the units sold in the sales forecast. In A43 type: Note 5: Sales in the first two months of operation reflect market entry.  And in A44 type: Note 6:  Full capacity of roughly 50 hours per week per person is possible (give or take depending on when the last job is finished).   An additional groomer will be contracted to join the owner when needed.  The owner will work longer hours during the popular summer months.

17. Okay, we have a magnificent worksheet for the first year’s Sales Forecast. Now let’s do it for Years 2 and 3.  I know that sounds like a lot of work, but it’s really not.  You’ve already done the hard part.  All we have to do now is create two new worksheets and copy and paste the Sales Forecast you just did into the new worksheets.  So our first step is, as before, to open up and label a new worksheet.  We do this by:

 Right-click the new worksheet and choose Rename, and then type Sales Forecast Y2 over the existing name.

18. Go to the Sales Forecast Y1 worksheet and click in A1 and drop and drag A1 to N36. When this area is shaded, go to the Edit command and click Copy.  Now go back to the Sales Forecast Y2 worksheet, click in box A1, go to Edit again, and select Paste.  Now the entire worksheet will be replicated in the new worksheet with the same formulas to calculate the numbers for you.  You can now edit the Units Sold estimates for that year. 

You’ll notice we didn’t copy the Notes section from Sales Forecast Y1.  That’s because you’ll have different assumptions for Y2.  Maybe you expect an increase in sales for Y2, or maybe you hire a licensed employee to increase units sold.  Whatever the difference in units sold in Year 1 and Year 2 can be explained in a new notes section in Sales Forecast Y2.

19. Financial statements usually forecast three years out, so you can repeat Steps 17 and 18 above to create Sales Forecast Y3. And again, you would expect changes in your units sold as the business changes over time.  Maybe you buy another truck for the assistant to use fulltime in the operation and thus double your numbers.  Keep in mind you’ll also see numbers changing year to year later in your Income Statement, Balance Sheet, and Cash Flow Statements too as expenses and costs rise due to the expanded service.  Again, you would make notes about these changes at the bottom of the new worksheet.  Some businesspeople forecast beyond three years.  If so, you can easily keep repeating these steps.  Also, as you keep moving out further into the future, you might need to change some of the formulas in those later spreadsheets to reflect a change in prices or costs.  Perhaps you add a multiplication to the existing formula by 5% to reflect inflation, etc.  But for the most part, you should have enough tools now to project well into the near future.  And that’s a great start to developing your financial acumen.

20. And now the crucial last direction for you in building financial statements in Excel: SAVE YOUR WORK AS YOU GO! And save it in multiple databases if possible.  Email it to yourself.  Email it to a trusted friend.  You don’t want to waste time recreating something you already did. 

Once you have 3 years worth of Sales Forecasts you’ll be well on your way to creating the Big 3 Financial Statements that everyone looks for.  Good job!

 

AN ADDENDUM TO THIS EXECUTIVE HIGHLIGHT

The notational formatting instructions in this Executive Highlight for building worksheets in Excel were adapted from Financial Analysis with Excel: 8^th Edition 2016 by Timothy R. Mayes (Cengage Learning: Boston; 2018).  I highly recommend you buy this book if you are interested in building financial statements in Excel.  I read practically every book on Excel and Financial Statements I could find over a six-month period of time, and I concluded that Financial Analysis with Excel: 8^th Edition 2016 is by far the most thorough and yet understandable one on the market. It provides excellent guidance on how to build the Big 3 financial statements, as well as providing explanations and formulas for key financial ratios and metrics.

The one shortcoming of the book is that it doesn’t provide an example of how to generate and integrate the numbers needed to build the Income Statement, Balance Sheet, and Cash Flow Statement.  Thus, this Executive Highlight helps you input and examine key data needed to build those important statements.  And if you integrate the worksheets properly, you can then test different combinations of prices and costs (among other variables) to test how your Big 3 statements and ratios can be optimized.

Once you have a fully integrated spreadsheet with multiple worksheets, Mayes explains how you can also perform advanced calculations to have Excel optimize the numbers for you.  But I would save developing those skills for later down the road of your financial statements journey. I’m sure once you start building and integrating financial statements, you’ll discover three important benefits: 1) you’ll learn financial statements by actually building them; thus increasing your financial savvy, 2) you’ll be much better prepared to test the viability for an idea by testing different scenarios, and 3) you’ll be able to make more informed decisions with more professionalism in whatever business situation you find yourself in.

So in summary, buy Financial Analysis with Excel: 8^th Edition 2016 by Timothy R. Mayes, follow the recipe in this Executive Highlight, and then open the Mayes book and build your expertise as a financial wizard.  A new world is about to open up to you.  I wish you Good Luck on your journey!

About the Author of This Note

DR. MIKE GOLDSBY

Mike Goldsby is a professor, author, university executive, and consultant whose research, teaching, and talks focus on complex problem solving, design, innovation, and leadership. His personal mission is to inspire others to pursue their opportunities, to become more self-sufficient in solving problems, and to increase their personal productivity.