All of the information you need to create a new monthly report exists in the previous reports. You’ll just need to copy and paste everything, utilize subtotals and enter formulas to calculate totals and percentages. You have two options for creating a new dashboard, which I’ve listed below.

1. Simply use a previous report to build the dashboard.

• Look for similar months (i.e. if June starts with 5 days, then use that to create a dashboard for another month that starts with 5 days).
• Monthly sales goal comes from Yearly Sales report
• Copy the yearly Pro Enhancement tracking chart from the previous month. 
• Get PE projected renewal figure from row 27 of Yearly Sales report

                                                                                                    --OR--

2. Build a new dashboard from scratch by opening a new workbook in Excel.

• Click on Row 1 and copy the row, then paste it into your new workbook
• Under Column B, type in the dates for the month
• In column A, put the week number. 
  • Remember, weeks run Sunday through Saturday. So the beginning and end of the report may not be 7 days each.
• Highlight the entire chart, then go to Data and click Subtotal. You want a subtotal for all columns except week and date.
• Monthly sales goal comes from Yearly Sales report
• Complete this process for the Pro Enhancements and Purchase Breakdown Charts. 
• Make sure that the Pro Enhancement chart starts on the corresponding row to your top chart 
  • i.e. the first day of the month on the top chart will be row 2, therefore on the Pro Enhancements chart, the first day of the month will be on row 42.
• Copy the yearly Pro Enhancement tracking chart from the previous month by highlighting the first two columns of this chart (month and percentage) and click Insert at the top menu. 
  • Insert a chart to show visualization of the monthly renewal rates.
  • Get PE projected renewal figure from row 27 of Yearly Sales report

• Open your saved report for the current month.
• Open the [email protected] email (login details in 1pass) to get daily revenue numbers
• Fill in column C with the end of day figures for each day in the previous week

• Open Chargify (Fundy Suite) and pull Legacy Proofer numbers by opening changing to the Proofer iteration and following these steps:
  • Select transactions
  • Put in the single date you need to pull, and check Payments and Refunds
  • Manually add/subtract the black and green numbers only
  • Do this for each date in the date range you need to complete
• Next, you’ll want to document Pro Enhancement failures. 
  • Switch back to the main dashboard for the Fundy Suite in Chargify, click the red i icon under expired cards

• Click the Subscription Status drop down select Past Due to apply filter.

• Click “Period End” to sort the dates in the correct order

• Manually count the number of failures in the Period End column for each day and record those as well as the $ amount in Columns G and H.

**Complete the process for each day in your date range.**

• Lastly, go to Transactions. From there:
  • Select the date range for the previous week
  • Check the boxes for Payment and Refund.
  • Click Filter, then Export Current View (all pages) and once the report is ready, click the download link.

• Right-click and select Open, then select to open the document in Excel.

• Change Column B to short date
• Sort Column L smallest to largest (if prompted, select to expand the selection)
  • Copy/paste all FALSE rows into a new tab of the Past Dues google doc
  • Delete all rows that appear FALSE in column L
• Insert two blank rows between the date breaks

• Select all payment amounts for a single date in Column E, then sort smallest to largest (A-Z, expand the selection if prompted)

• Cut/paste all refund rows to the top of the specific date section and highlight in red or bold text. *This is important to denote what is refund vs a purchase.

*Note: I recommend working on one day at a time to make it easier to find and fix errors.

PRO ENHANCEMENTS:

• Go through and fill out refunds for Pro Enhancements and Lease transactions. Record the number and dollar amounts in Columns E and F.
• Highlight cells that are full price Pro Enhancement renewals to see a count of the number of renewals and the amount of those total renewals. Enter this in Columns I and J in the Pro Enhancements chart on your dashboard report

• Any  “Full price component allocation changes” for $129 are discounted PE renewals. Fill in the total renewals and $ amount in Columns K and L on the dashboard report
  • Allocation changes that are odd amounts (prorated for switching from Legacy Proofer to Pro Enhancements) should be added to the Full Price renewal column (not discounted)
• On the dashboard report, Columns C and D should automatically add together the number of full price and discounted renewals for the respective date.
  • These are automated using SUM formulas.

• After you’ve recorded figures for each date in the Pro Enhancements chart, you can check the top sales chart to see the breakdown for Pro Enhancements and Software sales.
  • Check the cell in Column D corresponding to the date you are entering data for.
  • A formula example for the cell pictured below is: =c16-(d56-f56)
    • C16 is the cell number for 7/13, d56 is the corresponding cell in the Pro Enhancements chart for renewals and f56 is the corresponding cell for Pro Enhancements refunds.
• In Column E, you can see the SW sales subtracted from SW sales w/PE, so we know how much went to PE alone. For this example, the formula would be =c16-d16

• Next, update the PE renewal percentages in the renewal rate chart, using the figures at the bottom of the PE section.
  • Yellow Percentage goes in Renewal Rate box.
  • Green “Grand Total” above goes into Total Renewals box.
  • Projected Renewals are taken from our Yearly Sales report.
  • % to projected  = Total Renews/Projected Renewals
  • The graph on the right should automatically update as you update these figures.

• After wrapping up PE renewals, you’ll complete the Purchase Breakdown chart for the date in question. Purchases are broken down in the following manner:
  • # of Album and Pro Suites (combined) and $ amount
  • # of Lease Purchases and $ amount
  • # of Bulk Upgrades and $ amount
  • # of Lease Upgrades and $ amount
  • # of SDA and $ amount
  • Note: Crossgrades go under Album and Pro Suites
• You’ll highlight the cells the same way you did for Pro Enhancements
  • If a full return/refund was completed in full for an item, you’ll subtract that amount from both the unit total and $ amount for that item
  • If a partial return was completed, you will only subtract the amount from the $ amount, NOT the units sold.

*NOTE: Be sure to pay close attention to the Memo column of the Account Transaction CSV, bulk upgrades often have the same pricing as our leases.

• During or after working on the purchases breakdown, you’ll complete the Lease Purchase Breakdown chart for the date in question. Leases are broken down in the following manner:
  • # of Yearly Pro Suite Lease Purchases and $ amount
  • # of Yearly Album Lease Purchases and $ amount
  • # of Monthly Lease Purchases and $ amount
  • # of Yearly Pro Suite Lease Upgrade Purchases and $ amount
  • # of Yearly Album Lease Upgrade Purchases and $ amount
  • # of Monthly Lease Upgrade Purchases and $ amount

*Note: It is extremely important to pay attention to price points and memo lines to make sure we are accounting for regular and lease purchases in the appropriate columns.

**After recording info, make sure your calculations are correct by comparing the breakdown totals to the total software sales figure at the top of your report. 

• Take total PE renewals from column D and subtract total refunds listed in column F. That amount should match PE Sales only total at top of report.

• Hold down CTRL/CMD and select total weekly purchase amounts. Total amount should match SW Sales Only figure at top of the report.