Dollar Plan Assignment

AMM 2510A Fashion Retail Buying I

Dr. Yishuang Li, 2021 Fall

Page 1 of 8

WEEK 1: EXCEL BASICS

Learning Objectives

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

• Identify the expectations of this class and make a plan to succeed.

• (Core) Explain and distinguish the following concepts in Excel: o Columns vs. rows o Workbooks vs. worksheets o Table vs. charts

• (Core) Enter and edit data efficiently.

• (Core) Use basic EXCEL formulas to perform profit and loss calculations.

• (Core) Enable the formula view to master all formulas in one place.

• (Core) Set up a Profit and Loss Statement.

• (Extension) Use styles and themes to change the appearance of a table quickly.

• (Extension) Create an Excel template for homework assignments.

Introduction

Fashion marketers and professionals are often inundated with data. With the help of Excel, we can make

sense of our data, spot important trends, and build reports that leads to meaningful decisions. Excel’s

forte, among many others, is performing numerical calculations (if you are thinking of a bunch of

complicated formulas). But Excel is also very powerful in performing formula-free tasks. Below are just a

few of the uses:

• Access data: Import data from a wide variety of sources. For example, you can connect Excel to Microsoft Access and make your report refreshable.

• Organize and sort data: Use a combination of rows, columns, filters, and slicers to store and organize data efficiently.

• Visualize data with charts and diagrams: Create a wide variety yet customizable charts to make sense of the data.

• Automating complex tasks: Record and automate repetitive processes with Macro capacities that you can run over with just a button.

Let us launch Excel and start from the basics.

Activity 1: Understand Key Concepts

Activity

Open the sample spreadsheet file (Week 2 Sales Performance Sample), identify the following:

• the first worksheet and the whole workbook

• a table and a chart

• (on the second sheet) the column number and row number of the title “Northwind Traders”

Knowledge Base

AMM 2510A Fashion Retail Buying I

Dr. Yishuang Li, 2021 Fall

Page 2 of 8

Excel 101

• Cell: a cell is a rectangular box that occurs at the intersection of a vertical column and a horizontal row in a worksheet. Each cell has its own set of coordinates or position in the

worksheet

• Columns vs. rows: Each row is identified by row number and runs horizontally, while each column is identified by column header and runs vertically. By default, Excel uses the A1

reference style, which refers to columns as letters (A through IV, for a total of 256 columns), and

refers to rows as numbers (1 through 65,536).

• Workbooks vs. worksheets: a worksheet is a single spreadsheet (i.e., a page), whereas a collection of such worksheets is referred as a workbook (i.e., a complete binder of sheets).

• Table vs. charts: a table is the representation of data in rows and columns, while a chart is the graphical representation of data in symbols like bars, lines, and slices.

Activity 2: Enter and Edit Data

Data entry is one of the most basic things we do in Excel. However, Excel does not treat all data equally.

Let’s first learn about three basic types of data we can enter in Excel.

Activity

1. Enter the following data on a new workbook in Column A. Observe how the data are recognized and stored in Excel.

• 12oz

• ’12 (a leading apostrophe before the number 12)

• 12

• Sep 1, 2020

• 2020/08/31, Saturday

• $2000

• 2000 USD

• 3801 West Temple Avenue, Pomona, CA 91768 2. Adjust the width of Column A by dragging the boundary of column A left and right. Observe how

the data are displayed differently when the width changes.

3. Select all text cells on the page.

Knowledge Base

Data Types in Excel

• Numeric: Numerical values can be fractions (1/3), dates (2020/08/20), times (4:00 pm), or currencies ($800). numerical entries in Excel, when entered, are always right aligned.

• Text or label: Texts are descriptive pieces of information that often server as labels, headings, or instructions. Excel aligns text to the left side of the cell. A leading apostrophe tells Excel that this

cell should be treated as text regardless of a number or a formula is within)

• Formula: Formulas are instructions for Excel to perform calculations. They can be simple mathematical expressions or functions that are built in Excel.

Correct ### Error in Excel

