Dollar Plan Assignment

AMM 2510A Fashion Retail Buying I

Dr. Yishuang Li, 2021 Fall

Page 1 of 6

WEEK 2: TREND ANALYSIS & CHARTS

Learning Objectives

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

• (Core) Explain the difference between Relative and Absolute Cell References and use both efficiently.

• (Core) Use Autofill to enter numbers and formulas automatically.

• (Core) Visualize series of data using line charts.

• (Core) Customize line charts by adding a title, setting up the horizontal axis, scaling the vertical axis, and change number formats.

• (Extension) Customize line charts by customizing lines and markers.

Activity 1: Use Absolute Cell References to Perform P/L Analysis

Activity

Please open your Week 1 Activity Spreadsheet (or Homework Assignment 1), enable the formula view,

and observe the following cells: COGS%, GM%, OE%, and P%. You may see a pattern for all four

figures (COGS%, GM%, OE%, and P%): they all have a common denominator (Net Sales) and a

common structure for the numerator.

• By default, when you type a formula into a cell, the location of the cell is relative. For example, if you copy the cell calculating COGS% to the GM% cell, both the denominator and the numerator

adjust by one row.

• The numerator in the formula should refer to the cell associated with the destination cell ($COGS, $GM, $OE, & $P), but the denominator should always be the same ($NS).

• Use Figure 1 as an example and try the following: o The formula for COGS% would be “=B3/B2”. If you copy the cell C3 (COGS%) and

paste the formula to C4 (where GM% is), what do you get?

o Now change the formula in C3 to “=B3/B$2” and paste the new formula to C4. What do you get?

Figure 1 P/L Statement Example

Knowledge Base

Relative vs. Absolute Cell Reference

AMM 2510A Fashion Retail Buying I

Dr. Yishuang Li, 2021 Fall

Page 2 of 6

There are two types of cell references in Excel: relative and absolute. Excel interprets a cell reference as

an address. For example, when you type a formula “=A1” in cell B2, it does not treat the string A1 as

plain text but a location that is relative (one row down and one column to the right) to the current cell.

• Relative: By default, a cell reference is a relative reference, which means that the reference is relative to the location of the cell. Relative references change when a formula is copied to another

cell.

• Absolute: Absolute references are those “locked” in position. The range of an absolute reference remains constant no matter where it is copied. To make a reference absolute (i.e., maintain the

original cell reference when being copied), you can add c a dollar sign ($) before the column

and/or row portion of the reference.

• Mixed: Less often, you may want to mix absolute and relative cell references by preceding either the column or the row value with a dollar sign ($).

Table 1 A Summary of Cell References

$A$1 absolute column and absolute row allows neither the column nor the rows to change

$A1 absolute column and relative row allows the row reference to change, but no the column

A$1 relative column and absolute row allows the column reference to change, but no the row

A1 relative column and relative row allows both the column and the row to change * Even with an absolute referencing style, Excel will still change row and column references when you insert a row

or column.

Switch between Relative, Absolute, and Mixed references using the F4 Key

Although you can type the dollar signs manually, the F4 key on your keyboard allows you to add both

dollar signs with a single press1. F4 cycles through all four types of cell references (absolute, mixed

reference (2x), and relative).

Resources

Relative and Absolute References:

https://support.microsoft.com/en-us/office/switch-between-relative-absolute-and-mixed-references-

dfec08cd-ae65-4f56-839e-5f0d8d0baca9

Activity 2: Calculate Sales Trends using Autofill

Activity

We have Abercrombie’s annual sales figures between 2009 and 2018. The original data are listed in the

table below. The first step before further analysis is to enter all data into EXCEL.

Table 2 Abercrombie & Fitch Co (NYS: ANF) Net Sales in Thousands of USD

Year NS in Thousands Year NS in Thousands

2009 $2,928,626 2014 $3,744,030

2010 $3,468,777 2015 $3,518,680

2011 $4,158,058 2016 $3,326,740

2012 $4,510,805 2017 $3,492,690

2013 $4,116,897 2018 $3,590,109

1 *On some keyboard, F4 may be assigned to other functions (e.g., control volume) by default.

AMM 2510A Fashion Retail Buying I

Dr. Yishuang Li, 2021 Fall

Page 3 of 6

1. Setup an NS table in Excel and enter the header information in Table 2. Recall that variables are usually entered in columns, while data entries are usually arranged in rows.

2. To enter the year column, manually type the first and second years (2009 and 2010) only. 3. Use autofill to complete the remaining years. 4. Enter all NS figures manually.

Knowledge Base

Use AutoFill in Excel

1. Enter the first two values into the first two consecutive cells (row/column-wise).

• For a series like 1, 2, 3, 4, 5..., enter 1 and 2 in the first two cells.

• For the series 2, 4, 6, 8..., enter 2 and 4.

• For the series 2, 2, 2, 2..., type 2 in the first cell only. 2. Select both cells and position the mouse on the fill handle (a +

sign) in the bottom-right corner of the selected cells.

3. Drag the fill handle as far as you need. 4. If needed, click Auto Fill Options and choose the option you want.

Activity 3: Calculate Sales Trend using Autofill

Activity

Now we would like to understand how the sales trend (percentage increase or decrease in sales) changes

over the years. Thus, we need to calculate A&F’s sales trend figures between 2009 and 2018.

