data analysis

profilehelp-science20
mis303co1introductionmanagementinformationsystems.zip
Home>Mathematics homework help>Statistics homework help>data analysis

attachment_4 (2).docx

Individual Excel Problem

You should make up your own data for this excel project. There are two examples in a file right under the assignment on blackboard.

Create a workbook to contain your worksheets related to this project. Your workbook and worksheets should look professional in terms of formatting and titles, etc. Date the workbook. Name the workbook Excel Project and your first name. Name each worksheet according to the task you are performing (such as subtotals). Put your name on each worksheet.

Include the following in your worksheets:

Use a separate worksheet to show results of each task. Directly on the worksheet explain each numbered item and worksheet specifically so that I can follow your logic. For example, the worksheet showing functions – what five functions did you use and what is the purpose for each? Explain the data you are using.

1. Use a minimum of five functions in your first worksheet (such as SUM, MIN, etc.)

2. Create a Chart to help visualize your data.

3. Use the sort command on more than one column. Create conditional formatting along with this sort.

4. Use AutoFilter to display a group of records with particular meaning.

5. Use subtotals to highlight subtotals for particular categories.

6. Develop a Pivot Table and Pivot Chart to visualize data in a more meaningful way.

7. Use the If function to return a particular value.

8. Use the Goal Seek command.

9. Submit your workbook on Blackboard so that I can evaluate the cells. Use a text box to explain.

attachment_3 (1).docx

Excel Project Name_________________

Functions

Chart

Sort / Conditional Formatting

Auto Filter

Subtotals

Pivot Table and Pivor Chart

IF Function

Goal Seek

Comments:

attachment_2 (1).xlsx

Data sheet

Thao Chau
ABC Tree Nursery This is the stock list of ABC Nursery at Dec 31 2014
At December 31 2014 Data source from www.pearsonhighered.com/go
Item # Category Tree Name Retail Price Light Landscape Use Quantity in Stock
13129 Oak Golden Oak 108.99 Partial Sun Erosion 78
13358 Oak Columnar English 106.95 Full Shade Border 35
15688 Oak Coral Bark 106.25 Partial Shade Erosion 60
16555 Oak Crimson King 105.50 Full Shade Border 20
26787 Oak Sentry 108.50 Partial Sun Border 90
34793 Oak Emerald Elf 103.98 Full Shade Erosion 113
34982 Oak Fernleaf 105.99 Partial Shade Border 102
37803 Oak Osakazuki 103.88 Full Shade Erosion 173
38675 Oak Palo Alto 102.99 Partial Shade Erosion 75
38700 Oak Pacific Fire 103.75 Full Shade Erosion 93
39704 Oak Embers 105.99 Partial Sun Erosion 58
21683 Cherry Japanese Blooming 103.99 Partial Shade Erosion 75
24896 Cherry Bing Small Sweet 105.99 Partial Shade Border 159
35690 Cherry Bing Sweet 107.99 Partial Sun Erosion 170
38744 Cherry Cheals Weeping 104.99 Partial Shade Erosion 45
37845 Magnolia Anna 117.98 Partial Sun Woodland Garden 113
23677 Maple Black Japanese 107.99 Partial Sun Border 68
25844 Maple Bloodgood 110.99 Partial Shade Border 179
32544 Maple Burgundy Bell 110.95 Partial Sun Border 81
34266 Maple Lace Maple 109.99 Partial Sun Border 81
35988 Maple Butterfly Japanese 111.75 Partial Sun Border 70
36820 Maple Ever Red 110.95 Partial Sun Border 92
23688 Pear Artist Flowering 109.95 Partial Sun Erosion 71
34878 Pear Ginger Pear 107.78 Partial Sun Border 191
25678 Pear Bartlett 109.75 Partial Sun Erosion 60
42599 Pear Beurre 109.98 Partial Sun Border 90
43153 Pear Bradford 104.99 Full Shade Border 350

5 functions

