Dollar Plan Assignment

AMM 2510A Fashion Retail Buying I

Dr. Yishuang Li, 2021 Fall

Page 1 of 4

WEEK 4: MARKUPS & CONDITIONAL

FORMATTING

Learning Objectives

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

• (Core) Set up a markup table.

• (Core) Use markup formulas to calculate three basic pricing elements (i.e., R, C, & M).

• (Core) Calculate average or balancing markup for the entire merchandise.

• (Core) Customize the borders of a table.

• (Core) Use conditional formatting to customize cells with colors and shades.

Case Problem

As a buyer, you are planning the markups for the four different types of shirts: white, plaid, Oxford, and

knit. You have the quantity, unit cost, and planned unit retail price in Table 1. As a buyer, you are going

to develop pricing strategies to manage the entire merchandise to achieve the desired markup percentage.

• Use the given information, set up a markup table, and calculate the average markup for the entire merchandise.

• Identify the purchases with top or bottom performances. Table 1. Merchandise List

Quantity Unit Cost ($) Unit Retail ($)

White Shirts 14 $15 $34

Plaid Shirts 24 $20 $38

Oxford Shirts 45 $14 $30

Knit Shirts 10 $120 / dozen $25

Activity 1: Set up a Markup Table

Activity

1. Set up a table in Excel to calculate markup 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 the Total Cost and Total Retail figures for each of the four product categories. b. Compute $Markup amounts for each of the four product categories. c. Compute Total Cost, Total Retail, and Total Markup for the entire purchase. d. Compute the Markup% for each of the product categories. e. Compute the Average Markup% for the entire purchase.

AMM 2510A Fashion Retail Buying I

Dr. Yishuang Li, 2021 Fall

Page 2 of 4

Table 2 Example Markup Table

Quantity Unit Cost Unit Retail Total Cost Total Retail Markup ($) MU%

White Shirts

Plaid Shirts

Oxford Shirts

Knit Shirts

Total

Knowledge Base

Markup Formulas

You may need to refer to the following formulas:

• MU% = $Retail− $Cost

$Retail

• Total Cost (per merchandise) = Units * Unit Cost

• Total Retail (per merchandise) = Units * Unit Retail

• (Grand, the total row) Total Cost = sum of all total costs

• (Grand, the total row) Total Retail = sum of all total retails

• Average or balancing MU% (for the entire merchandise) = Total $Retail− Total $Cost

Total $Retail

Activity 2: Customize Borders

Activity

Add both outside and inside borders to the markup table created in Activity 1. Use a thick border for the

perimeter of the table.

Knowledge Base

Gridlines vs. Borders

Gridlines in Excel are the horizontal and vertical faint gray lines that differentiate between cells in a

worksheet. The gridlines help users navigate through the columns and rows with ease.

Borders in Excel are box-like frames added to a single cell or multiple cells. Unlike gridlines, borders

always display on the screen and always go to print, regardless of the settings. Borders are useful for

helping the reader’s eyes concentrate in the data area, and for identifying which parts of a spreadsheet go

together.

Show Gridlines

By default, Excel shows gridlines that separate the rows and columns on the screen but not in print. Users

can control how gridlines appear on screen as well as in print.

1. Go to the Page Layout tab, in the Sheet Options group, locate two checkboxes under Gridlines. 2. The check box beside the View controls the onscreen display, whereas the box beside the Print

controls the print view.

Format Borders

1. Go to the Home tab, in the Font group, click the arrow on the right of the border icon to open Borders Menu (shown in Error! Reference source not found..

AMM 2510A Fashion Retail Buying I

Dr. Yishuang Li, 2021 Fall

Page 3 of 4

2. From the list of borders on the menu, choose one that best represents the style you want to apply. 3. To specify a color, style, or weight of the border, choose the More Borders command to open the

Format Cells dialogue box. The dialogue box can also be opened by clicking the arrow on the

Font group and switching to the Border tab.

Figure 1. The Borders Menu

Activity 3: Conditional Formatting

Activity

Use conditional formatting to customize the table in Activity 2:

• highlight the cell that has the highest markup% using a green background

• make the cell with the lowest markup dollars in red font.

Knowledge Base

Conditional formatting changes the appearance of cells on the basis of designated conditions. Not only

does it help make sense of data, but it also enables the viewer to spot important cues in the blink of an

eye. The basic rule of conditional formatting is an if-then logical operation:

• If the conditions are met (true), the cell is formatted.

• If the conditions are not met (false), the cell is not formatted.

Basic Conditional Formatting

1. Select the data range to apply a conditional formatting rule. 2. On the Home tab, in the Styles group, select the arrow next to Conditional Formatting, hover

over the rules, and select one as needed.

AMM 2510A Fashion Retail Buying I

Dr. Yishuang Li, 2021 Fall

Page 4 of 4

a. To format cells using a comparison operator, click Highlight Cell Rules, select one command (e.g., Greater Than, Less Than, Between, etc.), in the pop-out dialog box,

enter the value and edit the cell format.

b. To format top or bottom ranked values, click Top/Bottom Rules, and choose an option as needed.

c. For advanced formatting, click Manage Rules and open the Conditional Formatting Rules Manager dialog box.

3. To clear conditional formatting, click Clear Rules, select either Clear Rules from Entire Sheet, or Clear Rules from Selected Cells.

Resources

• Conditional Formatting: https://youtu.be/h-yvFBg7SWM