This is a report that gets sent to Carat at the beginning of each month giving them a breakdown of their spend for different products and packages they have had through the month.
TABLE OF CONTENTS
Things you will need to run this report
- Dentsu Data (This is provided by usually provided by Fiona Wright/Adam Roberts in Finance. Contact if this has not come through by the due date)
- Actual Billing (Usually available during first week of the month)
- Barratt’s invoices for XXX Month (These can be obtained from CIS team or from Finance team Fiona Wright/Adam Roberts)
Running the report
1. Before running, open Actual billing file and Bill File
2. You will need to copy the data excluding the titles from “Actual billing” to “Bill_file”. Save the Bill_File and close Actual Billing.
Reminder: DO NOT SAVE THE ACTUAL BILLING FILE once finished
3. Now go here:
4. Open the MS Access Document called “Carat_Report” file and click Run Report
5. Wait until this has run, it should only take a few minutes.
6. When the report has run, close the access file and head to the file location below, and open the excel file usually named Carat_Invoice.
7. Now you need to sort the data. Open the File Carat_Invoice.
8. When you open it you will be given 2 tabs, ignore the summary tab for the moment and open the Invoice Data tab.
9. Initially, Right click on C Column and “insert”. Do this twice so you have two blank columns. This is because when we split the data, it can sometimes have 3 codes in so need these additional columns.
10. Highlight the H-Code Column and then go to Data>Text to Columns. In the box that opens Select Delimited>Tab, Comma, Space. All ticked > Next and Finish.
11. The data will now be split into 2 columns separating Dev Codes and H-Codes out. ->
Select the “1” row and click on Sort and filter > Filter.
12. In H-Code filter, sort by A-Z and in column C which should be blank, clear any which have a H Code. Half way down this list the Dev code and H-Code will switch. Copy the Dev codes and paste them over the H-Codes in the B column.
13. The H-Code column (B Column) should only have a Dev Code in it.
14. Delete the empty C and D columns as we no longer need these.
15. You may find some Dev codes are missing on some developments. These are normally ones which have come off site. But double check if this has initially come off site.
16. Next we need to adjust the From Date / To Date / Duration columns. Several of these are incorrect with From Dates and Duration
17. On Column M Add another column. Filter Duration > Anything with a date > Move the Dates in Duration column and put them into the blank column created > Copy the duration numbers from “To Date” and paste them in the Duration column > Copy the additional dates from the blank column back to the “To Date” Column > Clear Filter on Duration column. Then check both To Date and Duration columns to ensure that in “To Date” Has only short dates in it and Duration only have Numbers 1 – 31 present in it. Anything else, repeat steps above.
18. Format as a table – Go for the Blue Medium 2 table.
19. Go back to the Summary page and click insert and then Pivot table.
20. Highlight all the invoice data and create the pivot table below the PO data.
21. Put the Product Name in the Rows section and the Net Amount and the Product Name in the Values section.
22. Once you have done this copy and Paste 123 and delete the pivot table. (We’ll be adding FNH data so we don’t want it formatted as a Pivot Table, we just want the data from it)
23. Go onto the Dentsu Data and copy the Featured New Home data that was Manually billed (normally at the bottom of the data)
24. Add a row to your table for Featured New Homes and the cost.
25. Formula to calculate - =([@[To Date]]-[@[From Date]])+1
26. You will then need to update the Total cost column so that it is accurate.
27. Format the data and create a table.
28. You can then check this against the previous month to make sure all info is included.
29. When sending the report to Carat, check the previous report to make sure everyone that should be included in the email is there.
30. It should be sent to barratt.ops.team@carat.com + Callum.Dingle@rightmove.co.uk
Subject: Invoice Summary Report for XXX
Hi All,
Please find the attached Invoice Summary Report for XXX.
PASTE THE BREAKDOW TABLE
The report contains a Summary with a copy of your Purchase Orders and the total monthly invoice value grouped by product type.
If you have any questions please let me know.
Many thanks,
Attach Carat invoice to the email too.