Thao Chau
7/10/22
ABC Tree Nursery
At Dec 31 2014
This is the stock list of ABC Tree Bursery at Dec 31 2014: Using Average function to display the average price of the trees Using Median function to display the median price of the trees Using Min function to display the lowest price of the trees Using Max function to display the highest price of the trees Using Auto Sum function to add the quantity in stock of each tree category and total tree in stock
Total Oak Tree 897
Average Price $ 107.73 Total Cherry Tree 449
Median Price $ 107.78 Total Magnolia Tree 113
Lowest Price $ 102.99 Total Maple Tree 571
Highest Price $ 117.98 Total Pear Tree 762
Total Trees in Stock 2,792
Item # Category Tree Name Retail Price Light Landscape Use Quantity in Stock
13129 Oak Golden Oak 108.99 Partial Sun Erosion 78
13358 Oak Columnar English 106.95 Full Shade Border 35
15688 Oak Coral Bark 106.25 Partial Shade Erosion 60
16555 Oak Crimson King 105.50 Full Shade Border 20
26787 Oak Sentry 108.50 Partial Sun Border 90
34793 Oak Emerald Elf 103.98 Full Shade Erosion 113
34982 Oak Fernleaf 105.99 Partial Shade Border 102
37803 Oak Osakazuki 103.88 Full Shade Erosion 173
38675 Oak Palo Alto 102.99 Partial Shade Erosion 75
38700 Oak Pacific Fire 103.75 Full Shade Erosion 93
39704 Oak Embers 105.99 Partial Sun Erosion 58
21683 Cherry Japanese Blooming 103.99 Partial Shade Erosion 75
24896 Cherry Bing Small Sweet 105.99 Partial Shade Border 159
35690 Cherry Bing Sweet 107.99 Partial Sun Erosion 170
38744 Cherry Cheals Weeping 104.99 Partial Shade Erosion 45
37845 Magnolia Anna 117.98 Partial Sun Woodland Garden 113
23677 Maple Black Japanese 107.99 Partial Sun Border 68
25844 Maple Bloodgood 110.99 Partial Shade Border 179
32544 Maple Burgundy Bell 110.95 Partial Sun Border 81
34266 Maple Lace Maple 109.99 Partial Sun Border 81
35988 Maple Butterfly Japanese 111.75 Partial Sun Border 70
36820 Maple Ever Red 110.95 Partial Sun Border 92
23688 Pear Artist Flowering 109.95 Partial Sun Erosion 71
34878 Pear Ginger Pear 107.78 Partial Sun Border 191
25678 Pear Bartlett 109.75 Partial Sun Erosion 60
42599 Pear Beurre 109.98 Partial Sun Border 90
43153 Pear Bradford 104.99 Full Shade Border 350

Chart of Tree Stock

Thao Chau Thao Chau
ABC Tree Nursery
At Dec 31 2014
Item # Category Tree Name Retail Price Light Landscape Use Quantity in Stock
13129 Oak Golden Oak 108.99 Partial Sun Erosion 78
13358 Oak Columnar English 106.95 Full Shade Border 35
15688 Oak Coral Bark 106.25 Partial Shade Erosion 60
16555 Oak Crimson King 105.50 Full Shade Border 20
26787 Oak Sentry 108.50 Partial Sun Border 90
34793 Oak Emerald Elf 103.98 Full Shade Erosion 113
34982 Oak Fernleaf 105.99 Partial Shade Border 102
37803 Oak Osakazuki 103.88 Full Shade Erosion 173
38675 Oak Palo Alto 102.99 Partial Shade Erosion 75
38700 Oak Pacific Fire 103.75 Full Shade Erosion 93
39704 Oak Embers 105.99 Partial Sun Erosion 58
21683 Cherry Japanese Blooming 103.99 Partial Shade Erosion 75
24896 Cherry Bing Small Sweet 105.99 Partial Shade Border 159
35690 Cherry Bing Sweet 107.99 Partial Sun Erosion 170
38744 Cherry Cheals Weeping 104.99 Partial Shade Erosion 45
37845 Magnolia Anna 117.98 Partial Sun Woodland Garden 113
23677 Maple Black Japanese 107.99 Partial Sun Border 68
25844 Maple Bloodgood 110.99 Partial Shade Border 179
32544 Maple Burgundy Bell 110.95 Partial Sun Border 81
34266 Maple Lace Maple 109.99 Partial Sun Border 81
35988 Maple Butterfly Japanese 111.75 Partial Sun Border 70
36820 Maple Ever Red 110.95 Partial Sun Border 92
23688 Pear Artist Flowering 109.95 Partial Sun Erosion 71
34878 Pear Ginger Pear 107.78 Partial Sun Border 191
25678 Pear Bartlett 109.75 Partial Sun Erosion 60
42599 Pear Beurre 109.98 Partial Sun Border 90
43153 Pear Bradford 104.99 Full Shade Border 350
Total Oak Tree 897 32%
Total Cherry Tree 449 16%
Total Magnolia Tree 113 4%
Total Maple Tree 571 20%
Total Pear Tree 762 27%
Total Tree in Stock 2,792 100%
The chart gives us the portion of each tree category in the total quanity in stock. Oak tree, Pear tree, Maple tree, Cherry tree, and Magnolia ocupies 32%, 27%,20%,16%, and 4% in quantity in stock respectively.
Total Oak Tree Total Cherry Tree Total Magnolia Tree Total Maple Tree Total Pear Tree 0.32127507163323782 0.1608166189111748 4.047277936962751E-2 0.20451289398280803 0.27292263610315187

