I need someone to program an excel spreadsheet so that it will take the information from a sheet I can download and then place the projects and the dollar amounts in a monthly format so I can see how much will come in each month over the next couple of years.
I have attached a sample excel document that shows what I want.
The first page has the pipeline info I am downloading from my sales reps. The columns highlighted in yellow is the infomation I want to be auto populated on the second page.
On the second page I need to be able to see what projects and the amounts will be coming each month in the next years using the information in the yellow columns on page 1:
1) project name
2) net value
3) first ship date
4) last ship date
If you look at the examples I manually moved over you will see the first project has a the following values:
You can see I moved the information to the second page showing the project on May and June of 2019 with a monthly amount of $37,500 for each month - $75000 / 2 months
The second project I did manually has a the following values:
1) Spanis Peaks Lodge
You can see I moved the information to the second page showing the project on broken out January through December 2018 with a monthly amount of $115,000 for each month - $1,380,000 / 12 months
I want the 2nd page to auto calculate the data from the 1st page and automatically load it into the appropriate columns so we can clearly see the sales coming in each month in the future.
In my example I only have 10 or so projects on page 1, However I will need it to be able to sort up to 150 projects from page 1 into a meaningful chart on page 2. Should this be done in pivot tables, a dashboard, etc? I am open, I just want to be able to download page 1 info every month and be able to see the highlighted columns in a meaningful format each month.
I know this will take some programing and I will need someone who is solid in their Excel skills.
I am open to suggestions to make this work best. I want to be able to export this data each month into a sheet and be able to see what the future months look like to hold my reps accountable.
If this goes well I will have more work periodically as we find better ways to track our sales pipeline.
February 11, 2018
I am willing to pay higher rates for the most experienced freelancers