“Most people find building Excel spreadsheets intimidating, but it’s really not that hard if you have easy step-by-step instructions to get through it.  Unfortunately, most training guides and videos don’t find that happy medium between assuming you already know how to do some Excel programming or bombarding you with too much information.  What’s needed is an instructional guide that walks you through how to build a spreadsheet step-by-step in an easy-to-read format.  You know, kind of like a cookbook does.  Anyone can cook if they follow the directions because recipes are written with only what you really need to know to get the job done.  With just a little dedication and persistence, recipes help you prepare meals that will impress your guests…and most importantly yourself too!  You add a pinch of this and a dash of that, simmer, let set for ten minutes, and voila!, you have a gastronomical masterpiece.  If it works well with cooking…which is like rock science for most people…maybe it will work for other things too.  Let’s try that approach with building financial statements.

Welcome to your Financial Statements Recipe.  In the following Executive Highlight, I’ll help you build financial statements in Excel with no prior knowledge.  Simply turn on the oven to 350 degrees…I mean open up Excel…and follow the steps below.  Once you’ve learned how to build the example in this recipe, you’ll be able to do the same when you want to dish up other worksheets for financial statements.  So let’s begin with building a Notes and Assumptions worksheet.  This worksheet will communicate the assumptions and reasoning behind the numbers that follow in the income statement, balance sheet, and cash flow statement.  It lets the person examining your business forecasts to understand how you came up with the numbers you did.  Once you build this worksheet you’ll be well on your way to creating others. That makes sense, right? 

So let’s cook up a little appetizer for your financial statements.  This worksheet will be shorter than most financial statements, but the numbers on it set the stage for everything else to come.  Okay, ready to get started?  Let’s cook!

STEP-BY-STEP GUIDE TO COOKING UP A FINANCIAL STATEMENT

1. Right-click “Sheet 1,” choose Rename, and then type Notes and Assumptions over the existing name.

2. Build the titles in A1:A3 (meaning A1 to A3). In A1 type: Doggy Grooming on the Go; in A2: Notes and Assumptions; 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 Horizonal

4. Assume you have three standard grooming options for your average clients. We first need to set the prices for these options.  In A4 type: Prices($) (and click Align Left on the ribbon at the top of the page); in A5: Mutt Package; in A6: Pedigree Package; in A7: Westminster Package.

5. Now we need to estimate the Cost of Services for each package. If we were selling products, we would call it the Cost of Goods Sold.  In A8 type: Cost of Services($); in A9 type: Mutt Package; in A10: Gas and Mileage; in A11: Supplies; in A12: Labor (.5 hours); in A13: Mutt Package COS; in A14: Pedigree Package; in A15: Gas and Mileage; in A16: Supplies; in A17: Labor (3 hours); in A18: Professional Look; in A19: Pedigree COS; in A20: Westminster Package; in A21: Gas and Mileage; in A22: Supplies; in A23: Labor (5 hours); in A24: Professional Look; in A25: Westminster COS.

6. Now we estimate the Percentages we think we will sell of each package. In A26 type: Original % Estimate of Packages Sold; in 27: Mutt Package %; in A28: Pedigree %; in A29: Westminster %.

7. We can add any other assumptions in this fashion as well if needed.

8. Now let’s add a Notes section. Notes are for making the reader aware of any special issues or considerations in looking at the financial statements in the worksheets of Excel file. In A30 type: Notes; in A31 type: Note 1: The aforementioned numbers are estimates based on forecasts of future business activity.; in A32: Note 2: Doggy Grooming on the Go predicts increased sales of Mutt services during the summer months when customers play with their dogs more outside. This modification in percentages will be reflected in monthly forecasts.; in A33: Note 3: Doggy Grooming on the Go predicts steady sales throughout the year for Pedigree and Westminster customers as these dogs tend to always maintain a groomed appearance.; in A34: Note 4: Doggy Grooming on the Go increases these estimates by 5% each year to reflect inflation and changing market conditions.

9. The worksheet may not show all that you have typed; however, by double clicking on A31, A32, A33, or A34, the whole note will show up on the screen and can be read without any problem.

10. We can improve the appearance of the worksheet by bolding, italicizing, and underlining Labels and Categories as needed by clicking on the 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); A4; A8:A9; A13:14; A19:A20; A25:A26; A30.  Let’s italicize: A13:B13; A19:B19; A25:B25. Let’s underline with the Bottom Border button on the top ribbon for: A3:C3; A12:B12; A18:B18; A24:B24; A30:B30.

11. We can also spread a column out to show the entire entry by moving the mouse pointer over the column entry. You will notice that the pointer changes its shape as it passes over the boundary between the columns.  Click while the pointer is over this boundary and drag until the column is wide enough to accommodate the text.  So go to the top of Column A and click and drag until Column A is wide enough for label A26.

12. Now that we have built our Notes and Assumptions worksheet, we can populate it with data. In Column B, we will enter the numbers for each category.  Let’s assume the following estimates:

a. In B5, type 50; in B6: 125; in B7: 200

