Hi,
In a bit of a rut picturing which formulas/processes would be best to use. Some background info:
A consignment business which has a portfolio of cars for rent to the general public, by the car's individual owner. The business itself is only involved in finding the people interested in renting, and facilitating the rental between the owner and renter - a middle man. They collect the payment from the renter, deduct a % or flat fee for admin costs on each line item, and then pay out the rest to the car owner.
Each car has it's own daily rate & cleaning rate, and not all owners are paid out at the same % for the daily rental charge either - though the cleaning fee is always 100% paid to owner.
Other optional add-on charges are either a:
-One-time trip charge (For instance, Damage Protection being a flat $100)
-Daily rate charge (Roadside Assistance at $10/day), or
-Percentage of charge, whereby it would include/exclude certain items from the rest of the invoice before multiplying it against that % (Service Charge of 10% on all invoice items except insurance, for example)
With that said, the reservation system is not the greatest - it's good at generating the invoice to the renter but can't do the owner payout calculations/reporting; that's all currently being done in Excel, with very heavy manual data entry.
So I'd like to automate this whole process as much as possible. I've attached the beginning shell of the workbook I started out envisioning, as well as the current manual template that's in use in the first tab.
What I'd like as a finished product, is to be able to plug in the basics of the invoice on Sheet1, via manual entry: input date range of the rental - starting & ending dates, select the car from a dropdown, select the various add-ons that apply to the rental, input any one-off charges that will always vary by occasion (tolls, damages, etc.).
On Sheet2 there would be tables of the base rental pricing and payout % by individual car and another for add-on item pricing structures.
And then finally, Sheet3 would generate both an invoice and bill, similar to what the current template looks like, but using VLOOKUP's/IF (and/or others?) to calculate each line by referencing the Sheet1 detail against the Sheet2 tables....ie:
For 'Item': a formula that says, "If an X is next to 'Item' on Sheet1, show 'Item' "
For 'Qty', a formula that says, "If 'Item' invoice rate is 'Once', 1, if 'Daily', # of nights total (Sheet1!B3, in the attached), etc...,
and in 'Total', a formula that would lookup the daily rate, or Flat rate, for that specific car or add-on item, and multiply it against 'Qty', or calculate the % for the items that are percentage-based.
I feel like this all seems like it shouldn't be too hard to do using nesting but can't figure it out myself....anyone able to start me on the right path??
Alternatively/Also - open to more efficient/easier ways to accomplish these automations, this is just the path my mind first went down because I'm already pretty familiar with VLOOKUP/IFS.
