I wish to do the following with the attached data set:
Firstly, I want to match the prospects (leads) we’ve acquired in the Prospect tab, with the contracts we’ve settled in the Settlements tab. This is so we can show how many marketing leads have resulted in customers. We should match on email address and company name. So using the first row of data for example, if D2 in the Prospects tab matches with D2 in the Settlements tab, or if E2 in the Prospects tab matches with C2 in the Settlements tab. We only want to count it though if the Created Date in the Prospect tab is BEFORE the Contract Create Date in the Settlements tab. I’m thinking this might be done through a VLOOKUP at the end of the Settlements table, whereby if there’s a match it will say ‘Matched’.
Secondly, I then want to work out, what our conversion rate is of prospects to customers month on month. For example, if we got 100 prospects in June and since then, 50 of them have turned into customers, the conversion rate would show 50%. This might be done through a VLOOKUP at the end of the Prospects table.
Lastly, I’d like to show the average time from Created Date in the Prospect tab to Contract Create Date in the settlements tab. This is so we can view the average time from when we get a lead, to when the contract will eventually settle.
I’d like this to be set-up so I can just keep pasting data in and it will keep updating. Happy if there’s a little bit of manual manipulation need here, i.e. dragging down VLookup formulas, refreshing the pivot tables etc.
The data is sensitive so will only be provided to the hired freelancer.
Expert in Excel, Excel formulas, and all excel functions, macros, lookup, pivot tables and charts. Have done many projects in Excel, created many dashboards, and Business intelligence tool for forex markets. Can complete on time with good quality and on time. Expert in referencing and will deliver work with no plagiarism. Experienced MBA Finance as well.
Hello
I am here to assist u inthis project
I am an ACCA U.K member currently working in finance dept of Qatar Airways
Can share u example also
Have great skill and experience in Ms excel and finance
Plz share more info to proceed in project.
Thanks in anticipation
Looking forward for this projext positively
Thanks
Obaid