Autofilter

Thao Chau
ABC Tree Nursery
At Dec 31 2014
Autofilter is used to display the quantity in stock of Cherry Tree with the quantity less than 70, so that it's easy to place new order for this type of tree.
Item # Category Tree Name Retail Price Light Landscape Use Quantity in Stock
13129 Oak Golden Oak 108.99 Partial Sun Erosion 78
13358 Oak Columnar English 106.95 Full Shade Border 35
15688 Oak Coral Bark 106.25 Partial Shade Erosion 60
16555 Oak Crimson King 105.50 Full Shade Border 20
26787 Oak Sentry 108.50 Partial Sun Border 90
34793 Oak Emerald Elf 103.98 Full Shade Erosion 113
34982 Oak Fernleaf 105.99 Partial Shade Border 102
37803 Oak Osakazuki 103.88 Full Shade Erosion 173
38675 Oak Palo Alto 102.99 Partial Shade Erosion 75
38700 Oak Pacific Fire 103.75 Full Shade Erosion 93
39704 Oak Embers 105.99 Partial Sun Erosion 58
21683 Cherry Japanese Blooming 103.99 Partial Shade Erosion 75
24896 Cherry Bing Small Sweet 105.99 Partial Shade Border 159
35690 Cherry Bing Sweet 107.99 Partial Sun Erosion 170
38744 Cherry Cheals Weeping 104.99 Partial Shade Erosion 45
37845 Magnolia Anna 117.98 Partial Sun Woodland Garden 113
23677 Maple Black Japanese 107.99 Partial Sun Border 68
25844 Maple Bloodgood 110.99 Partial Shade Border 179
32544 Maple Burgundy Bell 110.95 Partial Sun Border 81
34266 Maple Lace Maple 109.99 Partial Sun Border 81
35988 Maple Butterfly Japanese 111.75 Partial Sun Border 70
36820 Maple Ever Red 110.95 Partial Sun Border 92
23688 Pear Artist Flowering 109.95 Partial Sun Erosion 71
34878 Pear Ginger Pear 107.78 Partial Sun Border 191
25678 Pear Bartlett 109.75 Partial Sun Erosion 60
42599 Pear Beurre 109.98 Partial Sun Border 90
43153 Pear Bradford 104.99 Full Shade Border 350

Sort & conditional formatting

Thao Chau
ABC Tree Nursery
At Dec 31 2014
Sort command is used in this sheet to sort Category from A to Z, retail price from smallest to largest, and light from A to Z. Conditional formatting is also used in this sheet to highlight trees with quantity in stock less than 70. Combing sort command and conditional formatting help us to point out the tree with low stock so that we can place new order on time.
Item # Category Tree Name Retail Price Light Landscape Use Quantity in Stock
21683 Cherry Japanese Blooming 103.99 Partial Shade Erosion 75
38744 Cherry Cheals Weeping 104.99 Partial Shade Erosion 45
24896 Cherry Bing Small Sweet 105.99 Partial Shade Border 159
35690 Cherry Bing Sweet 107.99 Partial Sun Erosion 170
37845 Magnolia Anna 117.98 Partial Sun Woodland Garden 113
23677 Maple Black Japanese 107.99 Partial Sun Border 68
34266 Maple Lace Maple 109.99 Partial Sun Border 81
32544 Maple Burgundy Bell 110.95 Partial Sun Border 81
36820 Maple Ever Red 110.95 Partial Sun Border 92
25844 Maple Bloodgood 110.99 Partial Shade Border 179
35988 Maple Butterfly Japanese 111.75 Partial Sun Border 70
38675 Oak Palo Alto 102.99 Partial Shade Erosion 75
38700 Oak Pacific Fire 103.75 Full Shade Erosion 93
37803 Oak Osakazuki 103.88 Full Shade Erosion 173
34793 Oak Emerald Elf 103.98 Full Shade Erosion 113
16555 Oak Crimson King 105.50 Full Shade Border 20
34982 Oak Fernleaf 105.99 Partial Shade Border 102
39704 Oak Embers 105.99 Partial Sun Erosion 58
15688 Oak Coral Bark 106.25 Partial Shade Erosion 60
13358 Oak Columnar English 106.95 Full Shade Border 35
26787 Oak Sentry 108.50 Partial Sun Border 90
13129 Oak Golden Oak 108.99 Partial Sun Erosion 78
43153 Pear Bradford 104.99 Full Shade Border 350
34878 Pear Ginger Pear 107.78 Partial Sun Border 191
25678 Pear Bartlett 109.75 Partial Sun Erosion 60
23688 Pear Artist Flowering 109.95 Partial Sun Erosion 71
42599 Pear Beurre 109.98 Partial Sun Border 90

