excel

profileSam321
CopyofExcelProjectExample1.xlsx
Home>Computer Science homework help>excel

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
9/5/17
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 Gar den 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.