Margin Report

To run the report:

1. Go to the Office menu > Shifts on Day.
2. Choose any date on the calendar prompt, we can change the date range once the shift list opens.
3. On the first drop down, choose Verified complete shifts. These are the shifts we know have been worked.
4. On the next drop down to the right, choose your date range.

Figure: Shift List

5. Go to the File menu and Choose ‘Export Shifts to Spreadsheet’
6. Click the Export button and save the file to your desktop.

Figure: Shift List Export Fields

7. Open the file on your desktop you just saved. If you did not change the name, it should be called ‘shifts’.
8. The data may be unreadable, that’s okay, we just want to select all of the cells. (Click the button between the 1 and A) then choose Insert > Pivot Table.

Figure: Shift Data in Excel

9. This pop-up will show up, just click OK.

10. Now you will see a Blank page, with a column of fields for the table on the left. Click and drag the row labels you want, in this case clientFullName. 
11. Then, drag payAmount and billAmount into the value fields, make sure they're in this order so each formula works. Make sure that the values are Sum, not count. You can change this by going to the little arrow by the value and choosing “Value Field settings.

12. Finally, you'll need to do two formulas to get the difference and the margin. The first formula is the difference between the bill amount and the pay amount. Formula using the above example: =C5-B5. The margin would be the difference divided by the bill amount. Formula: =D5/C5.

Figure: Adding margin in Excel
13. You’re done!