Subtotals

Thao Chau
ABC Tree Nursery
At Dec 31 2014
Subtotals is used in this sheet to display the subtotals of quantity in stock of each tree category.
Item # Category Tree Name Retail Price Light Landscape Use Quantity in Stock
13129 Oak Golden Oak 108.99 Partial Sun Erosion 78
13358 Oak Columnar English 106.95 Full Shade Border 35
15688 Oak Coral Bark 106.25 Partial Shade Erosion 60
16555 Oak Crimson King 105.50 Full Shade Border 20
26787 Oak Sentry 108.50 Partial Sun Border 90
34793 Oak Emerald Elf 103.98 Full Shade Erosion 113
34982 Oak Fernleaf 105.99 Partial Shade Border 102
37803 Oak Osakazuki 103.88 Full Shade Erosion 173
38675 Oak Palo Alto 102.99 Partial Shade Erosion 75
38700 Oak Pacific Fire 103.75 Full Shade Erosion 93
39704 Oak Embers 105.99 Partial Sun Erosion 58
Oak Total 897
21683 Cherry Japanese Blooming 103.99 Partial Shade Erosion 75
24896 Cherry Bing Small Sweet 105.99 Partial Shade Border 159
35690 Cherry Bing Sweet 107.99 Partial Sun Erosion 170
38744 Cherry Cheals Weeping 104.99 Partial Shade Erosion 45
Cherry Total 449
37845 Magnolia Anna 117.98 Partial Sun Woodland Garden 113
Magnolia Total 113
23677 Maple Black Japanese 107.99 Partial Sun Border 68
25844 Maple Bloodgood 110.99 Partial Shade Border 179
32544 Maple Burgundy Bell 110.95 Partial Sun Border 81
34266 Maple Lace Maple 109.99 Partial Sun Border 81
35988 Maple Butterfly Japanese 111.75 Partial Sun Border 70
36820 Maple Ever Red 110.95 Partial Sun Border 92
Maple Total 571
23688 Pear Artist Flowering 109.95 Partial Sun Erosion 71
34878 Pear Ginger Pear 107.78 Partial Sun Border 191
25678 Pear Bartlett 109.75 Partial Sun Erosion 60
42599 Pear Beurre 109.98 Partial Sun Border 90
43153 Pear Bradford 104.99 Full Shade Border 350
Pear Total 762
Grand Total 2792

IF Function

