Weekly Commission Payment Workflow

Fortress Management (Sales Team)

  1. Navigate to “FM Track 2025” google sheet.

  2. Delete all duplicate entries (if any), only delete from “Original Data” tab.

  3. Adjust dates in “Original Data” tab from recent sales as necessary to put them in chronological order.

  4. Copy data from spreadsheet starting at the first sale of the year (for 2025, that’s cell A527) through column L of the last entry on the sheet and paste it in a draft email to Zach after the opening line, which says “Here are the numbers:”

  5. Write “Total commissions for the week: [input figure from U12]”

  6. Check column R for any black text indicating adjustments to the commissions and add explanatory text to email (if any): “Adjustments for the week: ________”

  7. Add a line, “Total payment after adjustments $[amount].” Put the amount in bold.

  8. Send the email to Zach.

Aegis Review (License Protection)

  1. Create a second draft email to Zach, start it with “There are are [# yellow rows pasted] new enrollees for the week:” and paste All the yellow cells from columns B to D. Delete any rows for people for whom the value in Column N is zero.

  2. After pasted data, type “That’s [number from cell U10] in payment for the week.” Put the amount in bold.

  3. Take the text in column R and turn it red, indicating it has already been accounted for in the weekly payment.

  4. Send the email to Zach.

For the End-of-the-Month Residual Payments

On the first Thursday of every month, in addition to preparing the above, we need to calculate residuals.

There will be one change to the instructions above. Instead of copying starting in row 527 (the first sale of the year), you’re going to go back an entire year, which means starting your selection for copying at the first row where column M has a number 1.

To calculate the residuals for Fortress Management, that means calculating their share of the monthly Fortune Club payment (they get one amount for FC clients in their first year and another for clients in years 2+) and then their share of the installment payments (some clients split up their initial payment into six monthly payments, so FC’s commissions are spread out accordingly). Here’s how you do it:

First, put together your normal weekly email (with data going back twelve months instead of just to the beginning of the year). Set that aside for now.

The next step is not based on the email report you send, but rather is intended to identify those people whose credit cards have expired, have insufficient credit, or for whatever reason were not charged like they should have been.

Identifying Credit Cards that Need to Be Updated

You will have three spreadsheets that represent the transactions for the last month from the three merchant accounts we use.

  1. Go through each of them and identify the transactions that failed.

    • It is not uncommon for a transaction to fail, and then the merchant to try charging it again a few days later and for it to go through the second or third (etc.) time. If that happens, then there will be a line in the spreadsheet for each attempt. If it does ultimately go through, delete all rows that show the failed transaction. Those are irrelevant now.

  2. Make a list of every client whose transaction failed and send me an email with their name, email address (if it’s in the report) and the amount of the failed transaction. If it is a FC payment ($100, $150, $200, or $250), also include how many months left (if any) they have in their first year FC commitment. You will find that on the FM Track spreadsheet in column M. I will take it from there.

  3. Head back to the FM Track 2025 sheet, and for every person in your failed transactions list who is still in their first year of the Fortune Club (everyone in the spreadsheet with a value in column M) and delete the value in column K. This will also empty the value in column M. Then, in column Q, Notes, you will add a note that their credit card was declined.

Fortress Management

Start with the draft you’ve already created from the instructions above (the same weekly email that goes out with the one adjustment to send data going back farther). We need to figure out the residuals so we can append those to the email.

Fortune Club Monthly Residuals

  1. Combine the three merchant report spreadsheets (which you will get from me) into one report that eliminates extraneous information.

    • Delete all rows that represent failed transactions (these are all the ones you identified in the last step; now that you’ve got those listed in an email to me, we need to delete them)

    • The combined report should just have the following columns: date, amount, first name, last name, email.

    • Two merchant reports have first and last names in the same column. That’s fine.

    • One merchant report doesn’t include emails. That’s fine, too.

    • We really just need to make sure we can identify the client associated with the amount and date of the transaction.

  2. Once you have a clean report (just the columns above and only rows with successful transactions), create a small table to count how many of each transaction there was in the month: $100, $150, $200, and $250.

    • Where column B is amount, and I2 has $100, you’ll use the following formula: =COUNTIF(B:B,I2) to count the instances of $100 payments. Do the same for each of the other amounts.

  3. Navigate to “FM Track 2025” google sheet.

  4. Go to the “Monthly Instruction” table (T46:U75).

  5. Compare last month’s numbers to the monthly numbers you just calculated.

    • The number of $100 payments is in cell U56

    • The number of $150 payments is in cell U64

    • The number of $200 payments is in cell U72

    • The number of $250 payments is in cell U48

  6. The numbers should be pretty close. Hopefully they’ve gone up a little bit, but they don’t always. If there is too much of a variance, double check your count.

  7. Once you are confident you have the right numbers, enter the numbers you’ve calculated into the respective cell to update the table.

  8. Now you’re going to copy the entire table (S46:U81) into your email to FM after adding the following line: “Here are the monthly residuals:”

Delayed Commission Payments for Installment Payment Clients

Now we need to figure out, for those clients who have split up their initial payment into six monthly payments, how many of those installment payments we received this month and how much to pay FM in commissions for those.

  1. Go back to your combined merchant report spreadsheet.

  2. Create a new tab. You can name it “installment commissions” or something like that.

  3. Gather the rows from the first sheet for any payment that is not $100, $200, $150, or $250 and paste them into the second tab starting in cell A2. These are from a merchant report that doesn’t have email, so all you’ll have is date, amount, and client name. It should span A2 to some row in C, depending on how many rows there are.

    • You should now have a substantially shorter list that has amounts ranging from $233 to $2,483. Make sure the values you include match up with the left-hand column values provided in Step 4.

  4. Create a table with two columns with the following values in cells J3:K13 (the first column represents the monthly payment, and the second represents the commission on that payment):

    $233.33  $113.17  

    $733.33  $436.50

    $816.67 $436.50  

    $900.00  $404.17 

    $983.33  $517.33  

    $1,066.67  $485.00

    $1,150.00. $598.17  

    $1,233.33  $565.35  

  5. Teo, I totally redid this table above based on mistake I found in my calculations. So do a totally new table using these numbers.

  6. In cell E2, enter the following formula: =VLOOKUP(B2,$J$3:$K$13,2,FALSE)

  7. Drag the formula down so there is a number output for every row.

  8. Sum the total values in column E.

  9. Copy the entirety of your data in columns A to E, and paste it into your email after the following text: “And here are the numbers for the pay-by-month people:”

  10. In the last line of the email, write the following: “Total payment for the month is” and then include the total weekly commissions less adjustments, monthly residuals, and installment commissions. Put the number in bold.

Aegis Review

This email is much simpler. You’re simply going to append the following after your weekly email based on date from the FM Track 2025 Google sheet:

For the residuals, we’ve got [U78] total enrolled, [U79] in their first year and [U80] legacy subscribers. So that’s another $[U81] headed to you in residuals.

Total payment: $[sum of weekly and monthly].

Phew. That’s a lot of work.