Hey there!
I'm migrating some data and processes from Google Sheets to AirTable as it is far more responsive to the frontend of a portal I am developing for my company. Right now, I'm using AirTable as the backend for the quote generator we use for our events. I'm looking to do the following:
- Have a set of Items that may be changed (Ex: Min/Max Price Per Person, Event Discount - Percentage Value, Weekend Markup (A True or False value set if the event happens on a Saturday or Sunday), Amount of Raw Materials that will be priced using fixed amounts based on what I mention in Section 2)
- The Price Per Person will go up a fixed $ amount of $40 if the Weekend Markup is set to True
- Have a set of prices that will virtually never change (Cost of Instructor, Cost of Safety Officer, Cost of Raw Materials)
- Generate a range from Minimum to Maximum price per person to be quoted within
- Generate an "Addon" value of additional costs (Being the total of applied prices that will never change)
- Create a Final "Recommended Quote" that is the average of the Minimum and Maximum Price per Person, with the cost of Add-Ons applied
- Create a currency value of Total Profit based on the Recommended Quote less Costs (All items labeled Costs are what my company would pay to host the event. All items labeled Price are items the event host would pay)
I am wondering how I need to structure my Table (Or make multiple if necessary) in order to make this process work, as well as what formulas to apply and where. I can't share the particulars of the current actual amounts we charge based on cost and prices per person, but I believe I have shared enough information to receive guidance in the right direction.
TL;DR: I need to calculate a quote range, recommended quote which is the average of this range, and a profit total being the recommended quote less costs to my company.
Any guidance would be greatly appreciated!! Sincerely, a Google Sheets refugee.
