Select Page

master__1_.xlsx

Unformatted Attachment Preview

Sheet: Goal Seek 10%
Coffee Sales Forecast
Price per regular coffee: \$
Cups I need to sell:
Cost / per cup \$
Number of Employees
Employee Costs
Net Profit
1.85
1.05
Compute the profit based on the number of cups
sold, the sales price and the cost per cup.
The Number of Employees is based on the number
of Cups Sold. You need one employee for every
300 cups. Remember to round UP – e.g if you sell
301 CUPS you need 2 employees.
Employee Costs are calculated by the number of
Employees * the Hours_Per_Shift * The
Wage_Per_Hour. There are 6 Hours in a shift and
the wage per hour is \$15.75. I have defined helpful
constants using Name Manager for these 2 values.
You may use the names (ideally) or the constants
as stated here.
Profit PER CUP is the sale price minus the cost.
Total Profit is this value times the cups sold minus
the employee costs.
Using Goal Seek find the number of cups you need
to sell to achieve a Net profit of \$1000.00
Sheet: Lookup 20%
Name
Age
BB Johnson
Geezer Jones
Bopper Barnes
Mark Middie
Yannie Yaya
6
101
15
41
19
Income
Named Group Numeric Group Status
4321
20000
6123
65432
1234
Use the tables from sheet LookupData to show the age group
(numeric and named) and Income status for each person. For status
you will need to perform a lookup using the numeric group as a
column index for the lookup.
When done correctly the Stats colmn will have 2 valuse which are not
in the table. These will return the error #N/A. This is correct and
sufficent for full credit.
For 4 points extra credit, use IFERROR to create a “nicer” message
for these cells. OR (simpler?) For 1 points extra, change the table
titled “Group Status by Income Range” in the LookupData sheet to
deal with this problem. You may do both.
Sheet: Payment 10%
Years
Interest Rate
0.04
0.045
0.05
0.055
0.06
0.065
0.07
0.075
0.08
0.085
0.09
0.095
0.1
5
100000
125000
150000
175000
200000
Amount Borrowed
225000
250000
275000
Show the monthly payment for a loan. Using the Interest
rates in column A the Principal in Row 2 for the number
of years shown in cell B1.
Format Interest Rates, and \$ amounts appropriately
show/display rounded to the nearest dollar using
formatting but do NOT use ROUND
300000
325000
350000
375000
Sheet: DataTable 20%
Units Sold
Full
Mark Down
Unsold
Whole Sale Price
Markup
Full Price
Net
1000
10.0000%
60.00%
30.0%
\$20.00
30%
\$26.00
\$14,600.00
Create a One variable Data Table Showing the
Effect of Markup on Net, and the Number of
Units Sold at Full, Markdown and Unsold. The
entered for you (You’re Welcome).
Create a Two Variable Data Table Showing the
Effect of varing Markup and Number of Units
Sold on Net. Again the Example reference is
Use Data Validation to restrict the values in cell
B6 to be between 20% and 30%. Use an
appropriate Input and Error message.
Consider formatting all those references to be
“nicer” (OPTIONAL).
One Variable Table – Markup
Markup
Profit
Full
Down
Unsold
\$14,600.00 10.0000%
60.00%
30.0%
20%
21%
22%
23%
24%
25%
26%
27%
28%
29%
30%
Two Variable Table – Profit vary Markup & Units
########
20%
21%
22%
23%
24%
25%
26%
27%
28%
29%
30%
750
1000
1250
1500
1750
2000
Sheet: Table 10%
ID
3
4
364
8
365
10
13
15
24
26
344
343
352
359
360
2
7
12
19
358
357
355
5
6
21
22
23
347
1
345
350
354
11
16
28
366
353
346
363
9
14
17
18
20
25
27
342
348
349
351
356
361
362
Last Name
Ajenstat
Akers
Anderson
St. Clair
Bacon Jr.
Bankert
Barr
Beck
Boseman
Bourne
Smith
Waxman
Wistrom
Yukish
Zare
Ackerman
Alexander
Barnhill
Berglund
Omabashi
Rand
Yong
Alberts
Alderson
Berry
Bolender
Bonifaz
Nelson
Oster
West
Wilson
Wood
Barbariol
Ben-Sachar
Stinson
Stone
Wheeler
Abercrombie
Eisner
Bashary
Benson
Berge
Berndt
Ghanta
Plotzfield
Watters
Willett
Williams
Wilson
Young
Zimprich
Zwilling
First Name
Date of Hire Date of Birth Gender
François
2/1/81
12/21/64 M
Kim
5/29/79
4/8/58 F
Nancy
4/11/92
11/1/72 F
Rebecca
6/6/86
6/20/76 F
Dan K
6/22/74
5/15/49 M
Julie
4/21/83
7/9/93
4/8/59 M
12/20/98
2/5/58 M
Randall
10/18/76
4/2/71 M
Jason
1/18/86
9/10/53 M
Ed
1/4/93
4/1/71 M
Peter
11/30/74
5/10/76 M
Mark
12/12/78
8/31/74 M
Gary W
10/10/00
2/14/72 M
Robert
3/24/94
6/9/77 M
Pilar
4/26/89
10/5/50 F
Sean P
7/6/00
10/16/62 M
Josh
7/26/77
10/12/54 M
Andreas
3/19/88
10/21/54 M
Mariko
3/11/97
8/1/67 F
Ayn
4/6/86
11/18/74 F
Joe
1/29/85
11/16/77 M
Amy E.
10/15/89
4/22/70 F
Gregory F. (Greg)
1/12/92
5/28/64 M
Jo
9/20/00
1/7/66 F
Corinna
8/20/98
11/18/51 F
Luis
6/10/96
4/4/68 M
Cinthia
6/13/85
3/5/76 F
Lily
6/24/85
10/16/62 F
Paul
11/16/00
2/18/51 M
Dan
3/9/90
9/21/75 M
John
5/3/88
3/5/68 M
Angela
2/16/96
4/18/64 F
Linda
9/21/93
7/12/56 F
David M.
2/3/86
1/3/72 M
Craig
2/21/03
11/16/43 M
Jennifer
12/8/84
11/5/77 F
Wendy
7/15/84
9/10/57 F
Kim
3/25/85
1/28/55 F
Susan
4/6/88
1/14/61 F
Maurice
3/14/95
1/30/72 M
Max
11/22/77
9/2/75 M
Karen
6/1/97
2/27/65 F
Matthias
3/17/91
1/28/78 M
Raman
6/3/86
11/13/63 M
Stephanie
3/30/77
6/5/51 F
Jason M.
10/15/77
7/22/72 M
Benjamin C.
4/4/77
10/21/61 M
Jill A.
1/12/87
3/26/72 F
James C
6/29/77
1/12/76 M
Rob
4/7/88
11/29/58 M
Karin
12/13/82
3/6/73 F
Michael J.
10/10/92
3/13/62 M
City
St. Louis
Houston
Boston
Houston
St. Louis
Boston
St. Louis
St. Louis
St. Louis
St. Louis
Chicago
Houston
St. Louis
St. Louis
Chicago
Boston
St. Louis
Boston
Boston
Boston
Chicago
Chicago
Chicago
Boston
Houston
Chicago
Boston
Boston
Chicago
Boston
Chicago
Houston
St. Louis
Houston
St. Louis
Houston
Houston
St. Louis
Chicago
Chicago
Boston
Chicago
Chicago
St. Louis
Boston
St. Louis
Houston
St. Louis
Houston
Boston
Houston
Boston
St. Louis
Dept.
Engineering
Engineering
Engineering
Engineering
Engineering
Engineering
Engineering
Engineering
Engineering
Engineering
Engineering
Engineering
Engineering
Engineering
Engineering
Management
Management
Management
Management
Management
Management
Management
Other
Other
Other
Other
Other
Other
Other
Other
Other
Other
QA
QA
QA
QA
QA
QA
Sales
Sales
Sales
Sales
Sales
Sales
Sales
Sales
Sales
Sales
Sales
Sales
Sales
Sales
Sales
Salary
\$
48,000
\$
47,000
\$
39,000
\$
68,000
\$
32,000
\$
100,000
\$
66,000
\$
70,000
\$
74,000
\$
82,000
\$
68,000
\$
26,000
\$
43,000
\$
46,000
\$
56,000
\$
31,000
\$
29,000
\$
83,000
\$
93,000
\$
99,000
\$
39,000
\$
41,000
\$
60,000
\$
100,000
\$
76,000
\$
43,000
\$
77,000
\$
85,000
\$
91,000
\$
39,000
\$
90,000
\$
39,000
\$
92,000
\$
57,000
\$
65,000
\$
22,000
\$
57,000
\$
45,000
\$
50,000
\$
85,000
\$
35,000
\$
97,000
\$
35,000
\$
48,000
\$
55,000
\$
33,000
\$
58,000
\$
45,000
\$
53,000
\$
89,000
\$
84,000
\$
32,000
\$
73,000
Change the range into a table. Sort by Last Name
and then by Salary. Insert a total row showing
average salary. Filter so that only departments
Engineering and QA are shown.
Freeze the first Row and first Column.
Sheet: Pivot 10%
ID
2
6
364
10
12
14
19
23
25
347
358
345
351
361
363
5
9
17
18
22
1
357
344
350
355
360
4
8
16
21
366
353
342
343
349
354
356
3
7
365
11
13
15
20
24
26
27
28
346
348
352
359
362
Last Name
Ackerman
Alderson
Anderson
Bankert
Barnhill
Bashary
Berglund
Bonifaz
Ghanta
Nelson
Omabashi
West
Wilson
Zimprich
Abercrombie
Alberts
Eisner
Benson
Berge
Bolender
Oster
Rand
Smith
Wilson
Yong
Zare
Akers
St. Clair
Ben-Sachar
Berry
Stinson
Stone
Watters
Waxman
Williams
Wood
Young
Ajenstat
Alexander
Bacon Jr.
Barbariol
Barr
Beck
Berndt
Boseman
Bourne
Plotzfield
Wheeler
Willett
Wistrom
Yukish
Zwilling
First Name
Date of Hire Date of Birth Gender
Pilar
4/26/89
10/5/50 F
Gregory F. (Greg)
1/12/92
5/28/64 M
Nancy
4/11/92
11/1/72 F
Julie
4/21/83
Josh
7/26/77
10/12/54 M
Maurice
3/14/95
1/30/72 M
Andreas
3/19/88
10/21/54 M
Luis
6/10/96
4/4/68 M
Raman
6/3/86
11/13/63 M
Cinthia
6/13/85
3/5/76 F
Mariko
3/11/97
8/1/67 F
Paul
11/16/00
2/18/51 M
James C
6/29/77
1/12/76 M
Karin
12/13/82
3/6/73 F
Kim
3/25/85
1/28/55 F
Amy E.
10/15/89
4/22/70 F
Susan
4/6/88
1/14/61 F
Max
11/22/77
9/2/75 M
Karen
6/1/97
2/27/65 F
Corinna
8/20/98
11/18/51 F
Lily
6/24/85
10/16/62 F
Ayn
4/6/86
11/18/74 F
Ed
1/4/93
4/1/71 M
Dan
3/9/90
9/21/75 M
Joe
1/29/85
11/16/77 M
Robert
3/24/94
6/9/77 M
Kim
5/29/79
4/8/58 F
Rebecca
6/6/86
6/20/76 F
Linda
9/21/93
7/12/56 F
Jo
9/20/00
1/7/66 F
Craig
2/21/03
11/16/43 M
Jennifer
12/8/84
11/5/77 F
Jason M.
10/15/77
7/22/72 M
Peter
11/30/74
5/10/76 M
Jill A.
1/12/87
3/26/72 F
John
5/3/88
3/5/68 M
Rob
4/7/88
11/29/58 M
François
2/1/81
12/21/64 M
Sean P
7/6/00
10/16/62 M
Dan K
6/22/74
5/15/49 M
Angela
2/16/96
4/18/64 F
7/9/93
4/8/59 M
12/20/98
2/5/58 M
Matthias
3/17/91
1/28/78 M
Randall
10/18/76
4/2/71 M
Jason
1/18/86
9/10/53 M
Stephanie
3/30/77
6/5/51 F
David M.
2/3/86
1/3/72 M
Wendy
7/15/84
9/10/57 F
Benjamin C.
4/4/77
10/21/61 M
Mark
12/12/78
8/31/74 M
Gary W
10/10/00
2/14/72 M
Michael J.
10/10/92
3/13/62 M
City
Boston
Boston
Boston
Boston
Boston
Boston
Boston
Boston
Boston
Boston
Boston
Boston
Boston
Boston
Chicago
Chicago
Chicago
Chicago
Chicago
Chicago
Chicago
Chicago
Chicago
Chicago
Chicago
Chicago
Houston
Houston
Houston
Houston
Houston
Houston
Houston
Houston
Houston
Houston
Houston
St. Louis
St. Louis
St. Louis
St. Louis
St. Louis
St. Louis
St. Louis
St. Louis
St. Louis
St. Louis
St. Louis
St. Louis
St. Louis
St. Louis
St. Louis
St. Louis
Dept.
Management
Other
Engineering
Engineering
Management
Sales
Management
Other
Sales
Other
Management
Other
Sales
Sales
Sales
Other
Sales
Sales
Sales
Other
Other
Management
Engineering
Other
Management
Engineering
Engineering
Engineering
QA
Other
QA
QA
Sales
Engineering
Sales
Other
Sales
Engineering
Management
Engineering
QA
Engineering
Engineering
Sales
Engineering
Engineering
Sales
QA
QA
Sales
Engineering
Engineering
Sales
Salary
\$
31,000
\$
100,000
\$
39,000
\$
100,000
\$
83,000
\$
35,000
\$
93,000
\$
77,000
\$
55,000
\$
85,000
\$
99,000
\$
39,000
\$
89,000
\$
32,000
\$
50,000
\$
60,000
\$
85,000
\$
97,000
\$
35,000
\$
43,000
\$
91,000
\$
39,000
\$
68,000
\$
90,000
\$
41,000
\$
56,000
\$
47,000
\$
68,000
\$
57,000
\$
76,000
\$
22,000
\$
57,000
\$
58,000
\$
26,000
\$
53,000
\$
39,000
\$
84,000
\$
48,000
\$
29,000
\$
32,000
\$
92,000
\$
66,000
\$
70,000
\$
48,000
\$
74,000
\$
82,000
\$
33,000
\$
65,000
\$
45,000
\$
45,000
\$
43,000
\$
46,000
\$
73,000
Using this Data:
Create a pivot table in a new sheet.
Rows should show city and gender.
Columns should show department.
Values should show count of employees, sum of
salary and average salary.
Sheet: SolverX 20%
WoodMaster
Profit by Panel Type
Type
Profit Per
Total Profit
Tahoe
Pacific
Savannah
0
0
Aspen
Total
0
0
\$
450
\$
1,150
\$
800
\$
400
\$

