Start with the Excel spreadsheet – Tile Mart
Tile Mart sells a variety of tile products for interior and exterior home project such as porcelain, ceramic,
and glass. Tile Mart prides itself on being a discount store and has a variety of discounts that they offer
to their customers. For example, if a customer orders 100 square feet of tile or less, they do not receive
a discount. However, if a customer orders between 101 and 200 square feet of tile, the customer
receives a 7.5

% discount off the regular price. Likewise, if a customer purchases 201 or more square
feet, then they receive a 15% discount. For these volume-based discounts, these will be referred to as
Discount Code 1. Tile Mart has a number of repeat customers. Thus, to promote repeat business, Tile
Mart offers an additional 10% off to repeat customers. This case will be referred to as Discount Code 2.
Finally, Tile Mart offers a 5% discount for anyone placing an order that is 65 years of age or older. This
case will be referred to as Discount Code 3. It should be noted that discounts could accumulate. For
example, a customer can receive a discount based on volume, and being a senior.
In the starting spreadsheet, you will find 100 of Tile Mart’s latest transactions. You will find customer
information in columns A through E, where two columns are particularly important. For example, you
will find the customer Age in column C and whether a customer is considered a repeat customer in
column E (where a Y stands for Yes, and an N stands for No). You will also find the exact square footage
of tile that was ordered by the customer in column F, and the base price per square foot of the
purchased tile in column G.
Based on the above information, you have been asked to calculate several fields within the provided
template. Initially there are four columns that will need to be calculated sequentially. These columns are
located in columns H, I, J, and K. You will first calculate the base cost in column H. Then, you will
calculate the total cost if a Discount Code 1 applies in column I. Then, based on your value in column I,
you will reduce the cost further based on the condition of Discount code 2 in column J. Finally, based on
your calculation in column J, you ill will reduce the cost further based on the condition of Discount code
3. Finally, you will be asked to compute a percentage of total savings in column L.
Your task is as follows:
1. For each customer, you are to determine the total cost of their purchase without any discounts
applied in column H. Develop a formula in H2 that can be filled down to H101.
a. HINT: Total cost is computed as total square feet ordered multiplied by the base price per square foot.
2. Next, you are to determine the total cost based on Discount Code 1 in column I. Start by writing a
formula in I2 which can be filled down to I101.
a. HINT: Use an IF statement to determine the appropriate discount that should be applied based on the
order volume in column F, and the discount percentage in cells Q2:Q4.
b. HINT: In order to formulate this in a single cell, you will need to use two IF statements.
c. HINT: The discounted cost is determined by multiplying the base cost by subtracting the percent
discount from 100. In other words, if a 15% discount should be applied. The reduced cost would be the
base cost multiplied by (1-0.15), or 0.85, or 85%.
Tile Mart
3. Next, you are to determine the total cost based on Discount Code 2 in column J after Discount Code 1
has been applied from column I. Start by writing a formula in J2, which can be filled down to I101.
a. HINT: Use an IF statement to determine the appropriate discount that should be applied based on
whether the customer is considered a repeat customer from column E, and the and the discount
percentage in cell Q5.
b. HINT: If a discount does not apply, then the calculation should display the cost shown in column I.
4. Next, you are to determine the total cost based on Discount Code 3 in column K after Discount Code 1
and 2 has been applied from column J. Start by writing a formula in K2, which can be filled down to
K101.
a. HINT: Use an IF statement to determine the appropriate discount that should be applied based on the
customers age from column C, and the and the discount percentage in cell Q6.
b. HINT: If a discount does not apply, then the calculation should display the cost shown in column J.
5. Next, you are to compute each customer’s percent savings in column L. Start by writing a formula in
L2, which can be filled down to L101.
a. HINT: calculate percent savings by the following formula:
%???????= (????? ???? ??????? ??????????????? ???? ???? ???????? ???? 3)???? ???? ???????
?????????
6. Finally, for the range O9:P13, you are to summarize the savings offered to customers. For this
summary, a customer will fit into one of three categories. First, a customer may not receive any
discount, which is represented by “0%” in cell O10. Second, a customer receive a discount above 0% but
less than or equal to 10%, which is represented in O11. Third, a customer may receive a discount, which
is above 10%, which is represented in 012. Thus, develop three separate formulas in cells P10, P11, and
P12 that counts how many customers fit into each of the three savings categories.
a. HINT: You will need to use a COUNTIF, or COUNTIFS formula to achieve this count. In some situations
you can utilize discrete criteria conditions, others you will need continuous criteria conditions.
b. For cell P13, calculate the sum of the values in P10:P12. HINT: the result of this formula should be
100.
Now would be a good time to save your work.
Place your order now for a similar paper and have exceptional work written by our team of experts to guarantee you A Results

Why Choose US

6+ years experience on custom writing
80% Return Client
Urgent 2 Hrs Delivery
Your Privacy Guaranteed
Unlimited Free Revisions
Is this question part of your assignment?
Place order
Posted on May 23, 2016Author TutorCategories Question, Questions