HAP 489 GMU Volunteer Gift Cards Excel Spreadsheet

Lesson 7: Excel Spreadsheet Skills ReviewExcel Problem to Solve
HAP489 Pre-Internship Seminar
The “Gift Cards” Problem
(Using “Copy”, “Sorting” and “Formulas” Skills)
Objectives: `
1. To learn and practice some basic and intermediate Excel skills in solving a realistic
problem.
2. To identify sources on the internet that you can go to when needing explanations in
using different Excel formulas, functions and procedures.
3. To practice working as a team in solving problems.
The Scenario:
You are planning on interviewing for an internship assignment at Mason Medical Center
(MMC) in Fairfax, VA, along with some of your fellow students, and you are looking
forward to working for Mrs. Green, the Director of Human Resources.
During some pre-interview correspondence she lets you know that if you are accepted
to this internship position that she plans on asking you for your help in a project she is
working on. She wants to reward a group of MMC’s Auxiliary volunteer staff who have
been helping a great deal over the last four months. They have been particularly busy
with various volunteer assignments related to working on a variety of projects. She
explains that MMC wants to express their gratitude by giving out gift cards to those who
have worked during this period.
Here is her plan: To be eligible, a volunteer must work have worked a minimum of 21
hours during this period. Those working 20 hours or less will not receive a gift card.
Those working from 21 to 39 hours would receive a $25 gift card. Those working from
40 to 79 hours would receive a $50 gift card. Those working from 80 to 99 hours would
receive a $75 gift card, and those working from 100 and higher hours would receive a
$100 gift card.
In her work to get her plan approved, she has been asked by MMC’s Chief Financial
Officer to determine its total cost. And, she has been asked by the Chief Operating
Officer to provide a clear list as to who will get the gift cards in each of the four
departments at MMC where these volunteers have been assigned. The listing by
department is needed to efficiently distribute the cards and to be able to charge the cost
of these cards to the appropriate department.
SB 7/3/23 v1.0
Page 1
Mrs. Green has received a listing made by the staff at the Volunteers Office of all the
volunteers who have worked over this time as well as the department where they were
assigned. That listing was prepared on a Word document and has been included as a
separate attachment to this problem.
Mrs. Green was getting ready to manually prepare and calculate from the list which
volunteer would get which level of gift card, and then total the entire cost. She then
planned to provide a list noting the numbers of gift cards by denomination. She thought
that this would be a task that when manually done would take a good bit of time. With
your Excel skills, she thought you might be able to prepare an Excel workbook that
would give her the information in much less time, and more accurately. And she
thought that your workbook might include a simple one-page summary of her proposal
that she could use when seeking approval for her plan from the executives.
She tells you her plan and emails you a copy of the Word document that was prepared
for her noting each volunteer’s name, their department, and their hours worked. She
asks if you would be prepared to discuss with her at your interview scheduled in a few
weeks if you could use Excel to answer the questions she has been asked to address.
She noted to you that if you were to bring a sample of the Excel workbook to the
interview with the information that she is seeking, that would be a big plus.
Your Problem:
Working with a partner if you choose, you need to come up with an Excel
workbook that may have several tabs that would provide Mrs. Green with the
information that she needs. She needs to have a listing of the volunteers sorted in
different ways. She needs to have the payment amount calculated for each volunteer
and then determine the total cost of her proposal.
She needs a computation of how many of the different denominations of gift cards that
she needs to purchase. She needs to have a list sorted by department so that it can be
used to distribute the gift cards and as a way for the accounting staff to distribute the
cost of the total gifts to each of the departments where the volunteers worked. And, she
needs a one page summary that she can use to present to her executives. If the
executives provided her feedback to change the plan, such as giving different amounts,
or changing the range of hours worked, Mrs. Green would like your work to be done in a
way so that you can make those changes fairly quickly so that they could determine the
effects of any suggested changes.
SB
7/3/23 v1.0
Page 2
Things to Consider in Your Solutions:
Mrs. Green is correct when she says that to do this manually would take a lot of time,
and there would be the risk of getting some calculations incorrect. By using the “sort”
feature in Excel, you can sort the list of names in several ways which will be helpful.
And, in making the calculation of the size of gift card each volunteer should receive, you
can use the “Formulas” feature within Excel. One that is particularly useful is called
“VLOOKUP”, which has been updated recently to be “XLOOKUP”. This can match the
table of volunteers to the list of criteria in the second table as to the amount they should
receive. One table “looks up” data in the second table.
While some problems using “VLOOKUP” ask for an “exact match”, this problem does
not require an “exact” match and instead looks for a value to fall within a range of
numbers. Some term this a “RANGE LOOKUP”, however it is the “VLOOKUP” Excel
function that you will use here.
The “COUNT IF” formula is another useful one in this problem. You will be asked to
count the number of cards by denomination and the number of volunteers receiving
each gift card amount. This formula makes these counts.
To get the data into Excel in order to prepare the workbook is a common problem. In
this example you have been given it in the form of a Word table. You can use that to
“copy and paste” from that document into Excel. Getting familiar with and practicing the
“copy and paste” feature as it is an important part of Excel. This works in both receiving
data to put into Excel, and then taking it out of Excel and recording it elsewhere.
When using the “LOOKUP” functions you will also make use of the “copy and paste”
feature when copying the function for the first volunteer’s calculation of their gift card
amount when applying this formula for each volunteer on the following rows on your list.
When copying and pasting in this step, be sure to use the “$” feature before the cells
that define the second table that you create with the criteria noting the size of each gift
card. You will need to understand the concept of “permanent” versus “relative” cell
references and this is a good time to review that concept.
This is an excellent use of the capabilities in Excel. Take to time to learn these features
and you can adapt them to other problems you will encounter down the road.
Required: Prepare the Excel workbook Mrs. Green has requested and upload
your completed workbook in the Bb “Assignment 7” folder. Even if you worked
with a partner, each must submit an Excel workbook to get credit. Please note on
your spreadsheet the student(s) with whom you worked.
SB
7/3/23 v1.0
Page 3
This assignment requires a reflection of what you have learned in the Week 11
Internship Activity Report. Follow the instructions there that will ask you to
comment on the things learned during this activity, any particularly challenging
issues. You will also be asked to note at least two (2) Excel “go-to” resources
you and your partner feel helped you in this problem. Be specific in noting these
resources as you and others should be able to use these in the future when you
encounter other problems and need some guidance on how to use Excel.
For those wanting to share their “go-to” places during the assignment to help
others, an optional Discussion Board Forum is set up for that purpose.
SB
7/3/23 v1.0
Page 4