I need an Excel template we can use for our business which is a travel agency (we operate under TOMS VAT rules, so that is why we need to do some more complex calculations). We now have a simple template but I rather pay for someone to do a better, which includes all the functions we need.
We sell packaged travel services so the purpose of the Excel is to be able to calculate package prices by modifying different elements of the package.
1. We need a drop down from which we can choose a car (there will be around 15-30 options), we will have fixed rates assigned for each car for each package but sometimes we also need to modify that car price (when prices change). Once we have chosen the car we need a separate column which now also displays our commission (for example 10% of the total price of the car)
2. Next step is the hotels, 2-4 different in most cases, prices change for these so we need to be able to modify the price and then once all correct hotel prices are there, the second column should now also display the 10% commission of these prices
3. Next step is a margin / service fee which we calculate from the two first elements, so we need to have total car cost + total hotel cost = 100 in total for example, and then for example 10% of 100 will become 10, and the 10 sum also goes into this second column (where our commissions from cars and hotels are displayed)
4. Next step is we have the total cost of the car, the total cost of the hotels, and we have calculated our service fee on top of that. Now we need to calculate our total margin with the elements selected (commission from hotels, commission from cars, and our service fee) and get that amount. Now we need to calculate what is 20% of that amount, and this 20% gets added to the total price.
At this point we should have a total package price at the bottom which consists of cost of car + cost of hotels + our service fee + 20% of our total profit margin that is the commissions+service fee.
And separately (different column probably) a clear way of displaying our total margin as well.
I can do a better explanation / illustration for the one who gets the job, difficult to explain but should not be a very difficult task for someone who works a lot with Excel and can do the dropdowns etc. This is the first job, once I get one template I probably want you to do two more - similar, but slightly modififed calculations.
Was not sure what to put in budget so just name your price for a template like this.