b. In B10, type 7; in B11: 5; in B12: 15

c. In B15, type 7; in B16: 10; in B17: 45; in B18: 10

d. In B21, type 7; in B22: 15; in B23: 75; in B24: 20

e. In B27, type 30; in B28: 50; in B29: 20

f. And to make everything look nice and neat, let’s click and drag from B4 to B29, so that that area is shaded.  Click on the Align Right button on the top ribbon, and everything should line up nicely to the right.

13. Now let’s calculate the Cost of Services. At the top beneath the top ribbon, you’ll see fx and a long, skinny box to the right of it.  In that long box, you’ll be able to type calculations that you want Excel to do for you.  Since Cost of Services is the sum of all the costs in each package, we’ll have the formula in those boxes reflect that.

We do that by first clicking in the B13 box.  Now go to the formula box and type: =SUM(B10:B12)

This means that B13 is the sum of the B10, B11, and B12 boxes.  So the Cost of Services for the Mutt Package is the sum of the Gas and Mileage, Supplies, and Labor each time that job is performed for a customer.  The Mutt Package is just a basic wash and groom service.

Next click in the B19 box.  Now go to the formula box and type: =SUM(B15:B18)

This is the Cost of Services for the Prestige Package.  You’ll notice the labor is higher and there is a professional look fee as well.  This service includes more fine trims to reflect the unique fur qualities and looks of particular breeds.  Additional touches such as fancier bandanas and ribbons might be added to the look as well.  As such, this service takes more time and care than the basic services of the Mutt Package.

Next click in the B25 box.  Now go to the formula box and type: =SUM(B21:24)

This is the Cost of Services for the Westminster Package.  Customers who want this package tend to have show dogs who compete in contests or are present at dignified social affairs.  As such, they want their dogs to look perfect.  This extra care requires the most labor and attention to detail, and the customers of the Westminster Package are willing to pay for it. 

For B13, B19, and B25, let’s make sure that those numbers are bolded and italicized; as are A13, A19, and A25.  We want these estimates to pop on the spreadsheet because these play a major role later in determining what we clear in profit after selling a package (also known as Gross Profit or Contribution Margin, which will appear in the Income Statement you would do in completing your financial statements).

Well, that’s it! Congratulations! You did it!  You cooked up your first financial statement in Excel.  That wasn’t so bad, was it?  Now that you’ve got the basics down, you’ll find that future dishes you want to serve up won’t be so hard to prepare.  But keep in mind, just like a fine meal, the main course takes a little more work and has a few little tricks you have to pull off to make it sizzle.  

A FEW TRICKS OF THE TRADE

Okay, I’ll let you in now on a few key tricks of the trade that will help you out on those dishes.  For bigger statements, you’ll be adding new worksheets, integrating data from one worksheet to another, and building formulas in a new worksheet that include integration.  So let’s take a look at these little twists:

1. To add a new worksheet in the spreadsheet, simply click on the bottom label and right-click “Sheet 2,” choose Rename, and then type Sales Forecast, for example, over the existing name. A Sales Forecast along with an Operations Forecast and Capital Expenditure Forecast are helpful in building the Big 3 comprised of the Income Statement, Balance Sheet, and Cash Flow Statement. 

2. When you have multiple worksheets in a spreadsheet that make up your financial statements, data in one worksheet might be used in another. For example, you might decide to use a package’s price in computing revenue in a sales forecast.  To do that, click on the box where you want to enter a formula. Let’s say you want to calculate the sales of the Mutt Package in the Sales Forecast.  In the chosen box in the Sales Forecast, type: = and then go to the Notes and Assumption worksheet and click on the Price for the Mutt Package.  Excel will automatically place it in the formula.  Now type: * which is the sign for multiplication in Excel (division is / and subtraction is -), and click on the box in the Sales Forecast where the number of units sold for Mutt Packages is; then hit Enter and the formula is programmed for the box and the result will now be showing.                                         

3. If there are multiple columns using that calculation, we can duplicate the formula for all of the columns in a row of a worksheet.  Simply click on the first box in a row.  Then put the cursor in the shaded area and drag it to the end of the row and hit Enter.  You will do this often if your worksheet has each month of a year in it or if it has multiple years across the top of the columns.  Both formats are common in financial statements, as sometimes you are preparing a year in depth and other times providing a summary of multiple years together. You can also use the same approach if you want to add up columns. For example, in a Sales Forecast you may want to add up the sales of each package to calculate the Total Sales for a month or year.

You may find at times when you integrate worksheets and put a formula in a box that when you try to copy and paste it across a row, that it doesn’t provide the calculation you want.  If that happens, click in the box with the problem and look at the formula box. You may need to tweak the formula for each box to get the right one computed.  Let’s say you put a formula in a box for the month of January.  The other 11 months may read 0 after you cut and pasted the January formula into them.  You know this isn’t right.  A simple letter change in the formula box can be a quick fix when this happens.  Again, you may need to do it for the other 11 boxes after January in a row.  But it doesn’t take but a minute to make those fixes.

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.