ComputerScienceExpert
$18/per page/
Hello, I have an excel project due at midnight tonight. I have attached the excel file and the instructions. please let me know if anyone can help
You must have the most recent excel version.
Fairways Woods Company Shipping Analysis Plants
Denver
Phoenix
Dallas
Charlotte
Totals: Total
0
0
0
0 Demands by Whse -->
Plants
Supply
Denver
280
Phoenix
220
Dallas
200
Charlotte
150
$0
Shipping: Number to ship from plant to warehouse:
Sacramento Salt Lake
Chicago
Albeq
New York
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0 180
80
200
160
Shipping costs from plant to warehouse:
$6
$3
$6
$6
$6
$4
$7
$4
$4
$7
$5
$9
$7
$8
$6
$6
$0
$0
$0
$0 THIS is the field
you want to
minimize using
Solver. 220
$4
$6
$6
$5
$0 THESE (in
yellow) are the
fields that that
you want to
change THESE (in
yellow) are the
fields that that
you want to
change OrderDate ProductID
ProductName
04-Jan-09
22
Mozzarella cheese
04-Jan-09
16
Romaine lettuce
05-Jan-09
30
Red onions
06-Jan-09
16
Romaine lettuce
07-Jan-09
2
Black olives
07-Jan-09
16
Romaine lettuce
07-Jan-09
16
Romaine lettuce
08-Jan-09
30
Red onions
09-Jan-09
16
Romaine lettuce
10-Jan-09
16
Romaine lettuce
10-Jan-09
39
Pineapple
11-Jan-09
39
Pineapple
14-Jan-09
16
Romaine lettuce
14-Jan-09
16
Romaine lettuce
15-Jan-09
85
Pepperoni
15-Jan-09
100
Parmesan cheese
16-Jan-09
100
Parmesan cheese
18-Jan-09
100
Parmesan cheese
18-Jan-09
16
Romaine lettuce
20-Jan-09
4
Tomatoes
21-Jan-09
31
Peppers
21-Jan-09
22
Mozzarella cheese
23-Jan-09
2
Black olives
24-Jan-09
22
Mozzarella cheese
26-Jan-09
16
Romaine lettuce
27-Jan-09
100
Parmesan cheese
28-Jan-09
31
Peppers
28-Jan-09
22
Mozzarella cheese
29-Jan-09
4
Tomatoes
29-Jan-09
31
Peppers
29-Jan-09
16
Romaine lettuce
29-Jan-09
30
Red onions
31-Jan-09
100
Parmesan cheese
31-Jan-09
16
Romaine lettuce
02-Feb-09
30
Red onions
02-Feb-09
16
Romaine lettuce
04-Feb-09
39
Pineapple
05-Feb-09
22
Mozzarella cheese
05-Feb-09
16
Romaine lettuce Quantity
41
90
27
67
79
46
52
39
66
58
40
71
18
28
33
14
72
10
42
48
29
10
98
45
58
66
85
12
40
5
49
13
38
51
85
82
64
22
74 Unit Price
$ 24.00
$ 15.00
$ 12.00
$ 15.00
$ 12.00
$ 15.00
$ 15.00
$ 12.00
$ 15.00
$ 15.00
$ 33.00
$ 33.00
$ 15.00
$ 15.00
$ 53.00
$
8.00
$
8.00
$
8.00
$ 15.00
$
9.00
$ 21.00
$ 24.00
$ 12.00
$ 24.00
$ 15.00
$
8.00
$ 21.00
$ 24.00
$
9.00
$ 21.00
$ 15.00
$ 12.00
$
8.00
$ 15.00
$ 12.00
$ 15.00
$ 33.00
$ 24.00
$ 15.00 Total Sales
$
984.00
$ 1,350.00
$
324.00
$ 1,005.00
$
948.00
$
690.00
$
780.00
$
468.00
$
990.00
$
870.00
$ 1,320.00
$ 2,343.00
$
270.00
$
420.00
$ 1,749.00
$
112.00
$
576.00
$
80.00
$
630.00
$
432.00
$
609.00
$
240.00
$ 1,176.00
$ 1,080.00
$
870.00
$
528.00
$ 1,785.00
$
288.00
$
360.00
$
105.00
$
735.00
$
156.00
$
304.00
$
765.00
$ 1,020.00
$ 1,230.00
$ 2,112.00
$
528.00
$ 1,110.00 Unit Cost
$
18.00
$
14.00
$
8.00
$
14.00
$
6.00
$
14.00
$
14.00
$
8.00
$
14.00
$
14.00
$
28.00
$
28.00
$
14.00
$
14.00
$
35.00
$
4.00
$
4.00
$
4.00
$
14.00
$
7.00
$
19.00
$
18.00
$
6.00
$
18.00
$
14.00
$
4.00
$
19.00
$
18.00
$
7.00
$
19.00
$
14.00
$
8.00
$
4.00
$
14.00
$
8.00
$
14.00
$
28.00
$
18.00
$
14.00 Total Cost
$
738.00
$ 1,260.00
$
216.00
$
938.00
$
474.00
$
644.00
$
728.00
$
312.00
$
924.00
$
812.00
$ 1,120.00
$ 1,988.00
$
252.00
$
392.00
$ 1,155.00
$
56.00
$
288.00
$
40.00
$
588.00
$
336.00
$
551.00
$
180.00
$
588.00
$
810.00
$
812.00
$
264.00
$ 1,615.00
$
216.00
$
280.00
$
95.00
$
686.00
$
104.00
$
152.00
$
714.00
$
680.00
$ 1,148.00
$ 1,792.00
$
396.00
$ 1,036.00 $
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$ Profit
CustomerID
Customer
246.00
315
The Station
90.00
315
The Station
108.00
233
Pierce Arrow
67.00
14
Smoke House
474.00
572
Two Bitts
46.00
23
Flagstaff House
52.00
6
Bert's Bistro
156.00
95
Mamm'a Pasta Palace
66.00
846
The Dandelion
58.00
557
Carmens
200.00
315
The Station
355.00
846
The Dandelion
18.00
557
Carmens
28.00
572
Two Bitts
594.00
14
Smoke House
56.00
14
Smoke House
288.00
95
Mamm'a Pasta Palace
40.00
95
Mamm'a Pasta Palace
42.00
14
Smoke House
96.00
23
Flagstaff House
58.00
846
The Dandelion
60.00
95
Mamm'a Pasta Palace
588.00
23
Flagstaff House
270.00
557
Carmens
58.00
23
Flagstaff House
264.00
572
Two Bitts
170.00
6
Bert's Bistro
72.00
846
The Dandelion
80.00
6
Bert's Bistro
10.00
6
Bert's Bistro
49.00
572
Two Bitts
52.00
14
Smoke House
152.00
95
Mamm'a Pasta Palace
51.00
23
Flagstaff House
340.00
572
Two Bitts
82.00
14
Smoke House
320.00
846
The Dandelion
132.00
846
The Dandelion
74.00
6
Bert's Bistro OrderDate ProductID
ProductName
05-Feb-09
4
Tomatoes
05-Feb-09
4
Tomatoes
06-Feb-09
31
Peppers
07-Feb-09
6
Sun dried tomatoes
09-Feb-09
16
Romaine lettuce
09-Feb-09
100
Parmesan cheese
10-Feb-09
30
Red onions
10-Feb-09
16
Romaine lettuce
11-Feb-09
4
Tomatoes
11-Feb-09
100
Parmesan cheese
12-Feb-09
2
Black olives
13-Feb-09
31
Peppers
15-Feb-09
19
Chicken
16-Feb-09
16
Romaine lettuce
17-Feb-09
30
Red onions
18-Feb-09
4
Tomatoes
19-Feb-09
4
Tomatoes
19-Feb-09
16
Romaine lettuce
19-Feb-09
19
Chicken
20-Feb-09
31
Peppers
21-Feb-09
31
Peppers
21-Feb-09
4
Tomatoes
22-Feb-09
4
Tomatoes
24-Feb-09
19
Chicken
24-Feb-09
31
Peppers
26-Feb-09
39
Pineapple
26-Feb-09
2
Black olives
27-Feb-09
6
Sun dried tomatoes
28-Feb-09
39
Pineapple
28-Feb-09
4
Tomatoes
28-Feb-09
4
Tomatoes
01-Mar-09
4
Tomatoes
02-Mar-09
100
Parmesan cheese
03-Mar-09
39
Pineapple
03-Mar-09
4
Tomatoes
03-Mar-09
19
Chicken
03-Mar-09
31
Peppers
04-Mar-09
16
Romaine lettuce
04-Mar-09
22
Mozzarella cheese Quantity
13
64
59
12
7
14
7
87
70
38
4
44
41
15
76
49
86
68
50
31
62
13
84
55
89
56
16
2
67
2
59
37
19
48
41
64
33
66
92 Unit Price
$
9.00
$
9.00
$ 21.00
$ 75.00
$ 15.00
$
8.00
$ 12.00
$ 15.00
$
9.00
$
8.00
$ 12.00
$ 21.00
$ 36.00
$ 15.00
$ 12.00
$
9.00
$
9.00
$ 15.00
$ 36.00
$ 21.00
$ 21.00
$
9.00
$
9.00
$ 36.00
$ 21.00
$ 33.00
$ 12.00
$ 75.00
$ 33.00
$
9.00
$
9.00
$
9.00
$
8.00
$ 33.00
$
9.00
$ 36.00
$ 21.00
$ 15.00
$ 24.00 Total Sales
$
117.00
$
576.00
$ 1,239.00
$
900.00
$
105.00
$
112.00
$
84.00
$ 1,305.00
$
630.00
$
304.00
$
48.00
$
924.00
$ 1,476.00
$
225.00
$
912.00
$
441.00
$
774.00
$ 1,020.00
$ 1,800.00
$
651.00
$ 1,302.00
$
117.00
$
756.00
$ 1,980.00
$ 1,869.00
$ 1,848.00
$
192.00
$
150.00
$ 2,211.00
$
18.00
$
531.00
$
333.00
$
152.00
$ 1,584.00
$
369.00
$ 2,304.00
$
693.00
$
990.00
$ 2,208.00 Unit Cost
$
7.00
$
7.00
$
19.00
$
25.00
$
14.00
$
4.00
$
8.00
$
14.00
$
7.00
$
4.00
$
6.00
$
19.00
$
25.00
$
14.00
$
8.00
$
7.00
$
7.00
$
14.00
$
25.00
$
19.00
$
19.00
$
7.00
$
7.00
$
25.00
$
19.00
$
28.00
$
6.00
$
25.00
$
28.00
$
7.00
$
7.00
$
7.00
$
4.00
$
28.00
$
7.00
$
25.00
$
19.00
$
14.00
$
18.00 Total Cost
$
91.00
$
448.00
$ 1,121.00
$
300.00
$
98.00
$
56.00
$
56.00
$ 1,218.00
$
490.00
$
152.00
$
24.00
$
836.00
$ 1,025.00
$
210.00
$
608.00
$
343.00
$
602.00
$
952.00
$ 1,250.00
$
589.00
$ 1,178.00
$
91.00
$
588.00
$ 1,375.00
$ 1,691.00
$ 1,568.00
$
96.00
$
50.00
$ 1,876.00
$
14.00
$
413.00
$
259.00
$
76.00
$ 1,344.00
$
287.00
$ 1,600.00
$
627.00
$
924.00
$ 1,656.00 $
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$ Profit
CustomerID
Customer
26.00
686
Laudisio
128.00
6
Bert's Bistro
118.00
23
Flagstaff House
600.00
6
Bert's Bistro
7.00
95
Mamm'a Pasta Palace
56.00
95
Mamm'a Pasta Palace
28.00
23
Flagstaff House
87.00
23
Flagstaff House
140.00
557
Carmens
152.00
95
Mamm'a Pasta Palace
24.00
6
Bert's Bistro
88.00
557
Carmens
451.00
14
Smoke House
15.00
686
Laudisio
304.00
14
Smoke House
98.00
846
The Dandelion
172.00
14
Smoke House
68.00
14
Smoke House
550.00
14
Smoke House
62.00
14
Smoke House
124.00
572
Two Bitts
26.00
572
Two Bitts
168.00
846
The Dandelion
605.00
14
Smoke House
178.00
95
Mamm'a Pasta Palace
280.00
557
Carmens
96.00
95
Mamm'a Pasta Palace
100.00
6
Bert's Bistro
335.00
6
Bert's Bistro
4.00
95
Mamm'a Pasta Palace
118.00
846
The Dandelion
74.00
572
Two Bitts
76.00
686
Laudisio
240.00
14
Smoke House
82.00
572
Two Bitts
704.00
6
Bert's Bistro
66.00
6
Bert's Bistro
66.00
557
Carmens
552.00
6
Bert's Bistro OrderDate ProductID
ProductName
04-Mar-09
2
Black olives
04-Mar-09
4
Tomatoes
05-Mar-09
19
Chicken
06-Mar-09
30
Red onions
06-Mar-09
16
Romaine lettuce
07-Mar-09
19
Chicken
07-Mar-09
16
Romaine lettuce
07-Mar-09
2
Black olives
08-Mar-09
16
Romaine lettuce
08-Mar-09
4
Tomatoes
10-Mar-09
39
Pineapple
10-Mar-09
30
Red onions
10-Mar-09
16
Romaine lettuce
11-Mar-09
16
Romaine lettuce
11-Mar-09
4
Tomatoes
12-Mar-09
30
Red onions
13-Mar-09
31
Peppers
13-Mar-09
16
Romaine lettuce
14-Mar-09
39
Pineapple
15-Mar-09
16
Romaine lettuce
15-Mar-09
100
Parmesan cheese
16-Mar-09
16
Romaine lettuce
16-Mar-09
16
Romaine lettuce
17-Mar-09
4
Tomatoes
19-Mar-09
30
Red onions
20-Mar-09
31
Peppers
22-Mar-09
19
Chicken
23-Mar-09
4
Tomatoes
24-Mar-09
85
Pepperoni
27-Mar-09
100
Parmesan cheese
28-Mar-09
31
Peppers
28-Mar-09
31
Peppers
29-Mar-09
85
Pepperoni
30-Mar-09
4
Tomatoes
31-Mar-09
31
Peppers
01-Apr-09
100
Parmesan cheese
02-Apr-09
4
Tomatoes
02-Apr-09
16
Romaine lettuce
03-Apr-09
4
Tomatoes Quantity
32
17
83
89
8
25
5
73
36
16
33
90
74
72
53
59
71
9
95
72
39
68
38
87
54
85
1
93
41
95
7
92
30
38
21
95
69
96
36 Unit Price
$ 12.00
$
9.00
$ 36.00
$ 12.00
$ 15.00
$ 36.00
$ 15.00
$ 12.00
$ 15.00
$
9.00
$ 33.00
$ 12.00
$ 15.00
$ 15.00
$
9.00
$ 12.00
$ 21.00
$ 15.00
$ 33.00
$ 15.00
$
8.00
$ 15.00
$ 15.00
$
9.00
$ 12.00
$ 21.00
$ 36.00
$
9.00
$ 53.00
$
8.00
$ 21.00
$ 21.00
$ 53.00
$
9.00
$ 21.00
$
8.00
$
9.00
$ 15.00
$
9.00 Total Sales
$
384.00
$
153.00
$ 2,988.00
$ 1,068.00
$
120.00
$
900.00
$
75.00
$
876.00
$
540.00
$
144.00
$ 1,089.00
$ 1,080.00
$ 1,110.00
$ 1,080.00
$
477.00
$
708.00
$ 1,491.00
$
135.00
$ 3,135.00
$ 1,080.00
$
312.00
$ 1,020.00
$
570.00
$
783.00
$
648.00
$ 1,785.00
$
36.00
$
837.00
$ 2,173.00
$
760.00
$
147.00
$ 1,932.00
$ 1,590.00
$
342.00
$
441.00
$
760.00
$
621.00
$ 1,440.00
$
324.00 Unit Cost
$
6.00
$
7.00
$
25.00
$
8.00
$
14.00
$
25.00
$
14.00
$
6.00
$
14.00
$
7.00
$
28.00
$
8.00
$
14.00
$
14.00
$
7.00
$
8.00
$
19.00
$
14.00
$
28.00
$
14.00
$
4.00
$
14.00
$
14.00
$
7.00
$
8.00
$
19.00
$
25.00
$
7.00
$
35.00
$
4.00
$
19.00
$
19.00
$
35.00
$
7.00
$
19.00
$
4.00
$
7.00
$
14.00
$
7.00 Total Cost
$
192.00
$
119.00
$ 2,075.00
$
712.00
$
112.00
$
625.00
$
70.00
$
438.00
$
504.00
$
112.00
$
924.00
$
720.00
$ 1,036.00
$ 1,008.00
$
371.00
$
472.00
$ 1,349.00
$
126.00
$ 2,660.00
$ 1,008.00
$
156.00
$
952.00
$
532.00
$
609.00
$
432.00
$ 1,615.00
$
25.00
$
651.00
$ 1,435.00
$
380.00
$
133.00
$ 1,748.00
$ 1,050.00
$
266.00
$
399.00
$
380.00
$
483.00
$ 1,344.00
$
252.00 $
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$ Profit
CustomerID
Customer
192.00
686
Laudisio
34.00
95
Mamm'a Pasta Palace
913.00
23
Flagstaff House
356.00
846
The Dandelion
8.00
6
Bert's Bistro
275.00
572
Two Bitts
5.00
557
Carmens
438.00
14
Smoke House
36.00
557
Carmens
32.00
846
The Dandelion
165.00
572
Two Bitts
360.00
14
Smoke House
74.00
95
Mamm'a Pasta Palace
72.00
557
Carmens
106.00
23
Flagstaff House
236.00
6
Bert's Bistro
142.00
557
Carmens
9.00
572
Two Bitts
475.00
95
Mamm'a Pasta Palace
72.00
23
Flagstaff House
156.00
95
Mamm'a Pasta Palace
68.00
6
Bert's Bistro
38.00
6
Bert's Bistro
174.00
846
The Dandelion
216.00
95
Mamm'a Pasta Palace
170.00
95
Mamm'a Pasta Palace
11.00
14
Smoke House
186.00
557
Carmens
738.00
686
Laudisio
380.00
557
Carmens
14.00
23
Flagstaff House
184.00
572
Two Bitts
540.00
14
Smoke House
76.00
686
Laudisio
42.00
23
Flagstaff House
380.00
95
Mamm'a Pasta Palace
138.00
95
Mamm'a Pasta Palace
96.00
14
Smoke House
72.00
315
The Station OrderDate ProductID
ProductName
04-Apr-09
22
Mozzarella cheese
04-Apr-09
30
Red onions
05-Apr-09
100
Parmesan cheese
07-Apr-09
16
Romaine lettuce
07-Apr-09
31
Peppers
07-Apr-09
85
Pepperoni
09-Apr-09
2
Black olives
09-Apr-09
2
Black olives
09-Apr-09
100
Parmesan cheese
10-Apr-09
100
Parmesan cheese
10-Apr-09
4
Tomatoes
11-Apr-09
19
Chicken
12-Apr-09
19
Chicken
12-Apr-09
31
Peppers
13-Apr-09
22
Mozzarella cheese
13-Apr-09
4
Tomatoes
14-Apr-09
16
Romaine lettuce
14-Apr-09
100
Parmesan cheese
14-Apr-09
19
Chicken
15-Apr-09
16
Romaine lettuce
16-Apr-09
22
Mozzarella cheese
17-Apr-09
30
Red onions
18-Apr-09
16
Romaine lettuce
18-Apr-09
22
Mozzarella cheese
19-Apr-09
100
Parmesan cheese
19-Apr-09
98
Sausage
20-Apr-09
98
Sausage
20-Apr-09
6
Sun dried tomatoes
20-Apr-09
100
Parmesan cheese
20-Apr-09
85
Pepperoni
20-Apr-09
100
Parmesan cheese
22-Apr-09
22
Mozzarella cheese
22-Apr-09
16
Romaine lettuce
23-Apr-09
16
Romaine lettuce
23-Apr-09
16
Romaine lettuce
23-Apr-09
31
Peppers
24-Apr-09
31
Peppers
24-Apr-09
16
Romaine lettuce
24-Apr-09
30
Red onions Quantity
86
58
99
63
15
11
89
77
16
58
55
59
2
85
11
84
82
18
43
29
78
61
17
60
27
74
36
9
16
80
34
14
30
34
17
28
45
54
23 Unit Price
$ 24.00
$ 12.00
$
8.00
$ 15.00
$ 21.00
$ 53.00
$ 12.00
$ 12.00
$
8.00
$
8.00
$
9.00
$ 36.00
$ 36.00
$ 21.00
$ 24.00
$
9.00
$ 15.00
$
8.00
$ 36.00
$ 15.00
$ 24.00
$ 12.00
$ 15.00
$ 24.00
$
8.00
$ 18.00
$ 18.00
$ 75.00
$
8.00
$ 53.00
$
8.00
$ 24.00
$ 15.00
$ 15.00
$ 15.00
$ 21.00
$ 21.00
$ 15.00
$ 12.00 Total Sales
$ 2,064.00
$
696.00
$
792.00
$
945.00
$
315.00
$
583.00
$ 1,068.00
$
924.00
$
128.00
$
464.00
$
495.00
$ 2,124.00
$
72.00
$ 1,785.00
$
264.00
$
756.00
$ 1,230.00
$
144.00
$ 1,548.00
$
435.00
$ 1,872.00
$
732.00
$
255.00
$ 1,440.00
$
216.00
$ 1,332.00
$
648.00
$
675.00
$
128.00
$ 4,240.00
$
272.00
$
336.00
$
450.00
$
510.00
$
255.00
$
588.00
$
945.00
$
810.00
$
276.00 Unit Cost
$
18.00
$
8.00
$
4.00
$
14.00
$
19.00
$
35.00
$
6.00
$
6.00
$
4.00
$
4.00
$
7.00
$
25.00
$
25.00
$
19.00
$
18.00
$
7.00
$
14.00
$
4.00
$
25.00
$
14.00
$
18.00
$
8.00
$
14.00
$
18.00
$
4.00
$
8.00
$
8.00
$
25.00
$
4.00
$
35.00
$
4.00
$
18.00
$
14.00
$
14.00
$
14.00
$
19.00
$
19.00
$
14.00
$
8.00 Total Cost
$ 1,548.00
$
464.00
$
396.00
$
882.00
$
285.00
$
385.00
$
534.00
$
462.00
$
64.00
$
232.00
$
385.00
$ 1,475.00
$
50.00
$ 1,615.00
$
198.00
$
588.00
$ 1,148.00
$
72.00
$ 1,075.00
$
406.00
$ 1,404.00
$
488.00
$
238.00
$ 1,080.00
$
108.00
$
592.00
$
288.00
$
225.00
$
64.00
$ 2,800.00
$
136.00
$
252.00
$
420.00
$
476.00
$
238.00
$
532.00
$
855.00
$
756.00
$
184.00 Profit
CustomerID
Customer
$ 516.00
686
Laudisio
$ 232.00
557
Carmens
$ 396.00
233
Pierce Arrow
$
63.00
557
Carmens
$
30.00
846
The Dandelion
$ 198.00
23
Flagstaff House
$ 534.00
557
Carmens
$ 462.00
846
The Dandelion
$
64.00
14
Smoke House
$ 232.00
846
The Dandelion
$ 110.00
6
Bert's Bistro
$ 649.00
6
Bert's Bistro
$
22.00
686
Laudisio
$ 170.00
6
Bert's Bistro
$
66.00
23
Flagstaff House
$ 168.00
14
Smoke House
$
82.00
6
Bert's Bistro
$
72.00
557
Carmens
$ 473.00
315
The Station
$
29.00
572
Two Bitts
$ 468.00
572
Two Bitts
$ 244.00
686
Laudisio
$
17.00
14
Smoke House
$ 360.00
846
The Dandelion
$ 108.00
6
Bert's Bistro
$ 740.00
315
The Station
$ 360.00
557
Carmens
$ 450.00
686
Laudisio
$
64.00
846
The Dandelion
$ 1,440.00
23
Flagstaff House
$ 136.00
14
Smoke House
$
84.00
6
Bert's Bistro
$
30.00
846
The Dandelion
$
34.00
6
Bert's Bistro
$
17.00
572
Two Bitts
$
56.00
846
The Dandelion
$
90.00
557
Carmens
$
54.00
233
Pierce Arrow
$
92.00
6
Bert's Bistro OrderDate ProductID
ProductName
24-Apr-09
16
Romaine lettuce
24-Apr-09
100
Parmesan cheese
24-Apr-09
16
Romaine lettuce
26-Apr-09
30
Red onions
26-Apr-09
4
Tomatoes
27-Apr-09
30
Red onions
27-Apr-09
4
Tomatoes
28-Apr-09
4
Tomatoes
29-Apr-09
30
Red onions
30-Apr-09
100
Parmesan cheese
01-May-09
4
Tomatoes
03-May-09
39
Pineapple
03-May-09
30
Red onions
04-May-09
4
Tomatoes
05-May-09
16
Romaine lettuce
05-May-09
39
Pineapple
06-May-09
2
Black olives
06-May-09
16
Romaine lettuce
06-May-09
4
Tomatoes
07-May-09
19
Chicken
08-May-09
22
Mozzarella cheese
08-May-09
16
Romaine lettuce
10-May-09
16
Romaine lettuce
11-May-09
98
Sausage
12-May-09
100
Parmesan cheese
12-May-09
30
Red onions
14-May-09
30
Red onions
14-May-09
100
Parmesan cheese
14-May-09
100
Parmesan cheese
14-May-09
31
Peppers
14-May-09
19
Chicken
15-May-09
4
Tomatoes
16-May-09
16
Romaine lettuce
16-May-09
100
Parmesan cheese
17-May-09
100
Parmesan cheese
18-May-09
39
Pineapple
19-May-09
31
Peppers
19-May-09
30
Red onions
19-May-09
100
Parmesan cheese Quantity
85
89
24
85
86
95
19
82
48
10
62
3
25
25
84
51
63
18
10
84
69
88
98
52
28
79
53
78
69
60
34
30
60
78
46
65
88
76
11 Unit Price
$ 15.00
$
8.00
$ 15.00
$ 12.00
$
9.00
$ 12.00
$
9.00
$
9.00
$ 12.00
$
8.00
$
9.00
$ 33.00
$ 12.00
$
9.00
$ 15.00
$ 33.00
$ 12.00
$ 15.00
$
9.00
$ 36.00
$ 24.00
$ 15.00
$ 15.00
$ 18.00
$
8.00
$ 12.00
$ 12.00
$
8.00
$
8.00
$ 21.00
$ 36.00
$
9.00
$ 15.00
$
8.00
$
8.00
$ 33.00
$ 21.00
$ 12.00
$
8.00 Total Sales
$ 1,275.00
$
712.00
$
360.00
$ 1,020.00
$
774.00
$ 1,140.00
$
171.00
$
738.00
$
576.00
$
80.00
$
558.00
$
99.00
$
300.00
$
225.00
$ 1,260.00
$ 1,683.00
$
756.00
$
270.00
$
90.00
$ 3,024.00
$ 1,656.00
$ 1,320.00
$ 1,470.00
$
936.00
$
224.00
$
948.00
$
636.00
$
624.00
$
552.00
$ 1,260.00
$ 1,224.00
$
270.00
$
900.00
$
624.00
$
368.00
$ 2,145.00
$ 1,848.00
$
912.00
$
88.00 Unit Cost
$
14.00
$
4.00
$
14.00
$
8.00
$
7.00
$
8.00
$
7.00
$
7.00
$
8.00
$
4.00
$
7.00
$
28.00
$
8.00
$
7.00
$
14.00
$
28.00
$
6.00
$
14.00
$
7.00
$
25.00
$
18.00
$
14.00
$
14.00
$
8.00
$
4.00
$
8.00
$
8.00
$
4.00
$
4.00
$
19.00
$
25.00
$
7.00
$
14.00
$
4.00
$
4.00
$
28.00
$
19.00
$
8.00
$
4.00 Total Cost
$ 1,190.00
$
356.00
$
336.00
$
680.00
$
602.00
$
760.00
$
133.00
$
574.00
$
384.00
$
40.00
$
434.00
$
84.00
$
200.00
$
175.00
$ 1,176.00
$ 1,428.00
$
378.00
$
252.00
$
70.00
$ 2,100.00
$ 1,242.00
$ 1,232.00
$ 1,372.00
$
416.00
$
112.00
$
632.00
$
424.00
$
312.00
$
276.00
$ 1,140.00
$
850.00
$
210.00
$
840.00
$
312.00
$
184.00
$ 1,820.00
$ 1,672.00
$
608.00
$
44.00 $
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$ Profit
CustomerID
Customer
85.00
686
Laudisio
356.00
23
Flagstaff House
24.00
686
Laudisio
340.00
14
Smoke House
172.00
6
Bert's Bistro
380.00
686
Laudisio
38.00
14
Smoke House
164.00
233
Pierce Arrow
192.00
557
Carmens
40.00
572
Two Bitts
124.00
846
The Dandelion
15.00
572
Two Bitts
100.00
23
Flagstaff House
50.00
23
Flagstaff House
84.00
846
The Dandelion
255.00
95
Mamm'a Pasta Palace
378.00
23
Flagstaff House
18.00
315
The Station
20.00
14
Smoke House
924.00
23
Flagstaff House
414.00
557
Carmens
88.00
572
Two Bitts
98.00
686
Laudisio
520.00
572
Two Bitts
112.00
846
The Dandelion
316.00
572
Two Bitts
212.00
846
The Dandelion
312.00
95
Mamm'a Pasta Palace
276.00
686
Laudisio
120.00
572
Two Bitts
374.00
95
Mamm'a Pasta Palace
60.00
23
Flagstaff House
60.00
95
Mamm'a Pasta Palace
312.00
315
The Station
184.00
23
Flagstaff House
325.00
846
The Dandelion
176.00
95
Mamm'a Pasta Palace
304.00
557
Carmens
44.00
572
Two Bitts OrderDate ProductID
ProductName
19-May-09
22
Mozzarella cheese
19-May-09
16
Romaine lettuce
20-May-09
31
Peppers
21-May-09
31
Peppers
21-May-09
31
Peppers
22-May-09
19
Chicken
23-May-09
16
Romaine lettuce
23-May-09
98
Sausage
24-May-09
30
Red onions
24-May-09
98
Sausage
24-May-09
39
Pineapple
26-May-09
16
Romaine lettuce
27-May-09
39
Pineapple
27-May-09
30
Red onions
28-May-09
39
Pineapple
28-May-09
100
Parmesan cheese
29-May-09
4
Tomatoes
29-May-09
31
Peppers
29-May-09
31
Peppers
30-May-09
31
Peppers
30-May-09
4
Tomatoes
01-Jun-09
4
Tomatoes
02-Jun-09
85
Pepperoni
04-Jun-09
30
Red onions
07-Jun-09
31
Peppers
08-Jun-09
16
Romaine lettuce
08-Jun-09
22
Mozzarella cheese
08-Jun-09
39
Pineapple
10-Jun-09
30
Red onions
10-Jun-09
4
Tomatoes
11-Jun-09
100
Parmesan cheese
11-Jun-09
31
Peppers
11-Jun-09
16
Romaine lettuce
11-Jun-09
6
Sun dried tomatoes
12-Jun-09
100
Parmesan cheese
12-Jun-09
4
Tomatoes
12-Jun-09
30
Red onions
13-Jun-09
100
Parmesan cheese
13-Jun-09
100
Parmesan cheese Quantity
35
98
57
11
81
93
2
78
99
43
51
46
29
63
60
46
5
92
1
33
56
54
63
48
39
53
76
70
38
48
95
55
10
8
95
45
87
2
85 Unit Price
$ 24.00
$ 15.00
$ 21.00
$ 21.00
$ 21.00
$ 36.00
$ 15.00
$ 18.00
$ 12.00
$ 18.00
$ 33.00
$ 15.00
$ 33.00
$ 12.00
$ 33.00
$
8.00
$
9.00
$ 21.00
$ 21.00
$ 21.00
$
9.00
$
9.00
$ 53.00
$ 12.00
$ 21.00
$ 15.00
$ 24.00
$ 33.00
$ 12.00
$
9.00
$
8.00
$ 21.00
$ 15.00
$ 75.00
$
8.00
$
9.00
$ 12.00
$
8.00
$
8.00 Total Sales
$
840.00
$ 1,470.00
$ 1,197.00
$
231.00
$ 1,701.00
$ 3,348.00
$
30.00
$ 1,404.00
$ 1,188.00
$
774.00
$ 1,683.00
$
690.00
$
957.00
$
756.00
$ 1,980.00
$
368.00
$
45.00
$ 1,932.00
$
21.00
$
693.00
$
504.00
$
486.00
$ 3,339.00
$
576.00
$
819.00
$
795.00
$ 1,824.00
$ 2,310.00
$
456.00
$
432.00
$
760.00
$ 1,155.00
$
150.00
$
600.00
$
760.00
$
405.00
$ 1,044.00
$
16.00
$
680.00 Unit Cost
$
18.00
$
14.00
$
19.00
$
19.00
$
19.00
$
25.00
$
14.00
$
8.00
$
8.00
$
8.00
$
28.00
$
14.00
$
28.00
$
8.00
$
28.00
$
4.00
$
7.00
$
19.00
$
19.00
$
19.00
$
7.00
$
7.00
$
35.00
$
8.00
$
19.00
$
14.00
$
18.00
$
28.00
$
8.00
$
7.00
$
4.00
$
19.00
$
14.00
$
25.00
$
4.00
$
7.00
$
8.00
$
4.00
$
4.00 Total Cost
$
630.00
$ 1,372.00
$ 1,083.00
$
209.00
$ 1,539.00
$ 2,325.00
$
28.00
$
624.00
$
792.00
$
344.00
$ 1,428.00
$
644.00
$
812.00
$
504.00
$ 1,680.00
$
184.00
$
35.00
$ 1,748.00
$
19.00
$
627.00
$
392.00
$
378.00
$ 2,205.00
$
384.00
$
741.00
$
742.00
$ 1,368.00
$ 1,960.00
$
304.00
$
336.00
$
380.00
$ 1,045.00
$
140.00
$
200.00
$
380.00
$
315.00
$
696.00
$
8.00
$
340.00 Profit
CustomerID
Customer
$ 210.00
6
Bert's Bistro
$
98.00
233
Pierce Arrow
$ 114.00
846
The Dandelion
$
22.00
95
Mamm'a Pasta Palace
$ 162.00
6
Bert's Bistro
$ 1,023.00
95
Mamm'a Pasta Palace
$
2.00
572
Two Bitts
$ 780.00
14
Smoke House
$ 396.00
557
Carmens
$ 430.00
686
Laudisio
$ 255.00
572
Two Bitts
$
46.00
23
Flagstaff House
$ 145.00
557
Carmens
$ 252.00
23
Flagstaff House
$ 300.00
557
Carmens
$ 184.00
6
Bert's Bistro
$
10.00
14
Smoke House
$ 184.00
557
Carmens
$
2.00
95
Mamm'a Pasta Palace
$
66.00
14
Smoke House
$ 112.00
23
Flagstaff House
$ 108.00
23
Flagstaff House
$ 1,134.00
233
Pierce Arrow
$ 192.00
686
Laudisio
$
78.00
95
Mamm'a Pasta Palace
$
53.00
95
Mamm'a Pasta Palace
$ 456.00
14
Smoke House
$ 350.00
686
Laudisio
$ 152.00
6
Bert's Bistro
$
96.00
846
The Dandelion
$ 380.00
95
Mamm'a Pasta Palace
$ 110.00
95
Mamm'a Pasta Palace
$
10.00
23
Flagstaff House
$ 400.00
6
Bert's Bistro
$ 380.00
557
Carmens
$
90.00
315
The Station
$ 348.00
557
Carmens
$
8.00
14
Smoke House
$ 340.00
572
Two Bitts OrderDate ProductID
ProductName
14-Jun-09
19
Chicken
14-Jun-09
100
Parmesan cheese
15-Jun-09
16
Romaine lettuce
15-Jun-09
22
Mozzarella cheese
15-Jun-09
31
Peppers
16-Jun-09
85
Pepperoni
16-Jun-09
98
Sausage
17-Jun-09
16
Romaine lettuce
17-Jun-09
4
Tomatoes
20-Jun-09
4
Tomatoes
21-Jun-09
4
Tomatoes
21-Jun-09
22
Mozzarella cheese
21-Jun-09
16
Romaine lettuce
23-Jun-09
4
Tomatoes
23-Jun-09
6
Sun dried tomatoes
25-Jun-09
4
Tomatoes
26-Jun-09
100
Parmesan cheese
27-Jun-09
31
Peppers
28-Jun-09
4
Tomatoes
28-Jun-09
98
Sausage
29-Jun-09
100
Parmesan cheese
29-Jun-09
30
Red onions
30-Jun-09
31
Peppers
02-Jul-09
22
Mozzarella cheese
02-Jul-09
16
Romaine lettuce
02-Jul-09
16
Romaine lettuce
03-Jul-09
16
Romaine lettuce
04-Jul-09
16
Romaine lettuce
06-Jul-09
2
Black olives
07-Jul-09
98
Sausage
07-Jul-09
100
Parmesan cheese
08-Jul-09
19
Chicken
08-Jul-09
85
Pepperoni
11-Jul-09
39
Pineapple
11-Jul-09
2
Black olives
12-Jul-09
30
Red onions
13-Jul-09
16
Romaine lettuce
13-Jul-09
100
Parmesan cheese
14-Jul-09
31
Peppers Quantity
19
98
37
74
39
55
44
58
7
99
83
4
81
91
36
94
12
3
49
96
94
4
58
31
3
10
21
65
96
79
99
94
69
94
57
77
37
30
65 Unit Price
$ 36.00
$
8.00
$ 15.00
$ 24.00
$ 21.00
$ 53.00
$ 18.00
$ 15.00
$
9.00
$
9.00
$
9.00
$ 24.00
$ 15.00
$
9.00
$ 75.00
$
9.00
$
8.00
$ 21.00
$
9.00
$ 18.00
$
8.00
$ 12.00
$ 21.00
$ 24.00
$ 15.00
$ 15.00
$ 15.00
$ 15.00
$ 12.00
$ 18.00
$
8.00
$ 36.00
$ 53.00
$ 33.00
$ 12.00
$ 12.00
$ 15.00
$
8.00
$ 21.00 Total Sales
$
684.00
$
784.00
$
555.00
$ 1,776.00
$
819.00
$ 2,915.00
$
792.00
$
870.00
$
63.00
$
891.00
$
747.00
$
96.00
$ 1,215.00
$
819.00
$ 2,700.00
$
846.00
$
96.00
$
63.00
$
441.00
$ 1,728.00
$
752.00
$
48.00
$ 1,218.00
$
744.00
$
45.00
$
150.00
$
315.00
$
975.00
$ 1,152.00
$ 1,422.00
$
792.00
$ 3,384.00
$ 3,657.00
$ 3,102.00
$
684.00
$
924.00
$
555.00
$
240.00
$ 1,365.00 Unit Cost
$
25.00
$
4.00
$
14.00
$
18.00
$
19.00
$
35.00
$
8.00
$
14.00
$
7.00
$
7.00
$
7.00
$
18.00
$
14.00
$
7.00
$
25.00
$
7.00
$
4.00
$
19.00
$
7.00
$
8.00
$
4.00
$
8.00
$
19.00
$
18.00
$
14.00
$
14.00
$
14.00
$
14.00
$
6.00
$
8.00
$
4.00
$
25.00
$
35.00
$
28.00
$
6.00
$
8.00
$
14.00
$
4.00
$
19.00 Total Cost
$
475.00
$
392.00
$
518.00
$ 1,332.00
$
741.00
$ 1,925.00
$
352.00
$
812.00
$
49.00
$
693.00
$
581.00
$
72.00
$ 1,134.00
$
637.00
$
900.00
$
658.00
$
48.00
$
57.00
$
343.00
$
768.00
$
376.00
$
32.00
$ 1,102.00
$
558.00
$
42.00
$
140.00
$
294.00
$
910.00
$
576.00
$
632.00
$
396.00
$ 2,350.00
$ 2,415.00
$ 2,632.00
$
342.00
$
616.00
$
518.00
$
120.00
$ 1,235.00 Profit
CustomerID
Customer
$ 209.00
6
Bert's Bistro
$ 392.00
572
Two Bitts
$
37.00
6
Bert's Bistro
$ 444.00
95
Mamm'a Pasta Palace
$
78.00
686
Laudisio
$ 990.00
6
Bert's Bistro
$ 440.00
95
Mamm'a Pasta Palace
$
58.00
572
Two Bitts
$
14.00
686
Laudisio
$ 198.00
14
Smoke House
$ 166.00
23
Flagstaff House
$
24.00
23
Flagstaff House
$
81.00
557
Carmens
$ 182.00
557
Carmens
$ 1,800.00
686
Laudisio
$ 188.00
95
Mamm'a Pasta Palace
$
48.00
14
Smoke House
$
6.00
233
Pierce Arrow
$
98.00
14
Smoke House
$ 960.00
23
Flagstaff House
$ 376.00
23
Flagstaff House
$
16.00
95
Mamm'a Pasta Palace
$ 116.00
6
Bert's Bistro
$ 186.00
572
Two Bitts
$
3.00
572
Two Bitts
$
10.00
846
The Dandelion
$
21.00
846
The Dandel...
CIS 429 Fall 2016
Lab Project 2: Excel Modeling
Due: At 11:59 PM on November 11, 2016 Points: 75 (20% penalty if less than 24 hours late, zero if more than 24
hours late.) Objective: Be familiar with the concept of using Solver, Pivot Tables, and
other models in Excel to support decision-making. This assignment is in three parts. You should submit all three parts in one
file as described at the end of this document. Set up: Download the Lab Project 2 Data File.xlsx file from Blackboard -> Lab Project 2.
There are 5 worksheet tabs on the bottom which map to the assignment sections as follows:
o Part One – Solver Shipping Costs – Solver Template o Part Two – Pivot Tables Better Business - Data Better Business – Solution o Part Three – Financial Modeling Moving Espressos – No Move Moving Espressos - Move You will complete all of these tabs in this assignment, and will turn in the completed
LastName_LabProject2.xlsx with your answers included. 1 Part 1: Optimizing Costs Using Solver (25 Points)
Scenario: One of the main products of the Fairway Woods Company is custom-made golf
clubs. The clubs are manufactured at four plants (Denver, Colorado; Phoenix,
Arizona; Dallas, Texas, Charlotte, North Carolina) and are then shipped by
truck to five distribution warehouses in Sacramento, California; Salt Lake City,
Utah; Chicago, Illinois; Albuquerque, New Mexico; and New York City, New
York. Since shipping costs are a major expense, management has begun an
analysis to determine ways to reduce them. For the upcoming golf season, the
output from each manufacturing plant and how much each warehouse will
require to satisfy its customers have been estimated.
The CIO from Fairway Woods Company has created a data file for you, on the
“Shipping Costs–Solver Template” tab of the data file. This tab contains the
shipping costs from each manufacturing plant to each warehouse as a baseline
analysis. Some business rules and requirements you should be aware of include: Task: The problem presented involves the shipment of goods from four plants
to five regional warehouses.
Goods can be shipped from any plant to any warehouse, but it costs
more to ship goods over long distances than over short distances. Your goal is to minimize the costs of shipping goods from production plants to
warehouses, thereby meeting the demand from each metropolitan area while
not exceeding the supply available from each plant. To complete this project
you should use the Solver function in Excel to assist with the analysis.
Solver will provide you with the minimum shipping costs based upon the
following constraints:
1. Minimizing the total shipping costs.
2. Total shipped must be less than or equal to supply at a plant.
3. Total shipped to warehouses must be greater than or equal to the
warehouse demand.
4. Number to ship must be greater than or equal to 0.
Hint: you can use ranges of cells in solver
After you get your solution from Solver, you should allow solver to replace the
values in the tables with its solution. The complete table, and the solver
parameters is what you will be graded on.
After you complete this section – Save your spreadsheet, and move on to
Part Two. 2 Part 2 - Pivot Tables (25 Points)
Scenario: Schweizer Distribution distributes fresh produce to local restaurants in
Springfield. It currently sells 12 different products through the efforts of 3
sales representatives to 10 restaurants. The company, like all small businesses,
is always interested in finding ways to increase its revenues and decrease its
expenses.
Bob Schweizer, the company's founder, has hired you as a business analyst.
You have just graduated with a business degree, and Bob is eager to hear your
ideas on how the business can increase profitability. Task: You start by analyzing the previous year's sales data by using Pivot Tables.
Use data in the “Better Business - Data” worksheet to generate a Pivot Table
to answer the questions in the “Better Business - Solution” worksheet as
shown below. Complete this table on the “Better Business - Solution” Tab Schweitzer Distribution Analysis
Question Name Total Who is Bob’s best customer by total sales?
Who is Bob’s worst customer by total sales
Who is Bob’s best customer by profit?
Who is Bob’s worst customer by profit?
What is Bob’s best selling product by total sales?
What is Bob’s worst selling product by total sales?
What is Bob’s best selling product by profit?
What is Bob’s worst selling product by profit?
Who is Bob’s best sales representative by total sales?
Who is Bob’s worst sales representative by total sales?
What is the best sales representative’s best selling product (by total sales)?
Who is the best sales representative’s best customer (by total sales)?
What is the best sales representative’s worst selling product (by total sales)?
Who is the best sales representative’s worst customer (by total sales)?
DO NOT TURN IN THIS DOCUMENT – THIS IS A SCREEN SHOT FROM THE
EXCEL FILE – YOU MUST TURN IN THE EXCEL FILE
After you have completed this section, save your spreadsheet and move on to Part Three. 3 Part 3: Financial Problem Modeling for Decision Making (25 Points)
Scenario: Pony Espresso is a small business that sells specialty coffee drinks at office
buildings. Each morning and afternoon, trucks arrive at offices’ front entrances,
and the office employees purchase various beverages such as Java du Jour and
Café de Colombia. The business is profitable.
The Pony Espresso offices are located north of town, where lease rates are less
expensive, but the principal sales area is south of town. This means the trucks
must drive across town four times each day.
The cost of transportation to and from the sales area plus the power demands of
the trucks’ coffee brewing equipment are a significant portion of variable costs.
Pony Espresso could reduce the amount of driving and, therefore, the variable
costs, if it moved the offices closer to the sales area.
Pony Espresso presently has fixed costs of $10,000 per month. The lease of a
new office, closer to the sales area, would cost an additional $2,400 per month.
This would increase the fixed costs to $12,400 per month. Although the lease
of new offices would increase the fixed costs, a careful estimate of the potential
savings in gasoline and vehicle maintenance indicates that Pony Espresso could
reduce the variable costs from $0.60 per unit to $0.30 per unit.
Total sales are unlikely to increase as a result of the move, but the savings in
variable costs may increase the annual profit. Task:
Section i: Review the Moving Espressos Sheet in the data file. Complete the model that
is outline on the “Moving Espressos – No Move” tab. Make sure to: Use Relative & Absolute Cell values where appropriate so that you can
copy formulas while ensuring that the calculations are accurate. Use the Sum Function where appropriate EBIT is calculated as Sales – Fixed Costs – Variable Costs
To complete the model:
1. Develop the cost analysis on the existing lease information using the
monthly sales figures provided to you on the sheet.
2. Calculate the month-to-month earnings and total earnings for the year
3. Do not consider any association with downsizing such as overhead—simply
focus on the information provided to you.
4. You will need to calculate the EBIT (earnings before interest and taxes) –
See above for formula. 4 Especially look at the change in the variability of the profit from month to
month. From November through January, when it is much more difficult to lure
office workers out into the cold to purchase coffee, Pony Espresso barely breaks
even. In fact, in December, the business lost money.
Section ii: Complete the model on the “Moving Espressos – Move” tab. Section iii: Does moving make sense? Choose “Yes” or “No” from the selection on the
“Moving Espressos – Move” tab. After you have filled in your answers on both worksheets, Save your spreadsheet. GRADING SCALE
Item
Part 1: Solver
Part 2: Pivot Tables
Part 3: Financial Modeling Possible Points
25
25
25
Total Points Possible
75 5