April 15, 2021
I need help please.
• I have a fleet of 300 cars and 20 customers
• The fleet size can change every week
• Invoicing occurs monthly
• Some of the car invoiced on the 15th and some on the 30 of each month
• 150 car have individual contracts and 150 cars have a pool contract
• Each contract have its own price matrix based on forecast mileage for the next quarter
• Example: there is a price for 100 000 km, another for 110 000 km, ....all the way to 300 000 km
• Question 1: how can I keep track of past invoicing per car, over the whole year up to the current month (mileage invoiced, price per car invoiced)
• Question 2: how can find out which car has to be invoiced with which price for the next billing cycle.
• I can use Excel or VBA. Will Power Pivot or Power BI help?
• How shall I organise my spreadsheet?
December 7, 2016
Are you sure Excel is the best tool for such tasks?
You can of course use Excel, but there are more adequate tools out there, just search for "car rental software" and you will find some options. If you choose to go with Excel as the tool, then you need to organize your data in a structured way, not necessarily in one workbook, it can be split to several workbooks if you want.
If you need more advice on how to set up structured tables then I can recommend you to consider one or more of the courses available at this site. Mynda is a good teacher.