How to add Pay Rate, Bill Rate, and Mark up Percent to the Trans Master Sum v4-Branch report:
Open the report in Canvas for the date range that you want to see, then click Next
Scroll down on the next page and select your branch, then click Finish
After it loads, download the report in Excel 2007 Data
Open the file after if downloads
Add a column between column U (labeled Pay Amount) and column V (labeled Billable Charge ID) and label it Pay Rate
In cell V2 enter the following formula: =U2/T2
Hold your mouse over the lower righthand corner of the cell and double click so that the formula copies to the bottom of the sheet
Then insert two columns between column AD (labeled Bill Amount) and column AE (labeled Pre Invoice ID). Label your first new column “Bill Rate” and the second column “Mark Up”
In cell AE2 type in the following formula: =AD2/AC2 then double click the lower righthand corner of the cell to copy the formula all of the way down the sheet
In cell AF2 type in the following formula: =IF(AC2=””,AD2/U2,AE2/V2) then click the lower righthand corner of the cell to copy the formula all of the way down the sheet