1. Add a third column on the right of sales and name the third column “$NS Trend.” 2. Type the trend formula into the first trend cell (the one to the right of 2010’s sales figure)

between 2009 and 2010.

o Trend% = 𝑆𝑎𝑙𝑒𝑠 (𝑇ℎ𝑖𝑠 𝑌𝑒𝑎𝑟)−𝑆𝑎𝑙𝑒𝑠 (𝐿𝑎𝑠𝑡 𝑌𝑒𝑎𝑟)

𝑆𝑎𝑙𝑒𝑠 (𝐿𝑎𝑠𝑡 𝑌𝑒𝑎𝑟)

o Since no data for the year before 2009 is available, no trend data can be calculated for this year. Hence the trend data start from 2010.

3. Use autofill to complete the remaining trend column. 4. Format all trend cells as percentages.

Knowledge Base

Copy & Paste

You can also select and copy the first cell (CTRL+C for PC or CMD+C for macOS), select all cells for

the remaining year, and paste the formula (CTRL+V or CMD+V). Excel should automatically detect and

change the cell reference in the formula corresponding to the final response cell position.

Autofill Formulas

Instead, you can use the AutoFill function to copy the formula.

1. Select the cell that has the formula you want to fill into adjacent cells.

Figure 2. Auto Fill Handle

AMM 2510A Fashion Retail Buying I

Dr. Yishuang Li, 2021 Fall

Page 4 of 6

2. Drag the fill handle across the cells that you want to fill.2 3. To change how you want to fill the selection, click the small Auto Fill Options icon button that

appears after you finish dragging, and choose the option you want.

Activity 4: Plot Trends Using Excel

Activity

Use the $NS chart in Activity 3, plot a sales ($NS) chart to show how the sales changed over the past ten

years.

Ask yourself the following questions to plan the chart:

• What best summarize your chart? What information do you want to visualize? What variables will be used?

• Think about the horizontal and vertical axes. What information should be reflected in the axes?

• What is the range and scale (unit) of your major axis?

• Do you have the raw data in a chart for use? The following procedures are recommended to get a figure that is similar to Figure 3:

1. To show figures in the chart accurately, create an NS column that is not scaled in thousands. 2. Select all 10 (newly created) $NS cells to create the chart. 3. Insert a Line Chart (e.g., Line or Line with Markers). 4. Customize the following chart elements:

o Label the horizontal and vertical axes to carry accurate information. o Use a proper unit and number format to show figures in reasonable ranges. o Add a title that is meaningful to your readers. o (Extension) Change the look of the data marks and the connecting lines.

Figure 3 Sample Line Chart

2 If you don’t see the fill handle, it might be hidden. To display it again:

• Click File > Options

• Click Advanced.

• Under Editing Options, check the Enable fill handle and cell drag-and-drop box.

$2,000

$2,500

$3,000

$3,500

$4,000

$4,500

$5,000

2009 2010 2011 2012 2013 2014 2015 2016 2017 2018

M il

li o

n s

A&F's Net Sales (2009-2018) in Millions

AMM 2510A Fashion Retail Buying I

Dr. Yishuang Li, 2021 Fall

Page 5 of 6

Knowledge

Providing visual representation is a great way to enhance the understandability of data. Line charts are

one of the standard graph options in Excel that are best for illustrating trends over time.

Create a Line Chart

To create a sales line chart is just a click away with Excel.

1. Select the data range (the cells containing numbers). 2. On the Insert tab, in the Charts group, click the Line symbol, select one among many line graphs

(e.g., Line with Markers).

Customize a Line Chart

To edit the horizontal axis, follow the steps below (see Figure 4):

1. Pull out the Select Data window by doing either one of the following:

• Click anywhere on the line chart, scroll to the Design tab, in the Data group, click on Select Data.

• Make a right-click anywhere on the chart and choose Select Data. 2. On the pop-up menu, under the Horizontal Axis, hit Edit. 3. Select the cell range for the x-axis. Very often, it is a column that carries categorical data. Click

on OK to go back to the Select Data menu.

4. Click on the OK button to confirm.

Figure 4 Open the Select Data Dialogue Menu to Edit Horizontal Axis

To format an axis (see Figure 5),

1. Right-click on an axis (horizontal or vertical) and select Format Axis on the pop-up menu. 2. Open sub-menus (Axis Options, Tick Marks, Labels, and Number) to view customization options.

• Under Axis Options, you can set up the scale (i.e., more or less scattered) of the chart by editing the maximum and minimum values (the top and bottom line of the chart), change

the line intervals by editing major and minor units, and avoid gigantic numbers by

displaying numbers in thousands or millions,

• Under Number, you can change the numbers formats by changing the number category and specifying decimal places.

To customize how a line (or data points) looks (see Figure 5),

AMM 2510A Fashion Retail Buying I

Dr. Yishuang Li, 2021 Fall

Page 6 of 6

1. Right-click the connecting line (or the markers) and select Format Data Series. 2. Choose the Line (or Marker) sub-menu for more options such as color, transparency, width, and

dash type.

Figure 5 Customization Options to Format Axis and Data Series

Resources

Format Elements of a Chart: https://support.microsoft.com/en-us/office/format-elements-of-a-chart-

b6c787d5-f90a-41d2-a901-9d3ed9f0dbf0