Help with Sales records with Orders, customers, employees, & services (and calculator)

AppSheet AppSheet Q&A
, ,
1

So i’m not to new to Google Sheets but very new to Appsheet.
this

This is a pretty complicated (yet not as complicated as some apps out there,

my idea is as follows & just wondering best way i should setup.

This following information is fictional & used for virtual business (don’t ask, GTAV Roleplay business)

currently i use a single spreadsheet for calculating total business costs (combination of multiple costs), then i have a markup (Variable 50-100%) then an option for discount (Variable 0%-20%), Price Adjustments to round to closest $10 then total calculates into Customer Price & Profit.
Right now i have all the formulas automate once i input the multiple business costs.

once i have the completed calculation i copy results of a row
Business Cost, Markup, Discount, Price Adjustments (for rounding), Customer Price, Profit

Below the calculation i have Columns;
Date, Customer Name, Vehicle, Number Plate, Colour in, Colour Out, Staff (Y/N) then i paste the above results.

Date: 15-12-19
Customer Name: Mark Johnson
Vehicle: Ranger
License Plate: KSL425
Colour In: Grey
Colour Out: Red
Staff: No
Business Cost (total calculation): $18,134
Markup (250%): $45,335
Discount (0%): $45,335
Price Adjustment (amount rounded up): $65
Customer Price: $45,400
Profit: $27,266

Collections = Orders, Employee, Customers, Services (or Product doesn’t really matter)
there would be set Services; brakes, engine, suspension, transmission with a manual price input per order

Before creating an order i create a new Customer,
Then create the Order, Select employee who is creating new order
add whichever Services the Customer wants & when i select a service i input Cost
once all services selected the Order shows the optioin for Markup & Discount & Calculates a total.

AND just to throw another option… having an option of status Quote(defaulting to $200) or Paid if first quoted the Paid price deducts $200 from total price)

To give you an idea of what i use right now (google sheet link, no idea if it works)

i understand if this is too much to ask, but i have spent days trying to get something to work and keep frustrating myself and deleting & starting again. i have tried using and editing samples, to creating with an idea, to starting with data sheets.

i know i should probably start simple, like single form with inputs. but if i was just doing that i might as well use Google Forms (which i have done).

Anyway Enjoy,

2

oh and apologies for the messy post

3

Hi @Hypnotech03
Have you tried making an App from your form or sheet?

3 Likes
4

That I did and for the small part it was basic with no cost or price calculations…
BUT
I had an epiphany last night and just figured I could use a google form/sheet for each step. Knowing yes this would work, but still not the system I wanted.

Form 1 = Create new customer
Form 2 = Create vehicle (selecting customer as owner, using Google code to create the dropdown options from previous Form answer.)
Form 2 = Create order, Add Customer &/or Vehicle
Form 3 = Create/Select Service, Input custom price for each service,

The use a master Document and collect the data and formulate the costs markups and profits etc. from all 4 of those sheets to have a complete list of jobs prices and customer details.

I would though like to look at the sheets with a dashboard & Data sheets where I can easily see Employee commission (% of Profit) and all other stats Total quotes. Total sales, total jobs.

But yeah I’m thinking big early…
By basic spreadsheet system has been used now for over 16 months with over 3,450 entries in that time.
Each employee has their own copy of document, then I have a business report pulling their commision/profits, with =importrange which needs updating (by me)each week with a new sheet.