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)
  • Delete all rows that appear FALSE in column L
• Insert a blank row 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 or bold text. This is important to denote what is refund vs. a purchase.

• 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

• From your transaction report, highlight the Pro Enhancement returns. Record the number and dollar amounts in Columns E and F.
• Highlight cells that are marked “Full price component allocation changes”. These 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, add together the number of full price and discounted renewals and put this number in Column C for the respective date. Add the dollar amount for full price and discounted renewals and enter this into Column D. 
  • You can automate these by entering the SUM formulas.

**After you’ve recorded the information in your dashboard report, delete the rows you’ve recorded. This will keep you from accidentally adding that information again.**

• After you’ve recorded figures for each date in the Pro Enhancements chart, you can enter a formula in the top sales chart to separate the Pro Enhancement and Software sales. 
  • Click on the cell in Column D corresponding to the date you are entering
  • 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 subtract SW sales 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 Upgrades 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.

**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.

• From Zendesk, click the Reporting icon on the left
• Click Take Me to Explore
• In Zendesk Explore, click Tickets and enter the time period for the previous week (Sunday through Saturday)
  • Record the number of created(new) tickets and solved tickets into the Support tab on the dashboard report
• Click Efficiency and enter the time period for the previous week. Record the first response time in the dashboard
• Click Backlog and locate the date of Saturday for the previous week. Log the pending number in the backlog field on the dashboard report. (Note: MTD should show the average for the month)
• Click Satisfaction. Enter the time period for the previous week. Record the satisfaction rate in the support tab of the dashboard.