excel
Sam321
CopyofExcelIndiviualProject2.xlsxNow, Sum, Min, Max, Average, If
| Profit Per Service as of: | 9/5/17 21:42 | ||
| Income | Expense | Profit | |
| Board | 600 | 575 | 25 |
| Lessons | 75 | 15 | 60 |
| Training | 75 | 40 | 35 |
| Total Profit: | 120 | ||
| Minimum Profit: | 25 | ||
| Maximum Profit: | 60 | ||
| Average: | 40 | ||
| Congratulations! You are making a profit! |
Sort, Conditional Formatting
| Quarter | Client | Service | Quantity | Profit |
| First | Lisa | Board | 5 | 125 |
| Second | Lisa | Board | 5 | 125 |
| Third | Lisa | Board | 4 | 100 |
| Fourth | Lisa | Board | 4 | 100 |
| First | Mary | Board | 1 | 25 |
| Second | Mary | Board | 2 | 50 |
| Third | Mary | Board | 2 | 50 |
| Fourth | Mary | Board | 2 | 50 |
| First | Pam | Board | 1 | 25 |
| Fourth | Pam | Board | 1 | 25 |
| First | Brenda | Lessons | 4 | 240 |
| Second | Brenda | Lessons | 4 | 240 |
| Third | Brenda | Lessons | 4 | 240 |
| Fourth | Brenda | Lessons | 4 | 240 |
| First | Lisa | Lessons | 8 | 480 |
| Second | Lisa | Lessons | 7 | 420 |
| Third | Lisa | Lessons | 6 | 360 |
| Fourth | Lisa | Lessons | 8 | 480 |
| First | Mary | Lessons | 12 | 720 |
| Second | Mary | Lessons | 10 | 600 |
| Third | Mary | Lessons | 24 | 1440 |
| Fourth | Mary | Lessons | 12 | 720 |
| First | Pam | Lessons | 8 | 480 |
| Fourth | Pam | Lessons | 8 | 480 |
| First | Brenda | Training | 4 | 140 |
| Second | Brenda | Training | 4 | 140 |
| Third | Brenda | Training | 4 | 140 |
| Fourth | Brenda | Training | 4 | 140 |
| First | Lisa | Training | 12 | 420 |
| Second | Lisa | Training | 12 | 420 |
| Third | Lisa | Training | 12 | 420 |
| Fourth | Lisa | Training | 12 | 420 |
| First | Pam | Training | 4 | 140 |
| Fourth | Pam | Training | 4 | 140 |
Subtotals
| Quarter | Client | Service | Quantity | Profit |
| First | Lisa | Board | 5 | 125 |
| First | Mary | Board | 1 | 25 |
| First | Pam | Board | 1 | 25 |
| First | Brenda | Lessons | 4 | 240 |
| First | Lisa | Lessons | 8 | 480 |
| First | Mary | Lessons | 12 | 720 |
| First | Pam | Lessons | 8 | 480 |
| First | Brenda | Training | 4 | 140 |
| First | Lisa | Training | 12 | 420 |
| First | Pam | Training | 4 | 140 |
| First Total | 2795 | |||
| Second | Lisa | Board | 5 | 125 |
| Second | Mary | Board | 2 | 50 |
| Second | Brenda | Lessons | 4 | 240 |
| Second | Lisa | Lessons | 7 | 420 |
| Second | Mary | Lessons | 10 | 600 |
| Second | Brenda | Training | 4 | 140 |
| Second | Lisa | Training | 12 | 420 |
| Second Total | 1995 | |||
| Third | Lisa | Board | 4 | 100 |
| Third | Mary | Board | 2 | 50 |
| Third | Brenda | Lessons | 4 | 240 |
| Third | Lisa | Lessons | 6 | 360 |
| Third | Mary | Lessons | 24 | 1440 |
| Third | Brenda | Training | 4 | 140 |
| Third | Lisa | Training | 12 | 420 |
| Third Total | 2750 | |||
| Fourth | Lisa | Board | 4 | 100 |
| Fourth | Mary | Board | 2 | 50 |
| Fourth | Pam | Board | 1 | 25 |
| Fourth | Brenda | Lessons | 4 | 240 |
| Fourth | Lisa | Lessons | 8 | 480 |
| Fourth | Mary | Lessons | 12 | 720 |
| Fourth | Pam | Lessons | 8 | 480 |
| Fourth | Brenda | Training | 4 | 140 |
| Fourth | Lisa | Training | 12 | 420 |
| Fourth | Pam | Training | 4 | 140 |
| Fourth Total | 2795 | |||
| Grand Total | 10335 |
AutoFilter
| Quarter | Client | Service | Quantity | Profit |
| First | Lisa | Board | 5 | 125 |
| Second | Lisa | Board | 5 | 125 |
| Third | Lisa | Board | 4 | 100 |
| Fourth | Lisa | Board | 4 | 100 |
| First | Mary | Board | 1 | 25 |
| Second | Mary | Board | 2 | 50 |
| Third | Mary | Board | 2 | 50 |
| Fourth | Mary | Board | 2 | 50 |
| First | Pam | Board | 1 | 25 |
| Fourth | Pam | Board | 1 | 25 |
| First | Brenda | Lessons | 4 | 240 |
| Second | Brenda | Lessons | 4 | 240 |
| Third | Brenda | Lessons | 4 | 240 |
| Fourth | Brenda | Lessons | 4 | 240 |
| First | Lisa | Lessons | 8 | 480 |
| Second | Lisa | Lessons | 7 | 420 |
| Third | Lisa | Lessons | 6 | 360 |
| Fourth | Lisa | Lessons | 8 | 480 |
| First | Mary | Lessons | 12 | 720 |
| Second | Mary | Lessons | 10 | 600 |
| Third | Mary | Lessons | 24 | 1440 |
| Fourth | Mary | Lessons | 12 | 720 |
| First | Pam | Lessons | 8 | 480 |
| Fourth | Pam | Lessons | 8 | 480 |
| First | Brenda | Training | 4 | 140 |
| Second | Brenda | Training | 4 | 140 |
| Third | Brenda | Training | 4 | 140 |
| Fourth | Brenda | Training | 4 | 140 |
| First | Lisa | Training | 12 | 420 |
| Second | Lisa | Training | 12 | 420 |
| Third | Lisa | Training | 12 | 420 |
| Fourth | Lisa | Training | 12 | 420 |
| First | Pam | Training | 4 | 140 |
| Fourth | Pam | Training | 4 | 140 |
Chart
| Total Profit Per Client | ||||
| Board | Lessons | Training | Total | |
| Lisa | $ 450.00 | $ 1,740.00 | $ 1,680.00 | $ 3,870.00 |
| Pam | $ 50.00 | $ 960.00 | $ 280.00 | $ 1,290.00 |
| Mary | $ 175.00 | $ 3,480.00 | $ - 0 | $ 3,655.00 |
| Brenda | $ - 0 | $ 960.00 | $ 560.00 | $ 1,520.00 |
Pivot Table, Pivot Chart
| Client | (All) | |||
| Sum of Profit | Column Labels | |||
| Row Labels | Board | Lessons | Training | Grand Total |
| First | 175 | 1920 | 700 | 2795 |
| Second | 175 | 1260 | 560 | 1995 |
| Third | 150 | 2040 | 560 | 2750 |
| Fourth | 175 | 1920 | 700 | 2795 |
| Grand Total | 675 | 7140 | 2520 | 10335 |
Solver
| I want my horse business to make as much as it can every month. Below is how much profit I make from each service. My barn is full (10 horses). In Solver B, I can't offer training and lessons for more than 112 hours per month because the barn takes 140 hours. In Solver A, I have hired someone to do the barn so I can have more time to offer services. This give me 240 hours to offer either training or lessons. | |||||||||||||
| Income Per Month with 1 Employee (Solver A) | Income Per Month Only Self (Solver B) | ||||||||||||
| Profit Per Service | Time Per Service | Profit Per Service | Time Per Service | ||||||||||
| Lessons | $ 60.00 | Lessons | 1 | hours | Lessons | $ 60.00 | Lessons | 1 | hours | ||||
| Training | $ 35.00 | Training | 0.75 | hours | Training | $ 35.00 | Training | 0.75 | hours | ||||
| Board | $ 25.00 | Board | $ 25.00 | ||||||||||
| Constraints | Constraints | ||||||||||||
| Board = 10 horses | Board = 10 horses | ||||||||||||
| Lessons + Training Time <= 240 hours | Lessons + Training Time <= 150 hours | ||||||||||||
| Number of Services | Total Time of Services | Number of Services | Total Time of Services | ||||||||||
| Lessons | 240 | Lessons | 240 | hours | Lessons | 150 | Lessons | 150 | hours | ||||
| Training | 0 | Training | 0 | hours | Training | 0 | Training | 0 | hours | ||||
| Board | 10 | Total Time | 240 | hours | Board | 10 | Total Time | 150 | hours | ||||
| Lessons+Training | 240 | Lessons+Training | 150 | ||||||||||
| Maximum Profit: | $ 14,650.00 | Maximum Profit: | $ 9,250.00 |
Goal Seek
| I want to buy a truck and trailer to be able to haul any horses to the vet in case of an emergency. I don't want my montly payment to be more than $600 a month, and my loan would be for 36 months with a 4.5% interest rate. What is the most I can spend on a truck and trailer? | ||||
| Maximum Monthly Payment: | $600 | |||
| Interest Rate: | 4.50% | |||
| Loan Term: | 36 | Months | ||
| Price of Truck and Trailer Combined: | $20,669.86 |
Scenario Data
| I'm thinking about hiring some contracted help for the barn to free up some more time to offer my services. I want to know if it will be profitable or not to do so. I am paying myself 25% of what the business makes. It takes 5 hours a day to do "barn work" with 10 horses. | |||||
| # of Employees including self | 2 | Profit Per Service | # of Each Service (Using Solver) | ||
| Barn Hours | 150 | Lessons | $ 60.00 | 240 | |
| Service Hours | 240 | Training | $ 35.00 | 0 | |
| Barn Hour $/Hour | $ 7.00 | Board | $ 25.00 | 10 | |
| Service Hours Pay | $ 3,662.50 | ||||
| Total Cost Per Month | $ 4,712.50 | ||||
| Total Income Per Month | $ 14,650.00 | ||||
| Total Profit Per Month | $ 9,937.50 |
Scenario Summary
| "To Contract Help or Not?" Scenario Summary | ||||||
| Current Values: | Only Self | One Employee @ $10/hr | One Employee @ $7/hr | |||
| Created by Kaitlin on 9/25/2015 Modified by Kaitlin on 9/25/2015 | Created by Kaitlin on 9/25/2015 Modified by Kaitlin on 9/25/2015 | Created by Kaitlin on 9/25/2015 Modified by Kaitlin on 9/25/2015 | ||||
| Changing Cells: | ||||||
| Number of Employees Incl. Self | 2 | 1 | 2 | 2 | ||
| Barn Hours | 150 | 150 | 150 | 150 | ||
| Service Hours | 240 | 150 | 240 | 240 | ||
| $/Hour for Barn Hours | $ 7.00 | $ - 0 | $ 10.00 | $ 7.00 | ||
| Number of Lessons | 240 | 150 | 240 | 240 | ||
| Number of Training Rides | 0 | 0 | 0 | 0 | ||
| Number of Horses | 10 | 10 | 10 | 10 | ||
| Result Cells: | ||||||
| Total Expenses | $ 4,712.50 | $ 2,312.50 | $ 5,162.50 | $ 4,712.50 | ||
| Total Income | $ 14,650.00 | $ 9,250.00 | $ 14,650.00 | $ 14,650.00 | ||
| Total Profit | $ 9,937.50 | $ 6,937.50 | $ 9,487.50 | $ 9,937.50 | ||
| Notes: Current Values column represents values of changing cells at | ||||||
| time Scenario Summary Report was created. Changing cells for each | ||||||
| scenario are highlighted in gray. |
