How we used Zapier to provide a real-time view of our company’s income to all employees and VCs

One of the most important things a good manager can do is provide employees with all the information they’ll need to make smart decisions in the company’s best interest. One key piece of info that needs to be communicated simply is, are we achieving our goals?

I wanted every employee to have a full and clear view of our company’s financial situation. However, I didn’t want to do this with quarterly reports. For me, quarterlies create two big issues:

• You have to wait for the next quarter for accurate, up-to-date information. In startups, last quarter is already ancient history.
• Somebody has to compile these reports. If it’s you, it’s time you could be spending working productively and efficiently. If it’s an accountant, it’s expensive and nobody’s going to understand the report!

I wanted to come up with a way for anybody in the company (employees, VCs, the board) to get real-time answers to the following questions:

• How much did we bring in yesterday? Last week? Last month?
• What is our revenue goal for next month?
• Are we achieving our goals?

Yes, what I wanted was any entrepreneur’s dream, but I wanted to realize this dream without having to do constantly dig through numbers.

Do you share the same dream? Great! Let’s make that dream COME TRUE.

Step 1: Create a new Google Spreadsheet. We’ll call this file “My Company’s Live Finances”

Name the first tab “Projected”

In this sheet, you’ll want to list all of your projected revenue and expenses by month for the calendar year. Eventually, you’ll be able to compare your company’s actual performance to these projections.

Your data should look something like this:

Step 2: Prepare your spreadsheet to receive data

Open a new TAB on your spreadsheet and name it “Data.” Use the template below:

Connect your Spreadsheet to QuickBooks

• Go to your Zapier account
• Create a new Zap that you’ll call “Send QuickBooks Invoices to Report”
• Trigger: select QuickBooks, then “New Invoice,” and test this step
• Add a new step. Select “Google Sheets”
• Select “Create Spreadsheet Row”
• Connect the Google account where the Spreadsheet is stored
• You’ll now be able to “Set Up / Edit Template”
• For “Spreadsheet,” select the file we created in Step 1, “My Company’s Live Finances”. For “Worksheet,” select ”Data”
• For the new rows being created, select the following options:

1. Client: Customer Display Name
2. Invoice amount: Sub Total
3. Invoice category: Lines Sales Item Line Detail Itemref Name
4. Invoice date: Meta Data Create Time

• Test this step. For the test, you can create a new invoice for $0.01 (and then add a credit memo).

An important thing to note is that QuickBooks will not add credit memo to the files. This is something you’ll have to do manually for the moment.

Now, open a new Spreadsheet and name it “Importrange.” In this spreadsheet, create the same column headers as in the “Data” worksheet:

In D2, enter the URL of the Google Spreadsheet you created in Step 1 (“My Company’s Live Finances”). It’ll look something like this: https://docs.google.com/spreadsheets/d/1CpsRXgwKnUXrOPmnDN9_ij4-k0v9EaSkxNM6unLeSjg/edit#gid=650578112

Then, enter the following formulas in the corresponding cells:

A2: =ImportRange(D2;“data!A1:A10000”)

B2: =ImportRange(D2;“data!B2:B10000”)

C2: =ImportRange(D2;“data!C2:C10000”)

Step 3: Create Reports in the Google Sheet

We’re now going to create this table:

This table will present the following info:

• The total revenue for the current month
• The projected revenue for this month (i.e. the target for the month)
• The total revenue Year-To-Date, and the target revenue goal for the year
• For each month, the total revenue in each category
• The total revenue for each month
• The projected revenue for each month
• The difference between the projected and actual revenue

To create this table, start by listing the months of the year. You can start by putting “Month” in cell A1, and January in A2

Type the header “Total/month” in cell B1, and in cell B2, enter the following formula: =SUMIF(importrange!$D$2:$D$92000;A2;’data’!$B$2:$B$4000)

This formula allows you to get the sum of all the invoices that were created in January. Once done, drag this formula down to fill it for all the other months.

Now you’ve got something that looks like this:

Next, create a column with the header “Estimated Revenue”

In cell C2, enter the following formula: =estimated!B7

This formula will display the projected revenue for the given month.

Drag the formula down to fill it for all other months. You’ll now have the following:

Ok, now let’s create a 4th column. This one will be “Total Projected Revenue (YTD) at the End of the Month.”  

In D2, enter the formula: =C2

In D3, enter the formula: =D2+C3

Then, extend cell D3 to fill in each month:

Awesome!

Now we’re ready for our last column, which will show the difference between the projected and actual revenues. This one’s pretty easy 😉

• In E2, enter the formula: =B2-C2
• Drag the formula down

Now you’ve got your table all set up and it should look like this:

Finally, let’s add the general data on the top of the table:

Insert 3 new rows at the very top of the sheet.

In A1, we’ll want it to display the current month. You can use the formula: =TEXT(TODAY(),”MMMM”) to display this automatically.

In B1 enter this formula: =VLOOKUP(A1;B4:D18;2;0)

In A2, enter: 2017 Revenue

In B2, enter: =sum(B5:B16)

In C2, enter “Total Projected Revenue (YTD) at the End of the Month”

In D2, enter the formula: =VLOOKUP(A1;A4:G13;4;0)

Alright, we’re almost done!

In E2, enter: “2017 Revenue Goal”

In F2, enter the formula: =sum(D5 :D15)

Look at that! You now have the ultimate tool to monitor your company’s revenue, and it’s so easy to use! Your employees are going to feel more invested in their projects, and your VCs are going to let you manage your company instead of compiling stupid reports. How great is that?!