Price Calculator Customer facing page

Price Calculator
Customer facing page:

Three D Media Logo on top

Input boxes:

Project name

Upload Excel spreadsheet or link to google sheets doc. [upload]

Desired completion date [ xx/xx/xxxx ]

[Calculate]

Results page:

Map showing dots of all addresses and grouping dots by dates proposed service

Estimated Cost [ $xx,xxx ] with no overtime | [ $xx,xxx ] with overtime

Estimated Completion date [ xx/xx/xxxx ] with no overtime | [ xx/xx/xxxx] with overtime

Suggested Number of people on the field [ x ]

Total Estimated Sqft [ xxx,xxx ] *expand button, shows list of homes with pulled editable sqft in case data was not available for some or imported incorrectly

Total # of units

Total # of floors [ xxx ]

Total Miles [ xx,xxx ]

Per diem costs (based on central zip code of the project pulled from https://www.gsa.gov/travel/plan-book/per-diem-rates) [ $xxx.xx per day or $xxxxx.xx per project ]

Admin facing page:

Input boxes:

1. 
   [ xx ] Labor rate
   
2. 
   [ xx ] Cost per mile
   
3. 
   [ xx ] Adjustable per diem cost if automatic is not accurate
   

Display:

Project names

Each project will display all data from custom inputs and formula calculation. Manual approval button that will unlock prices to customer and send them email notification with official quote.

What happens behind the scenes:

1. 
   Import spreadsheet will have 2 rows, 1 with address and 2 with 0/1 value for photography.
   
2. 
   Use row 1 with addresses to pull interior SQFT per unit, Lot SQFT per unit, and number of floors per unit. Zillow API
   
3. 
   Use row 1 with addresses to map out all locations,
   
4. 
   Pick a hotel in the center of all locations
   
5. 
   Calculate optimal routes per day with and without overtime (8- or 12-hour shifts), calculate total travel distance in miles (add 30% for inefficacy). Google Maps API
   
6. 
   Use Interior SQFT size data from each unit / 34 (if value in row 2 of spreadsheet is 1, change 34 to 20) = [ xxx ] # of minutes required for each unit
   
7. 
   Use Lot SQFT size data / 2000 (if value in row 2 of spreadsheet is 1, change 2000 to 1000) = [ xxx ] # of minutes required
   
8. 
   Use number of floors -1 ) x 5 = # of minutes (if value in row 2 of spreadsheet is 1, change 5 to 7) / 60 = [ xxx ] # of hours required rounded off by +1
   
9. 
   Add results from 4, 5, 6 to determine total # of labor hours
   
10. 
    Use total miles / average MPH # in the geographic area pulled from Google Maps / 60 = # of minutes required for traveling between appointments
    
11. 
    Calculate how days it will take for 1 person to complete the work using the result from 7 + 8 based on normal hours (8 hours per day, 5 days per week). If the estimated completion date exceeds desired completion date, suggest either 2 people at standard hours or 1 person in overtime schedule (up to 12 hours per day, up to 6 days per week), try to calculate lowest cost using per diem and extra cost of overtime.
    
12. 
    Internal Cost calculation formula = (Regular hours x labor rate [editable by admin ] + overtime hours x 1.5 x labor rate) + (total miles x cost per mile [editable]) + per diem cost from [calculated by pulling zip code from central point and pulled from] https://www.gsa.gov/travel/plan-book/per-diem-rates)
    
13. 
    Customer Cost calculation =
    
    1. 
       internal cost x 2
       
    2. 
       Minimum project cost = total number of units x $160 + total number of from row 2 of spreadsheet x $160 = minimum project cost
       
    3. 
       Maximum project cost = total number of units x $220 + total number of from row 2 of spreadsheet x $220 = minimum project cost
       
    4. 
       Show cost from line A if it falls between B and C, otherwise show either minimum or maximum project cost.