Quote Generation Based on Fixed Prices, With Multiple Inputs/Outputs

Topic Labels: Base design Data
615 1
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

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:

  1. 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)
    1. The Price Per Person will go up a fixed $ amount of $40 if the Weekend Markup is set to True
  2. Have a set of prices that will virtually never change (Cost of Instructor, Cost of Safety Officer, Cost of Raw Materials)
  3. Generate a range from Minimum to Maximum price per person to be quoted within
  4. Generate an "Addon" value of additional costs (Being the total of applied prices that will never change)
  5. Create a Final "Recommended Quote" that is the average of the Minimum and Maximum Price per Person, with the cost of Add-Ons applied
  6. 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.

1 Reply 1
18 - Pluto
18 - Pluto

This can be challenging to setup in Airtable since it’s not as powerful as Excel in these ways, but it can likely be done with scripting or by building your own no-code automations with Make.

There’s not a quick answer that I can give you in a post for how to setup an entire system like this, but maybe I’ll create a YouTube video at some point to train people on how to setup a system like this in Airtable.

In the meantime, if you have a budget for your project and you’d like to hire an expert Airtable consultant to help you with any of this, please feel free to contact me through my website: Airtable consulting — ScottWorld