Dollar Plan Assignment

AMM 2510A Fashion Retail Buying I

Dr. Yishuang Li, 2021 Fall

Page 1 of 3

WEEK 5: MARKDOWNS & SUMIF

Learning Objectives

By the end of this week, you should be able to:

• (Core) Set up a markdown table.

• (Core) Use markdown formulas to calculate total sales and markdowns.

• (Core) Use the SUMIF function to sum values in a range that meet specific criterion.

• (Review) Use absolute cell reference in a SUMIF function.

Case Problem

The boy's department took a weekend flash sale and recorded a good sales volume (see Table 1). Three

styles of jackets and two styles of overalls were involved in this flash sale. The price of the remaining

items returned to their original value after the sale. Calculate the total sales, markdown, and on-hand

inventory for both jackets and overalls.

Table 1. Merchandise List

Description Orig. $R Total Units Units Sold MD%

Primaloft Puffer Jacket $48.00 20 10 20.00%

Bomber Jacket $40.00 20 8 10.00%

Sherpa Lined Denim Jacket $19.99 40 32 10.00%

Primaloft Puff Overall $24.99 40 28 10.00%

Primaloft Camo Puff Overall $56.00 24 18 20.00%

Activity 1: Set up a Markdown Table

Activity

1. Set up a table in Excel to calculate markdowns, sales, and on hand inventory in dollars and percent. You can use Table 2 as an example. Note that in this table, you have shaded cells for further

calculations.

2. Enter the numerical information provided in the question in the respective cells. Make sure that all numbers provided in the question are entered in the right cell.

3. Calculate shaded areas of the table. a. Compute Units On Hand (OH) for each of the five products b. Compute the Unit MD ($) and New Unit R ($) figures for each of the five products. c. Compute Total MD ($), NS ($), and Total On Hand Inventory ($) for each of the five

products.

d. Compute the total row for the entire purchase.

AMM 2510A Fashion Retail Buying I

Dr. Yishuang Li, 2021 Fall

Page 2 of 3

Table 2 Example Markdown Table

Description

Orig.

$R

Total

Units

Units

Sold

Units

OH MD%

Unit

MD ($)

New Unit

R ($)

Total

MD ($)

NS

($)

Total

OH ($)

Total

Knowledge Base

Markdown Formulas

You may need to refer to the following formulas:

• MD % = MD ($)

Orig. R ($) =

Orig. R ($)−New R ($)

Orig. R ($)

• MD ($) = Orig. R ($)-New R ($) = Orig. R ($) × MD %

• Total MD ($) = sum of MD ($) for all items

• NS ($) = Unit New R ($) * Units (of sold items)

• On Hand Inventory ($) = Current R ($) * Units (of remaining items)

Activity 2: Performance Analysis

Activity

Based on activity 1, analyze the performances of the two product lines (jackets and overalls) in terms of

the overall sales, markdowns, and the current inventory.

1. Set up a performance analysis table. Note that the performances will be evaluated by product line (jackets and overalls). See Table 3 as an example.

2. Complete the analysis table using numbers in the markdown table (see Table 2). Use the SUMIF and absolute cell references if appliable.

a. We would like Excel to add values of some cells based on a criterion. b. The criterion is whether the cell contains a keyword (e.g., Jacket or Overall). c. See Week 2’s handout (Activity 1) for an explanation of absolute vs. relative cell

references.

Table 3 Example Summary Table

Description # Units Sold # Units OH Total MD ($) Total Sales ($) Total OH ($)

Jackets

Overalls

TOTAL

Knowledge Base

The SUMIF function returns the sum of cells in a range that meet a single condition (for multiple

conditions, use SUMIFS). The SUMIF function syntax has the following arguments:

AMM 2510A Fashion Retail Buying I

Dr. Yishuang Li, 2021 Fall

Page 3 of 3

• range (required): The range of cells to be evaluated by criteria. Cells in each range must be numbers or names, arrays, or references that contain numbers.

o Blank and text values are ignored. o The selected range may contain dates in standard Excel format.

• criteria (required): The criteria in the form of a number, expression, a cell reference, text, or a function that defines which cells will be added.

o The criteria can be numeric. For example, the criteria can be expressed as 32 (choose only the cells that have the value of 32) or ">32" (with the double quotation marks,

choose the cells that have values large than 32).

o Text criteria can be used but must be enclosed in double quotation marks. For example, "apple" means choose the cells that have the text apple.

▪ IMPORTANT: For text criteria, SUMIF is NOT case sensitive.

• sum_range (optional): The actual cells to add if you want to add cells other than those specified in the range argument. By default, Excel adds the cells that are specified in the range argument

(the same cells to which the criterion is applied).

• You can use the wildcard characters—the question mark (?) and asterisk (*)—as the criteria argument.

o A question mark (?) matches any single character. For example, "?s" means to find cells that have only two characters and end with an "s" (e.g., "is" and "as" are possible matches

but not "has").

o An asterisk (*) matches any sequence of characters. For example, "*es" means to find cells that contain a word with an "es" ending (e.g., apples, watches, and oranges are all

targeted)

o If you want to find an actual question mark or asterisk, type a tilde (~) preceding the character.

Resources

• SUMIF function page from Microsoft: https://support.microsoft.com/en-us/office/sumif-function- 169b8c99-c05c-4483-a712-1697a653039b