Easier way to calculate differences between spreadsheets
- or -
Post a project like this3621
£350(approx. $440)
- Posted:
- Proposals: 1
- Remote
- #480833
- PRE-FUNDED
- Awarded
Description
Experience Level: Expert
Estimated project duration: less than 1 week
General information for the business: We are an IT company but require someone who has really good Excel knowledge and perhaps VBscript
Industry: Technology
Kind of support: Software
Description of support work: We have 4 files that relate to the charges made to our customer by the Water board for wholesale supplies of water and associate waste.
P1 is a provisional file that looks at the premises registered to our customer for July 2013 it was downloaded from the central system on 5 June 2013 and they pay in advance for the predicted number of customers premises and volumes.
R1 is a reconciliation file that looks at the premises registered to our customer for July 2013 it was downloaded from the central system on 1 August 2013 and the customer pays or are credited with the difference between the P1 file and this R1 file. The changes are invariable that the customer signed new or lost customers or meter readings carried out changed the predicted uses.
R2 is a reconciliation file that looks at the premises registered to our customer for July 2013 it was downloaded from the central system on 24 September 2013 and they pay or are credited with the difference between the R1 file and this R2 file. The changes are invariable that we have completed meter readings and this has changed the predicted uses, or something has changed with the premises like the Rateable Value (RV) has be changed.
R3 is a reconciliation file that looks at the premises registered to the customer for July 2013 it was downloaded from the central system on 25 March 2014 and they pay or are credited with the difference between the R2 file and this R3 file. The changes are invariable that the customer have completed meter readings and this has changed the predicted uses, or something has changed with the premises like the Rateable Value (RV) has be changed.
Each premises has a maximum of 2 Supply point identifier (SPID's) which are 12 digits long one for water ending in 1XX and waste/drainage ending in 2XX. Therefore the first 9 digits are know as the Core Spid as they will be that same for water and waste.
A premises may have water only, waste only or water and waste and are classed as SPID categories in column J of the spreadsheets. Please note that a premises could have a number of meters attached to it but will still only have one water spid as the spid is for the premises.
What I want to achieve initially is an easy monthly comparison to see what new or lost spid came between each run, and the highest variances in financial terms (both positive and negative).
The 5th file shows all of the customer spids in their own CRM system, this is downloaded each month to assist with data cleansing, but they also want to know which spids are in or missing from our system and the P1, R1,R2 and R3 files. This will allow the customer to check if they have are being charged for customers they are not aware of or any new customers they have found and requested a spid for but not been informed by the Water board that the spid has been issued (hence the TBC (to be confirmed)). they can then match them up.
They need these outputs to be in an easy upload form so that it can be done by an administrator to generate a report for managers to act on efficiently.
Extra notes: Spread sheets will be provided upon an NDA (non disclosure agreement) being signed
Industry: Technology
Kind of support: Software
Description of support work: We have 4 files that relate to the charges made to our customer by the Water board for wholesale supplies of water and associate waste.
P1 is a provisional file that looks at the premises registered to our customer for July 2013 it was downloaded from the central system on 5 June 2013 and they pay in advance for the predicted number of customers premises and volumes.
R1 is a reconciliation file that looks at the premises registered to our customer for July 2013 it was downloaded from the central system on 1 August 2013 and the customer pays or are credited with the difference between the P1 file and this R1 file. The changes are invariable that the customer signed new or lost customers or meter readings carried out changed the predicted uses.
R2 is a reconciliation file that looks at the premises registered to our customer for July 2013 it was downloaded from the central system on 24 September 2013 and they pay or are credited with the difference between the R1 file and this R2 file. The changes are invariable that we have completed meter readings and this has changed the predicted uses, or something has changed with the premises like the Rateable Value (RV) has be changed.
R3 is a reconciliation file that looks at the premises registered to the customer for July 2013 it was downloaded from the central system on 25 March 2014 and they pay or are credited with the difference between the R2 file and this R3 file. The changes are invariable that the customer have completed meter readings and this has changed the predicted uses, or something has changed with the premises like the Rateable Value (RV) has be changed.
Each premises has a maximum of 2 Supply point identifier (SPID's) which are 12 digits long one for water ending in 1XX and waste/drainage ending in 2XX. Therefore the first 9 digits are know as the Core Spid as they will be that same for water and waste.
A premises may have water only, waste only or water and waste and are classed as SPID categories in column J of the spreadsheets. Please note that a premises could have a number of meters attached to it but will still only have one water spid as the spid is for the premises.
What I want to achieve initially is an easy monthly comparison to see what new or lost spid came between each run, and the highest variances in financial terms (both positive and negative).
The 5th file shows all of the customer spids in their own CRM system, this is downloaded each month to assist with data cleansing, but they also want to know which spids are in or missing from our system and the P1, R1,R2 and R3 files. This will allow the customer to check if they have are being charged for customers they are not aware of or any new customers they have found and requested a spid for but not been informed by the Water board that the spid has been issued (hence the TBC (to be confirmed)). they can then match them up.
They need these outputs to be in an easy upload form so that it can be done by an administrator to generate a report for managers to act on efficiently.
Extra notes: Spread sheets will be provided upon an NDA (non disclosure agreement) being signed
Stuart O.
100% (1)Projects Completed
4
Freelancers worked with
4
Projects awarded
44%
Last project
5 Oct 2012
United Kingdom
New Proposal
Login to your account and send a proposal now to get this project.
Log inClarification Board Ask a Question
-
There are no clarification messages.
We collect cookies to enable the proper functioning and security of our website, and to enhance your experience. By clicking on 'Accept All Cookies', you consent to the use of these cookies. You can change your 'Cookies Settings' at any time. For more information, please read ourCookie Policy
Cookie Settings
Accept All Cookies