|
-
PART ONE:
You are the Marketing Manager for a Credit Card company. You
have been tasked with providing a data loading sheet to create
2000 mock credit cards for a marketing event. Your data will
load the magnetic strip on the mock cards.
To accomplish this you will need to create a spreadsheet with
one credit card record per row. Each record will have this
format:
Name/AccountNumber#AccountAmount~State All in 1 cell.
To accomplish this you will need to create a spreadsheet with
one credit card record per row. Each record will have this
format:
-
Customer Name -- You can just use one name and it just needs
to be a name like (Consonant, Vowel, Consonant, Vowel,
Consonant). Set up a function to generate random fake
names. (You may want to use a VLOOKUP for this.)
-
Account Number -- Set up a function to create a random 6
digit number
-
Account Amount -- Set up a Function to create a 4 digit
random amount ($1,234)
-
State Code -- Set up a function to randomly assign a 2
character State code to each record for one of the 50
states. (You might want to use a VLOOKUP for this)
Once you have the fields, concatenate them together with the
appropriate delimiters (/ # ~)
Note: You will create one record in a way that allows you to
do a fill down to get the other 1,999 records
-
PART TWO:
Reverse the process. Take the magnetic strip record and use
functions (like LEFT, MID, RIGHT, FIND) to break it up so that
each field is in its own cell.
Format the fields so they are appropriate for the type of data.
Then create a Pie chart that shows how much customer money is
in each state.(Note: To do this you will need to sort and count
the data)
|