\$

\$

\$

0
\$

Materials Used by Panel Type
Tahoe
Glue (Quarts)
Pressing (Hours)
Pine chips (Pounds)
Oak Chips (Pounds)
Min Required
(extra Credit)
Pacific
Savannah
Aspen
Available
Remaining
51
48
103
50
5,800
5,800
5
12
10
5
730
730
500
400
300
200
29,200
29,200
500
750
250
500
35,000
35,000
Maximize Profit as shown in cell F6 using solver.
Use the following constraints:
Number of panels produced is an integer.
You cannot run out (>=0) of any of the materials)
You MUST make at least 10 of each type of panel. You can use a constant but note extra
credit opportunity (below).
I have saved a Scenario to Reset the initial Conditions.
For 5 Points Extra Credit Add Constraints to compute the number of panels of each type
from the values in B19:E19. Add 2 Scenarios with the number of panels set to
0 20 30 5 (Named Mo_Money) and another with 20 20 0 20 (Named Save_Glue).
Remember to add the 10 10 10 10 values to my current reset scenario. Your new
Scenarios should include all of the initial constants from the reset scenario as well as your
new Min Required / Constants constraints.
Extra Credit: Use macros and/or visual basic as well as
form controls (buttons). Be creative.
Sheet: LookupData
Age
0
13
18
40
65
Age Groups
Group
Group Name
1 Preteen
2 Teen
4 Mature
Income
5000
25000
50000
70000
100000
Group Status by Income Range
Group1
Group2
Group3
Group4
Exceptional Fair
Poor
Poor
Unbeliveable Exceptional Good
Fair
Unbeliveable Unbeliveable Exceptional Good
Unbeliveable Unbeliveable Unbeliveable Exceptional
Unbeliveable Unbeliveable Unbeliveable Exceptional
This sheet contains data for the Lookup Sheet.
Group5
Fair
Good
Exceptional
Exceptional
Exceptional