Our company is trying to build a report app to show the profitability of each service route based on the customer info and some of our past data. We are a waste management company in Vancouver. We pick up clients' waste everyday and that is our major activity. In our company, there are plenty clients on each route where the clients are served, and in order to maintain efficiency or to deal with some occasional incidence, the COO often adjusts the route for some of the clients. The pic below shows the client App:
As you can see in the app, every client can be served on several routes and some of the companies are not a client yet. In the report app, we need to sum up the monthly sales field, but not all of the items. Instead, we want to sum up only for the companies that the "Client Status" is "Client!:D" and sum up by routes. Similarly, we want to sum up the total number of bins on each route, which will become a factor of our variable cost. Please keep in mind, the "Route" field in this app is a category field.
As you know, profit=revenue-cost. In our case, after getting the revenue and one variable cost factor(# of bins) for each route from the previous app, we will get other variable cost factors from another app called "Pre/Post Vehicle Inspection" where the travel information of each route is stored. The pic below is the content:
In this app, every truck driver inputs the odometer reading and route info before and after their daily service. The "Date" field records the beginning and ending time of a driver's work. "Route/Vehicle" field indicates which route it is. We want to use the report app to get the total travel distance and hours for each route in a specific period which can be selected by the user.
In the last step, the report app will calculate the estimated profit for each route by using the formula below:
Profit for route A = monthly revenue of route A - bag cost of route A (number of bins * bag unit cost) - gas cost of route A (travel distance * gas rate) - driver wage of route A ( travel hours * wage rate).
I am currently using Excel to do all of this work, but we want to make it online and easier for the users. So, if you have any suggestions or idea, please let me know. If you'd like to help us out, we are open to receive a quote at firstname.lastname@example.org. Thank you very much!!!
Please sign in to leave a comment.