Monthly Profit Calculator
After speaking to my close family friend Sunna about her small business, and how she's struggled to keep track of her income, I offered some assistance in creating a program which could help her with this. My goal in this project was to give her multiple sources of visual aids with explanations, and for myself to become more proficient in working with data frames and visualizing the data with specific types of charts. I used these specific types of charts in order to assist Sunna in making better inferences that track changes over time; so that she could see which weeks and weekdays were most profitable by totals and averages, as well as percentage comparisons for each.
Please Note: I ended up using randomized revenues and costs for project display purposes, in order to keep Sunna's business profits confidential.
Final Product Examples
Part 1: Entering The Data and Table of Contents
For this profit calculator and visualization project, the majority of my coding work was
completed in Jupyter Notebook, and the majority of my documentation, table of contents, and bookmarking was completed in Visual Studio Code. I have also run the code with example data, so the final products of each cell that has been run are also shown.
I've included the majority of my coding and documentation that was completed and displayed in Jupyter Notebook, and I will provide some additional details in the sections throughout this project with further clarification if needed.
This table of contents was created in Visual Studio Code, thanks to the ease of a Bookmarking extension. Each section has a hyperlink that can take you back to the table of contents if needed, and their subsections detail the major portions of code or visualizations in this project.
In the beginning of section 1, I detail which python modules were used in this project, and describe the options available for loading data.
For this optional portion of Section 1, I allow the user to select an xlsx or csv file to load from tkinter's pop up window. If the data is in the correct format, that is shown and explained in the Example Excel or Csv Format step below, then the data can be loaded, and the calculator step can be skipped with a hyperlink below the "Data Check" portion of this cell.
This example formatting step displays the row and column structure, and naming, that is required for a file to be loaded into this calculator. I also explain that Weekday names can have some variance, but must follow a general rule of thumb for this calculator to function properly.
For my custom profit calculator, I tried to be as detailed in the documentation as possible, in order to prevent mistakes or confusion while using it, and to cover all bases. I feel that the documentation may be a bit wordy for a beginner who wants to use this project, however most of these explanations are necessary to avoid errors.
Because I've already run the cells with example data for display purposes, and there are now examples of the finished products at the bottom of each cell, I believe that this will assist a beginner in clarifying what the end product should look like.
In the first step of this calculator, the user will begin by running the cell. A box will ask if you would like to enter data for this portion, and the user can type "yes", "Yes", or "YES" if they would like to proceed. If they would like to skip the selected week, or accidentally ran the cell without meaning to, then they can type anything besides the variants of "yes", and no data that was previously entered into a week-specific dataframe will be overwritten.
After typing in a "yes" variant, text labelled "Current Day: #" will appear, and the user will be prompted to enter the weekday name. The weekday name can only be entered in all lowercase, or capitalization of the first letter of each weekday. Afterwards, the user will be prompted to enter their revenues, and then their costs. When this is completed, a loop will initiate, until all 7 days of data are entered. The newly created dataframe will then be displayed as "Week 1 Data Values:" at the bottom of the cell, so that the user can check their data entry if needed.
This process will be repeated for weeks 2-4. Each weekly data entry is separated into new cells, so week 1 data is entered separate from week 2, week 3, and week 4. If a 5th week is present (29 to 31 days in a month), then this will be addressed in the step further below.
As noted in bold, after entering all data values for the first 4 weeks, the user will now be
prompted to choose between finalizing their data entry for the calculator (if they are in
February with only 28 days), or continuing to the 5th week, which contains only 1-3 days of data.
If the user only has 28 days of data, a blank dataframe with the correct formatting will be created in place of a 5th week's values. Otherwise, a dataframe containing the last few days of the month's data will be created by the user.
After the user enters data for all 4 or 5 weeks of the month, this cell will be run in order to concatenate and combine the separate data frames into one data frame.
I have not displayed section 2's cells, since their tasks are very basic, and I feel that it would add unnecessary length to this webpage. However, I've included a description in the screenshot of what is completed in this section.
Part 2: Backup Creation and Data Comparisons
In section 3, if you entered data through the profit calculator, then you will be very happy to know that your data will now be backed up in a separate csv file. After running the cell, a file named "Summed Results Backup.csv" will be created, and the current working directory will be displayed, in case the user cannot find the file.
At this point in the project, there are two options for loading your backed up csv file.
​
-
You can restart from the initial "Load Excel or Csv Data" in section 1 and choose the "Summed Results Backup" file.
-
You can use this step in section 3 to load the file, if it hasn't been moved from the current working directory.
If you have loaded your backed up file, you will see a "Summed Results" data frame
displayed at the bottom of this cell, as a way for the user to double check that this is the
correct data. Underneath this data frame, other metrics will also be created for you, to give some extra insights into the data that you've loaded. The Mean, Min, Max, and Summed Totals of the Revenues, Costs, and Profits will be displayed. I have not included each of these metrics in the screenshot, to try and keep the picture formatting from downsizing the text and making it more difficult to read.
In section 4, the user can apply a filter to the original data frame into specific weekdays or weeks, for visual comparisons of the data. As an example for the weekdays, the user can enter "Monday" and see the Revenues, Costs, and Profits of every Monday in the month. If the user wants to see this data in a concise way, instead of looking for each weekday or week in the visualizations in section 7, then this section can be of assistance.
As specified above, this portion of section 4 will filter data for the week that the user enters. All weekdays with the week number entered by the user will be displayed for comparisons.
Section 5 is a lengthy section that contains multiple cells which formulate the variables for my graphs and visualizations in sections 6 and 7. I have not included these cells to help keep this webpage less lengthy. However, I have left the section's description of what the formula cells will achieve for the visualizations.
Part 3: Graphs and Results
In section 6, the graphs and visualizations begin for making comparisons of the weekday and weekly data. These graphs are specific to the summed totals and summed averages of profits for each weekday and week number, including pie charts to show the % contribution of each.
For the first example, the Total Profits Per Weekday is shown as a bar chart, which displays the summed profits of all mondays, all tuesdays, etc ... This graph is meant to help show which weekdays were the most and least profitable for the entire month, and potentially help highlight if outside factors or outliers existed for this month, when compared with the other graphs.
This next example, Total Profits Per Week, will show the total summed profits for each
specific week of the month. The 5th week, which may contain only 3 days of data, will more than likely be the least profitable week of every month. However, if this week is matching the profits of other weeks, or is significantly higher than the other weeks, then this graph could be compared with the Total Profits Per Weekday or sections 4 and 7 to troubleshoot the potential issue.
The pie charts here will display the % contributions of each weekday and week number
towards the overall total profits. As stated previously, two things to keep in mind are that the 5th week will most likely have the least % contribution towards the total profits, and the additional weekdays in the 5th week may have a higher % contribution, since they have an extra day's worth of profits.
For the average profit graphs, I've only included the Average Profits Per Weekday as an
example, since these average graphs are nearly identical in display as the Total Profits
graphs. This graph displays the summed and averaged profits for each weekday, which may assist in highlighting outliers as mentioned in the beginning of section 6.
In the beginning of section 7, the profits are displayed for each weekday, based on the week number. I feel that this graph is one of the most helpful in viewing significant differences in profits for each weekday, and for detecting outliers for specific days of the week. For example, the random values that I chose to display show multiple significant differences and/or outliers in profits for days like Tuesday, Wednesday, Saturday, and Sunday.
Below the Profits Per Weekday, Across All Weeks bar graph, there is a facetgrid of line charts that shows a simplified version of the bar graph above it. This facetgrid assists with more easily tracking the differences in profits, and shows the continuity of the data over the time period of each week.
In the last portion of section 7, the total profits are shown based on the week number, per weekday. I feel that this bar graph is useful in showing potential trends of whether the beginning, middle, or end of a week tend to be more or less profitable. This graph also easily highlights which days were included in the 5th week of the month, to be taken into consideration.
For the last graph in my project, I decided to use a heatmap, instead of another facetgrid, to practice working with another type of graph. I feel that heatmaps can be another great visual tool that somewhat combine the traits of a bar graph and pie graph together. You are able to see the weight of each contribution based on the color scheme present, while still keeping a categorical style of display.