Thao Chau
ABC Tree Nursery
At Dec 31 2014
Using IF function in this sheet helps to check the stock level of each tree. If the stock level is less than 70, then the word "Order" will display in stock level column and company will place new order.
Item # Category Tree Name Retail Price Light Landscape Use Quantity in Stock Stock Level
13129 Oak Golden Oak 108.99 Partial Sun Erosion 78 OK
13358 Oak Columnar English 106.95 Full Shade Border 35 Order
15688 Oak Coral Bark 106.25 Partial Shade Erosion 60 Order
16555 Oak Crimson King 105.50 Full Shade Border 20 Order
26787 Oak Sentry 108.50 Partial Sun Border 90 OK
34793 Oak Emerald Elf 103.98 Full Shade Erosion 113 OK
34982 Oak Fernleaf 105.99 Partial Shade Border 102 OK
37803 Oak Osakazuki 103.88 Full Shade Erosion 173 OK
38675 Oak Palo Alto 102.99 Partial Shade Erosion 75 OK
38700 Oak Pacific Fire 103.75 Full Shade Erosion 93 OK
39704 Oak Embers 105.99 Partial Sun Erosion 58 Order
21683 Cherry Japanese Blooming 103.99 Partial Shade Erosion 75 OK
24896 Cherry Bing Small Sweet 105.99 Partial Shade Border 159 OK
35690 Cherry Bing Sweet 107.99 Partial Sun Erosion 170 OK
38744 Cherry Cheals Weeping 104.99 Partial Shade Erosion 45 Order
37845 Magnolia Anna 117.98 Partial Sun Woodland Garden 113 OK
23677 Maple Black Japanese 107.99 Partial Sun Border 68 Order
25844 Maple Bloodgood 110.99 Partial Shade Border 179 OK
32544 Maple Burgundy Bell 110.95 Partial Sun Border 81 OK
34266 Maple Lace Maple 109.99 Partial Sun Border 81 OK
35988 Maple Butterfly Japanese 111.75 Partial Sun Border 70 OK
36820 Maple Ever Red 110.95 Partial Sun Border 92 OK
23688 Pear Artist Flowering 109.95 Partial Sun Erosion 71 OK
34878 Pear Ginger Pear 107.78 Partial Sun Border 191 OK
25678 Pear Bartlett 109.75 Partial Sun Erosion 60 Order
42599 Pear Beurre 109.98 Partial Sun Border 90 OK
43153 Pear Bradford 104.99 Full Shade Border 350 OK

Pivot Table

Thao Chau
Category (All)
Sum of Quantity in Stock Light
Landscape Use Full Shade Partial Shade Partial Sun Partial Sun Grand Total
Border 405 440 682 81 1608
Erosion 379 255 437 1071
Woodland Garden 113 113
Grand Total 784 695 1232 81 2792
Using Pivot Table and Pivot Chart in this worksheet help us visualize the quantity in stock of each tree category particularly in landscape use and how much light they provide

Thao-Pivot Chart

Full Shade Border Erosion Woodland Garden 405 379 Partial Shade Border Erosion Woodland Garden 440 255 Partial Sun Border Erosion Woodland Garden 682 437 113 Partial Sun Border Erosion Woodland Garden 81

Goal Seek

Thao Chau
HOUSE LOAN I want to buy a house. I can afford down payment $20,000, and monthly payment $1,500. With the interest rate 4% and the period in 15 years, how much is the house I can buy? Using Goal Seek command to solve this problem. The result displays that I can buy a house with price $222,788
Purchased price $ 222,788
Down payment $ 20,000
Amount of loan $ 202,788
Period (years) 15
Interest rate (per year) 4.00%
Payment (per month) $1,500.00

Data sheet for Solver

Thao Chau
Evening Shift Server Schedule Data source from www.pearsonhighered.com/go
A manager of a restaurant want to schedule the evening shift for her employee. Her goal is to minimize the weekly payroll expense and each employee can have 2 day off per week . She observed the quantity of customers who came to her restaurant every night and came up with the number of employees she needed for each night. She has 5 schedules named A to F. She marks 0 for the day off and 1 for the day working in the weekly schedule table. The number of employees she schedules must be equal or larger than the one she demands. She uses Solver to solve this problem.
Schedule Day off Number of employees Mon Tue Wed Thu Fri Sat Sun
A Mon, Tue 0 0 1 1 1 1 1
B Tue, Wed 1 0 0 1 1 1 1
C Wed, Thu 1 1 0 0 1 1 1
D Thu, Fri 1 1 1 0 0 1 1
E Sat, Sun 1 1 1 1 1 0 0
F Sun, Mon 0 1 1 1 1 1 0
Schedule Totals 0 0 0 0 0 0 0 0
Total Demand (Constraint) 18 14 15 19 27 27 26
Wage per hour: $ 9.00
Number of hours working per day 8
Weekly wage per server $ 360.00
Weekly payroll expense $ - 0

Solver

Thao Chau
Evening Shift Server Schedule
The minimum weekly payroll expense is $10,800.00
Schedule Day off Number of employees Mon Tue Wed Thu Fri Sat Sun
A Mon, Tue 11 0 0 1 1 1 1 1
B Tue, Wed 4 1 0 0 1 1 1 1
C Wed, Thu 11 1 1 0 0 1 1 1
D Thu, Fri 0 1 1 1 0 0 1 1
E Sat, Sun 3 1 1 1 1 1 0 0
F Sun, Mon 1 0 1 1 1 1 1 0
Schedule Totals 30 18 15 15 19 30 27 26
Total Demand 18 14 15 19 27 27 26
Wage per hour: $ 9.00
Number of hours working per day 8
Weekly wage per server $ 360.00
Weekly payroll expense $ 10,800.00

