Dummies Brilliant Business Moms!
Business income and expense tracking is always last on my list. I know that at some point I’ll cram it all in! But I also know I can do it super fast, thanks to Microsoft Excel!
If Microsoft Excel is not your cup of tea, let me help you. Get out your nerdiest glasses, open up a blank spreadsheet, and as Sarah Kornhak says, “let’s get started!”
DOWNLOAD AND FORMAT YOUR ORDERS
I want to show you how to take the raw data from your Etsy shop and crunch the numbers. This will also work with orders from any other platform as well, but they will be formatted a little differently.
- To download your orders in Etsy, you will go to “Orders”, and at the bottom of the page “Download a CSV file with the details for each order.” Decide which date spread to download.
- Once you’ve downloaded your data, save your spreadsheet.
- Next, it can be helpful to clean up your columns so you are looking at less information. To do this, you will hide the columns you don’t want to see. I’m going to hide some of the columns that are blank. Select the columns by clicking on the column letter, and dragging over to the last column you want to hide. Right-click, select “Hide”.
- Note that every selected column will be hidden, so if you want some to show, just hide them in sections. To unhide the columns, follow the same steps and click “Unhide”. You’ll know that you have hidden columns if you see missing column numbers at the top.
QUICKLY FILTER ORDERS
Maybe you want to see how many of one item you’ve sold over the last few months. Easy peasy!
- Double-click on the bar to the right of my item title column to expand it to show the full item descriptions. (Mine are looong, because I’m sure to maximize my title SEO!)
- Click on the number to the left of your Header Row. It should be the first row, row 1. This will highlight the entire row.
- Click on the “Data” Menu at the top, and select “Filter”. Now you should see a little arrow by each row heading.
- When you click on the little arrow to the right of your Item Header, a black box pops up with all the items in your item row. Uncheck “Select All” (top of the list), and select one of your items to view. Once you click away from the black box, you’ll see only the items you filtered out.
- THE CHEATER MATH WINDOW (not his real name): If you want to quickly sum the totals for the items you’ve filtered, click on the column letter above your “Price” column. This selects the entire column. Now you’ll look at the bottom of your Excel window. There is a little cheater math window that says “Sum” (or it may say “Count”). Click on the little black arrow on the right side of the box and you’ll see the variety of information you can get out of this little guy. It’s a great quick way to sum a column without actually entering formulas.
- To clear your filter, click on the little funnel (formerly an arrow) at the top of your column, and click the checkbox that says “Select All”. Now all your rows will reappear.
TOTAL UP YOUR SALES
Here’s the moment of truth! Your sales totals! We are going to make another worksheet in your Excel Workbook to hold your totals.
- Click on the little + on the tab at the bottom of your window to create a new worksheet.
- Rename the Sheet “Totals” by double-clicking on the title.
- Select a Cell, any Cell, and type “Total Sales”.
- In the next column, type “ =sum( ” and then click the tab at the bottom of your window that will take you to your orders worksheet.
- Click the column letter above your order totals column, then hit “enter”.
- Voila! You have your total sales in your “Totals” sheet. If you download and paste in more orders, this total will continue to update, because you’re totaling the entire column from your orders worksheet.
TRACK YOUR EXPENSES
Grab another cup of coffee and a square of chocolate and sit back down – time for expense tracking!
- Add two more blank worksheets to your spreadsheet by again clicking the little + sign on the tab at the bottom of your window. Label one “Expenses”, and the other “Expense Categories.”
- On the Worksheet “Expense Categories”, type a list, all in Column A, of categories you can think of for your business expenses. It’s ok if you can’t think of all of them now, you can add more later.
- Go to the Worksheet “Expenses” and create three columns. (You can add more later if you want more detail.) Column A: “Date”, Column B: “Expense Category”, Column C “Amount”.
- Click on the “B” at the top of Column B to select the column. Go to the Data Menu and select “Validation”.
- Under “source”, click the emblem to the right of the blank. Click on your tab “Expense Categories” to go to that worksheet. Select a large chunk of Column A – as many rows as you think you’ll need for Expense Categories in the future. I’m selecting 30 rows of Column A. Hit “Enter”. Then hit “OK”.
- Now you should be back in your “Expenses” worksheet. Click on the top blank cell of the column “Expense Category.” You’ll see an up/down arrow to the right of the cell. Click on this arrow and you will see a box displaying your categories, and you can select the appropriate category for the expense your are entering. This will keep your expenses categorized correctly so you can easily sort them just like we did above for orders. You can also get fancy and learn to use VLOOKUP or SUMIFS formulas within this spreadsheet to easily show you totals for each category.
- Go to your “Totals” worksheet, and follow the instructions for totaling up your sales, but this time do it for your expenses.
Phew! That was a lot of information. But I really hope you feel more prepared to track your earnings and expenses on your computer.
If you aren’t interested in making your own Excel business expense/income tracking spreadsheet, or would like more reporting capabilities, I sell a Home Business Planner for Excel in my Etsy Shop, Perennial Planner. It is super user-friendly to enter your income and expenses, and easy to view your daily, monthly, and yearly income/expense/profit at a glance. Other worksheets include a mileage tracker, invoice, contacts sheet, hourly custom work tracker, schedules, goal tracker, and more!
Sarah Gilcher is a pastor's wife and mom to three girls. She believes in making life more organized with printables and tools, which she sells in her Etsy shop, Perennial Planner. And Sarah believes in making those tools beautiful and inspiring too! She lives in Washington state. You can learn more about Sarah on her blog, PerennialPlanner.com.
Find this information helpful? Look at these posts, next!
Episode 22: Pricing Your Products – The Details