If your spreadsheet displays pound signs (###) or asterisks (***) in a cell instead of data, it is usually

because the column is not wide enough to display the value. To display the value correctly, you can take

one of the following actions:

AMM 2510A Fashion Retail Buying I

Dr. Yishuang Li, 2021 Fall

Page 3 of 8

• Edit the text using fewer words.

• Increase the width of the column (drag the border in the column letter display).

• Use a smaller font.

• Wrap the text within the cell so that it more than one line.

Select Multiple Cells in Excel

• Click and drag to select adjacent cells.

• Click the first cell, press and hold the CTRL (Windows) or CMD (Mac) on the keyboard while

select through the last cell.

• Click the first cell, press Shift on the keyboard and click the last cell.

Resources

• (1’05”) Select cells in Excel by Microsoft 365: https://youtu.be/7SGcyxNE0TQ

Activity 3: Format Numbers

Activity

Suppose that you are working on a Profit/Loss table for your online business. Use the following numbers

to set up a P/L table in Excel.

Table 1 P/L Calculation Case

$ %

Net Sales (NS) 100

Cost of Goods Sold (COGS) 40

Gross Margin (GM)

Total Operating Expenses (OE) 40

Operating Profit or Loss (P/L)

1. Format the dollar column so that all dollar numbers carry a dollar sign. 2. Format the percentage column so that all percentage dollars carry a percent sign.

Knowledge Base

By default, Excel displays numeric values as plain numbers. You can ask for a custom number format,

such as currency, percentage, decimals, dates, and phone numbers. To see all available number formats in

Excel, click the Dialog Box Launcher next to Number on the Home tab in the Number group.

Figure 2 Format Number Formats

Custom Number Format

← Home tab button

right-click menu→ Figure 1 Format a Cell in Excel

AMM 2510A Fashion Retail Buying I

Dr. Yishuang Li, 2021 Fall

Page 4 of 8

1. Select the cell(s) to be formatted. 2. Use of the following methods:

a. open the format list from the dropdown list b. right-click, and then click Format Cells to open the dialog box c. click the Dialog box to open the “Format Cells” dialog box

3. Select the format you want.

Resources

• (0’36”) Format numbers in cells in Microsoft Excel by Microsoft 365: https://youtu.be/2suE4YmZu_Q

Activity 4: Use Formula to Perform P/L Calculations

Activity

Use the table created in Activity 3 to calculate the GM and P/L in both dollars and percent. You will get

your answer like the following table below.

Table 2 Case Answer Key

$ %

Net Sales (NS) $100.00 100.00%

Cost of Goods Sold (COGS) $40.00 40.00%

Gross Margin (GM) $60.00 60.00%

Total Operating Expenses (OE) $40.00 40.00%

Operating Profit or Loss (P/L) $20.00 50.00%

Knowledge Base

Formulas in Excel always start with an equal sign (=). You can add constants (numeric figures such as

20), cell reference (such as A1), and calculation operators (such as plus and minus) to create a simple

formula or use built-in functions (such as SUM) for more complex formulas.

Resources

• (1’36”) Creating Microsoft Excel formulas and functions by Microsoft 365: https://youtu.be/qqU2xllewk4

Activity 5: Enable Formula View

Activity

Enable the formula view for the P/L case calculation and double-check your calculation. You will have a

view like the following figure.

AMM 2510A Fashion Retail Buying I

Dr. Yishuang Li, 2021 Fall

Page 5 of 8

Table 3 Formula View of the Case (see Table 2 for the value view)

Knowledge Base

By default, Excel shows you the calculation results of the formulas. To view the formulas instead, you

can select a cell and check out the formula in the formula bar. Sometimes, you may find it useful to

display embedded formulas instead of their results to comprehend how all formulas relate each other.

Excel provides a really simple and quick shortcut to toggle between displaying values and formulas.

To enable the formula view so that all formulas on the page are displayed, you can do either of the

following:

a) press CTRL and ` (the grave accent key) together.

b) on the Formulas tab, in the Formula Auditing group, click Show Formulas.

Figure 3 Two Ways to Enable Formula View in Excel

Activity 6: Create Your Own Assignment Template

No template is going to be perfect right of the bat. But you can always create a template that work for you

in certain occasions.

Activity

Please set up a template that has the following format by default:

• Start a blank new Excel file.

• Go to Cell Styles and right-click on the “Normal” style. Select Modify from the menu (see Figure 5).

• Change font and size according to the Course Syllabus. You may change heading styles too. o Font: Times Roman or Arial o Size 10-12.

• Go to the View tab. Within the Workbook Views group, switch from the Normal view to Page Layout.

• Click on the space where a grey “Add header” text is, create a custom Header.

AMM 2510A Fashion Retail Buying I

Dr. Yishuang Li, 2021 Fall

Page 6 of 8

o Place your name on the left, assignment number in the middle, and the due date on the right (see Figure 4).

o You will edit the due date and assignment number when you use this template to submit your assignment.

• Go to the Page Layout tab, within the Sheet Option group, make sure the check box for View under Gridlines and Headings are checked. You now should be able to see row and column

headings now (see Figure 4)

• Save the current worksheet as a template (.xltx). You can simply name it “AMM 2510A Template”

• Create a new Excel file and find the template in your Personal collection.

Figure 4 View Excel Headings and Create Page Header

Knowledge Base

Styles, themes, and templates are important tools to setup the appearance of a worksheet.

• Style: A cell style in Excel is a combination of formatting options that you can name and save as part of the worksheet. If you want to change text formatting quickly, Styles are the most effective

tools.

• Theme: A theme is a bundle of default collections of colors schemes, fonts, and effects that you can apply to a workbook with a lick. You can apply a theme to Word, Excel, and PowerPoint

documents to get a similar look and feel across all files with the same purpose.

• Template: A template is a predesigned layout (i.e., format, formulas, numbers) with contents that you can use as a starting point to create more workbooks. For example, you can create an

assignment template instead of starting from scratch every time.

AMM 2510A Fashion Retail Buying I

Dr. Yishuang Li, 2021 Fall

Page 7 of 8

Figure 5 Apply and Modify Cell Style

Create a workbook based on the template:

1. click File > New (see Figure 6) 2. click Personal or Office provided categories. 3. double click the template you want to use.

Figure 6 Create a file using Templates: (Left) Windows and (Right) macOS

Save a workbook as a template:

1. click File > Save as Template 2. the template file as “.xltx” as the file extension.

Resources

The following video illustrates procedures to apply styles in Excel.

AMM 2510A Fashion Retail Buying I

Dr. Yishuang Li, 2021 Fall

Page 8 of 8

https://youtu.be/gz-aH9JBjuM