MAX 502 Homework 3 Prof.
Panchal
MBA 504 Fall 2013
Problem 1: A local retailer wants to send reward offers to a subset of its customers who use the retailer’s store-
issued credit card.
The reward offers will depend on a variety of factors, including when the customer opened the account (year),
the type of account (business or personal), and the spending history, as follows:
• If total purchases in the last year are more than $4,000 or the purchases in the last two years add to
more than $6,000, then the customer receives “2.5% APR” offer.
• If a customer’s purchases are less than the amounts above, but the customer has been a member for at
least five years (since 2017 or earlier) and has a “Personal” account, he/she will get “No Annual Fee”; if
the customer has a “Business” account, and has been a member for six years or more, then he/she gets
“4% APR”
• If the customer does not meet any of the criteria above, he/she gets “No Reward”.
a. On the Problem 1 worksheet, under the “Reward” column, fill in the appropriate reward offer for each
customer. Use nested IF statements, along with the AND and OR functions.
b. Insert column sparklines to show purchase levels over years for each customer.
Problem 2: Marianne Sanders of JMB Auto Sales needs your help. Several times each year, JMB runs sales
promotions to incentivize individual dealerships to sell more cars. Sometimes the promotions focus on specific
car models and sometimes they are focused on overall sales volume. This year the firm is running three
different promotions:
1. Dealers can earn rebates on car transport expenses by exceeding expected quarterly volumes. Rebates are
awarded on a quarter-by-quarter basis, but only for quarters where the dealerships actual sales exceed
expected volumes for that quarter. Expected sales volumes are as follows:
o Q1 – 325 cars; Q2 – 425 cars; Q3 – 440 cars; Q4 – 350 cars
Rebate amounts for each quarter are as follows:
o Q1 - $65 per car sold by the dealership
o Q2 - $80 per car sold; Q3 - $65 per car sold; Q4 - $122 per car sold
2. Dealerships that exceed the expected annual sales volume can earn a bonus. Those that exceed expected
annual volume by more than 7% are awarded a $10,000 bonus and those that exceed the expected annual
volume by 7% or less are awarded a $5,000 bonus. Otherwise, no bonus is awarded.
3. A “Top Performer” bonus of $6,000 is awarded to the dealership with the highest overall sales volume.
Marianne has already entered the quarterly sales volumes, annual expected volumes for each dealership, as well
as the target and thresholds needed to calculate the rebates and bonuses in the spreadsheet. Your job is to
complete the analysis and provide Marianne with an attractively formatted, easy to understand, well-labeled
worksheet that will apply the appropriate promotional awards to each dealership. Be sure to include the
following:
a. In the column adjacent to the quarterly sales volume data (B18:E28), calculate the corresponding annual
sales volume for each dealership. Place the result in cells F18:F28.
b. Calculate the value of the transport (or shipping) rebate for each dealer for each quarter (use four new
columns). This should require only one formula that can be copied down the column and across the row.
Be sure your inputs are set up so that this can be easily accomplished. Remember, dealers will receive
rebates only in quarters where their actual quarterly sales volumes exceed expected sales volumes.
c. In the adjacent column, determine the total value of the transport rebate for all four quarters by dealership,
label it “Total Shipping Rebate.”
d. Analyze the quality of these volume estimates by categorizing the
annual volume estimate versus the actual annual volumes for each
dealership. Use the following categories:
i. Display “Excellent” if the estimate is within 5% (higher or
lower) of the actual sales volume.
• For example, if you want to determine if the value
26 is within +/- 25% of 40, you would need to test
26 to make sure that 26>=40-(.25*40) and
26<=40+(.25*40).
ii. Display “Good” if the estimate is greater than 5% higher or
lower, but within 10% higher or lower than the actual volume.
iii. Display “Poor” if the estimate is greater than 10% higher or
lower.
e. In an adjacent column or columns, calculate the value of the annual sales volume bonus for each dealership.
f. In an adjacent column or columns, calculate the value of the “Top Performer” bonus for each dealership
(only the dealership with the highest annual sales volume will receive this; all other will receive $0).
g. In a row below the data, calculate the totals for each type of bonus.
h. In an adjacent column, determine if (TRUE or FALSE) each dealership received money during the year for
both a transport/shipping rebate and a sales volume bonus.
Problem 3: This problem requires you to practice using IF, AND/OR statements in conjunction with HLOOKUP or
VLOOKUP functions. Worksheet Problem 3 contains the exam scores and number of absences for students in a
class. Find the average score for each student, and assign a final grade based on the following criteria:
a. If a student chooses the Pass/Fail option (“Y”), and his/her average score is less than 70 OR he/she has
six or more absences, the student receives an “F” in the course. Otherwise, the student gets a “P”
grade.
If, on the other hand, the student chooses the grade option, i.e., Pass/Fail = “N”, the student’s final
grade is as described in the table below, as long as he/she has fewer than six absences. Otherwise, the
student gets an “F”.
If Average Score is Final Grade
less than 60 F
60 or higher, but less than 70 D
70 or higher, but less than 80 C
80 or higher, but less than 90 B
90 or more A
As an example, here’s the output for a few hypothetical students:
Absences Pass/Fail Average Final
Student Grade
Sunny Delight 0 N 95 A
Lemony Snicket 5 N 45 F
Harry Potter 2 Y 90 P
Edgar Cayce 6 Y 78 F
Roman Warrior 3 N 78 C
Problem 4: The Problem 4 worksheet contains a variation of the Tax Calculations example discussed in class. In
this problem you will once again calculate the income taxes due for each person based on their exempt status
and their taxable income.
Note the following:
• Exempt Employees (Exempt = "Yes") pay no taxes
• Non-exempt Employees (Exempt = "No") pay taxes as follows:
o Taxes = Base Tax Amount + Marginal Tax Rate*(Taxable Income – Income Bracket Minimum)
o Individuals with negative taxable income pay no taxes.
a. Use Reference Functions (HLOOKUP or VLOOKUP) and logical functions (and IF statements) to
calculate the taxes owed, if any, by each individual.
b. Use Nested IF statements to calculate the taxes owed, if any, by each individual.
Problem 5: The purpose of these exercises is to show you how to create a sophisticated-looking chart using
Excel’s standard capabilities. The directions are quite detailed, but it won’t take long to construct the charts.
a. When it comes to Stacked Column Charts, my general advice is: don’t use them. The user is asked to
compare buckets without a fixed basis for comparison. Stacked charts are worse that donut charts
(avoid those as well!). Here’s an example of a simple stack chart comparing the market share of three
social media companies (I made up the data).
Notice that Instagram’s share did not change from 2018 to 2019, and increased in 2020, but the the buckets for
Instagram appear lower each year. To address the shortfalls of stacked charts, we will create a fixed basis to
compare the buckets, as shown in this chart:
Original Data
The original data for this example is: Facebook Instagram WhatsApp
2018 0.65 0.3 0.05
2019 0.5 0.3 0.2
2020 0.25 0.4 0.35
Plot this data (A3:D6) as a stacked chart. You will get this plot:
• This the unformatted version of the first chart shown in this problem.
To create the corrected chart, we must add spaces (fillers) between each of the company bucket, and then
remove their fill, so that only the original data appears in the chart, but it appears anchored.
• To do this, create a new table that includes space fillers
o What does the height represent? It represents how tall each of the firm’s buckets needs to be.
Because the largest value in the table is 0.65 (for Facebook), I set the bucket height to 0.7. For
different data, the bucket will likely need to be rescaled.
• Add new series (e.g., Space 1, Space 2, and Space 3) in between the existing company series.
o Calculate how big the space needs to be for each year, for each company.
§ Do NOT hardcode this!
o Plot the new augmented data using a stacked column chart.
o The space fillers are shown in black in the figure below for illustrative purposes. They will have
different colors in your chart.
• Set the Shape Fill and Shape Outline to No Fill for each of the Space series.
• To get the correct anchoring lines, set the axis bounds as
shown to the right. In other words, we want a line to anchor
the first series (at 0), the second at (0.7), and the third at the
maximum of 2.1. I chose these bounds because they are multiples
of the height I selected.
• Remove the vertical axis.
• Format the chart.
o Add title
o Reposition and format the legend.
o Change colors
o Show Gridlines
o Format horizontal axis
