Author:
Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from
SAM website.
Shelly Cashman Excel 2016 | Module 2: SAM Project 1a
Rough Stitch Clothing
FORMULAS, FUNCTIONS, AND FORMATTING
Faisal Alazaima
edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the
SAM website.
Rough Stitch Clothing
Sales
Department
Women's
Men's
Children's
Babies'
Total
Average
Highest
Lowest
2018
150,200
123,615
85,680
75,000
2019
88,600
47,100
15,417
40,300
2020
125,000
114,580
74,250
62,358
2021
178,956
145,698
105,600
86,400
2020
125,340
45,600
70,200
1,680
242,820
2021
168,950
52,350
70,200
2,050
293,550
Expenses
Items Cost
Payrol
Rent
Other
Total
$
2018
148,500
52,689
68,900
15,230
285,319
$
2019
79,850
42,350
70,200
12,364
204,764
$
$
Profit
2018
Amount
2019
2020
2021
Employee Name
Stephanie Michaelski
Maria Angeles
Jayson Ellerbee
Lily Minamoto
Lauren Ramirez
Last updated
Employee Discount Listing
Department
Dept Code
Women's
010
Women's
010
Men's
021
Children's
034
Babies'
043
Friday, March 9, 2018
Employee ID
SM-WO-010
Hiring Date Discount
1/20/2021
0.05
2/5/2018
0.22
3/7/2019
0.18
5/8/2018
0.22
7/15/2018
0.22
Shelly Cashman Excel 2016 | Module 2: SAM Project 1a
Rough Stitch Clothing
FORMULAS, FUNCTIONS, AND FORMATTING
GETTING STARTED
•
Open the file SC_EX16_2a_FirstLastName_1.xlsx, available for download
from the SAM website.
•
Save the file as SC_EX16_2a_FirstLastName_2.xlsx by changing the “1” to
a “2”.
o
•
If you do not see the .xlsx file extension in the Save As dialog box, do
not type it. The program will add the file extension for you automatically.
With the file SC_EX16_2a_FirstLastName_2.xlsx still open, ensure that
your first and last name is displayed in cell B6 of the Documentation sheet.
o
If cell B6 does not display your name, delete the file and download a new
copy from the SAM website.
PROJECT STEPS
1.
You are an assistant manager at Rough Stitch Clothing, a boutique clothing
store. The store owner has asked you to generate a report detailing the
financials of the store from 2018 to 2021 and, at the same time, format the
store’s staff list.
Switch to the Profit Analysis worksheet and apply the Office theme to the
workbook.
2.
3.
Update the column and row sizes in this worksheet as described below:
a.
Change the width of column A to 15.00.
b.
Apply AutoFit to row 1, so that the row height best fits the content it
contains.
In cell B9, create a formula using the SUM function to total the values in the
range B5:B8 to determine the total sales in 2018 across all departments.
Copy the formula you just created in cell B9 to the range C9:E9 to calculate the
total sales for the following three years.
4.
Format the range B9:E9 using the Accounting number format with zero
decimal places and $ as the symbol. (Hint: Depending on how you complete
this action, the number format may appear as Custom instead of Accounting.)
5.
Change the fill color of the range A9:E9 to Yellow (4th column, 1st row of the
Standard Colors palette).
6.
Apply a Top and Thick Bottom Border cell border to the range A9:E9.
Shelly Cashman Excel 2016 | Module 2: SAM Project 1a
7.
You want to calculate some business statistics (average, highest, and lowest
sales figures) from each year across all departments.
In cell B10, create a formula using the AVERAGE function to calculate the
average sales figure across all departments (as shown in the range B5:B8) in
2018.
Using the Fill Handle, copy the formula you just created in cell B10 to all cells in
the range C10:E10.
8.
In cell B11, create a formula using the MAX function to determine the highest
sales figure across all departments (as shown in the range B5:B8) in 2018.
Copy the formula you just created in cell B11 to the range C11:E11.
9.
In cell B12, create a formula using the MIN function to determine the lowest
sales figure across all departments (as shown in the range B5:B8) in 2018.
Copy the formula you just created in cell B12 to the range C12:E12.
10.
You need to determine the profit generated each year across all departments.
In cell B22, create a formula without using a function that subtracts the total
expenses for 2018 (cell B19) from the total sales for 2018 (cell B9).
Copy the formula you created in cell B22 to the range C22:E22.
11.
You want to visually highlight any year with a loss (i.e., a negative profit).
Apply a conditional formatting rule to the range B22:E22 as described below:
12.
13.
a.
Use the New Rule option. [Mac Hint: In the "New Formatting Rule" dialog
box, select "Classic" from the Style list drop-down box.]
b.
Select the Rule type Format only cells that contain.
c.
Change the Rule Description to format only cells with a cell value of
less than 0 with a Red fill color (2nd column, 1st row of the Standard
Colors palette).
Modify the worksheet format as described below:
a.
Change the page orientation of the worksheet to Portrait.
b.
Change the margins to Narrow.
Add a header to the worksheet, and then enter Rough Stitch Clothing Profit
Analysis in the center section.
Return to Normal View.
14.
Click on cell A1, and then check the Spelling to identify and correct any spelling
errors on the worksheet. (Hint: You should find and correct at least one spelling
error.)
15.
Switch to the Employee Discount worksheet. Rough Stitch Clothing offers
employees discounts based on how long an employee has worked at the store.
All employees forgot to enter their employee IDs, so you need to input this
information. The ID of an employee consists of the first letter of his/her first
name and last name together followed by a dash, the first two letters of the
department followed by a dash, and the department code.
Shelly Cashman Excel 2016 | Module 2: SAM Project 1a
For instance, in cell D4, enter the text MA-WO-010. Select the range D3:D7,
and then use Flash Fill to fill the range. [Mac Hint: Flash Fill is not available in
Excel 2016 for Mac, so refer to the Final Figure to enter the text.] (Hint: Be
sure to preserve the formatting of the range D3:D7.)
16.
The range F3:F7 shows each employee’s discount.
Format the range F3:F7 using the Percentage number format with zero
decimal places.
17.
Format the date value in cell B9 using the Short Date (e.g., 3/15/2018)
number format.
18.
Change the page orientation of the worksheet to Landscape.
Your workbook should look like the Final Figures on the following pages. Save your
changes, close the workbook, and then exit Excel. Follow the directions on the SAM
website to submit your completed project.
Final Figure 1: Profit Analysis Worksheet
Shelly Cashman Excel 2016 | Module 2: SAM Project 1a
Final Figure 2: Employee Discount Worksheet
Purchase answer to see full
attachment