Answer Report 1

Microsoft Excel 14.0 Answer Report
Worksheet: [Thao - Excel Project.xlsx]Solver
Report Created: 9/24/2015 7:20:55 PM
Result: Solver found a solution. All Constraints and optimality conditions are satisfied.
Solver Engine
Engine: GRG Nonlinear
Solution Time: 0.265 Seconds.
Iterations: 0 Subproblems: 14
Solver Options
Max Time Unlimited, Iterations Unlimited, Precision 0.000001, Use Automatic Scaling
Convergence 0.0001, Population Size 100, Random Seed 0, Derivatives Forward, Require Bounds
Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume NonNegative
Objective Cell (Min)
Cell Name Original Value Final Value
$D$21 Weekly payroll expense Mon $ 10,800.00 $ 10,800.00
Variable Cells
Cell Name Original Value Final Value Integer
$C$6:$C$11
$C$6 Mon__Tue 10 11 Integer
$C$7 Tue__Wed 5 4 Integer
$C$8 Wed__Thu 10 11 Integer
$C$9 Thu__Fri 1 0 Integer
$C$10 Sat__Sun 2 3 Integer
$C$11 Sun__Mon 2 1 Integer
Constraints
Cell Name Cell Value Formula Status Slack
$D$13:$J$13 >= $D$15:$J$15
$D$13 Schedule Totals Mon 18 $D$13>=$D$15 Binding 0
$E$13 Schedule Totals Tue 15 $E$13>=$E$15 Not Binding 1
$F$13 Schedule Totals Wed 15 $F$13>=$F$15 Binding 0
$G$13 Schedule Totals Thu 19 $G$13>=$G$15 Binding 0
$H$13 Schedule Totals Fri 30 $H$13>=$H$15 Not Binding 3
$I$13 Schedule Totals Sat 27 $I$13>=$I$15 Binding 0
$J$13 Schedule Totals Sun 26 $J$13>=$J$15 Binding 0
$C$6:$C$11 >= 0
$C$6 Mon__Tue 11 $C$6>=0 Binding 0
$C$7 Tue__Wed 4 $C$7>=0 Not Binding 4
$C$8 Wed__Thu 11 $C$8>=0 Not Binding 11
$C$9 Thu__Fri 0 $C$9>=0 Binding 0
$C$10 Sat__Sun 3 $C$10>=0 Not Binding 3
$C$11 Sun__Mon 1 $C$11>=0 Not Binding 1
$C$6:$C$11=Integer

Scenario Summary

Thao Chau
Using the same data sheet with Solver The manager of the restaurant already had the best solution for her problem. But she wants to know how much wage expense change if she decreases or increases the number of employee in each schedule. She uses Scenario Manager to compare the changes in each option. Option 1 is her best solution. For option 2, she increases one employee in schedule Thu_Fri and the expense is inceased. For option 3, she decreases two employees in schedule Wed_Thu and expense is decreased.
Scenario Summary
Current Values: Option 1 Option 2 Option 3
Created by Datco on 9/18/2015 Created by Datco on 9/18/2015 Created by Datco on 9/18/2015
Changing Cells:
Mon__Tue 10 10 10 10
Tue__Wed 5 5 5 5
Wed__Thu 10 10 10 8
Thu__Fri 1 1 2 1
Sat__Sun 2 2 2 2
Sun__Mon 2 2 2 2
Result Cells:
$D$19 $ 10,800.00 $ 10,800.00 $ 11,160.00 $ 10,080.00
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.

attachment_1 (1).xlsx

Now, Sum, Min, Max, Average, If

Profit Per Service as of: 7/10/22 15:29
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
Board Lisa Pam Mary Brenda 450 50 175 0 Lessons Lisa Pam Mary Brenda 1740 960 3480 960 Training Lisa Pam Mary Brenda 1680 280 0 560

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
Board First Second Third Fourth 175 175 150 175 Lessons First Second Third Fourth 1920 1260 2040 1920 Training First Second Third Fourth 700 560 560 700

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.