Annual Net Sales Worksheet

Front Range Action SportsAnnual Net Sales
Quarter 1
Colorado
$
48,123,789
Quarter 2
$
Quarter 3
42,468,256 $
45,159,681
Quarter 4
$
49,452,695
New Mexico
Oregon
25,783,516
35,658,498
21,985,365
34,689,526
19,987,269
37,986,369
22,252,487
39,456,899
California
Washington
Total Sales
58,123,789
42,143,258
64,468,256
46,598,456
65,159,681
44,874,332
68,452,695
50,546,222
Total
Percent of
Total Sales
Trend
Seattle Facility: Inventory Status of Apparel
As of December 31
Total Items in Stock
Average Price
271
Median Price
Lowest Price
Highest Price
Ski Products
Ski Quantity in Stock
Quantity in Stock
Item #
Item Name
32
115689 Deluxe Biking Shorts
78
114568 Vented Explorer
28
456897 Wicked Heavy Socks
58
465899 Gripper Gloves
57
465888 Airflow Hat
18
165332 Mini Crew Socks
Retail Price
Category
69.52 Pants
34.78 Hats
9.35 Socks and Gloves
25.55 Socks and Gloves
2.99 Hats
6.45 Socks and Gloves
of Apparel
Sport
Biking
Hiking
Skiing
Skiing
Hiking
Fitness
Stock Level
Denver Facility: Inventory Status of Apparel
As of December 31
Total Items in Stock
Average Price
Median Price
Lowest Price
265
9.68
6.45
2.99
$
$
$
Highest Price
$ 18.98
Number of Biking Products
Biking Quantity in Stock
Quantity in Stock
1
58
Item #
Item Name
58
965888 Cotton Visor
35
658752 Bandana
18
58
96
765332 Mini Crew Socks
729567 Striped Crew Socks
796689 Sun Runner Cap
6a2099140084ef3b7c3a29d59294714a
Retail Price
Category
2.99 Hats
4.99 Hats
6.45 Socks and Gloves
14.99 Socks and Gloves
18.98 Hats
Sport
Biking
Hiking
Stock Level
OK
Order
Fitness
Fitness
Fitness
Order
OK
OK
Apparel Inventory Summary
As of December 31
Seattle
Total Items in Stock
Average Price
Median Price
Lowest Price
Highest Price
Denver
Direct Expenses by Quarter
Summary
Sales Expense
Quarter 1
Quarter 2
Quarter 3
Quarter 4
Annual Total
$ 4,458,578 $ 4,678,712 $ 4,887,953 $ 5,325,215
Administrative Expense
Rent Expense
Interest Expense
Advertising Expense
Totals by Quarter
1,311,774
1,317,470
1,587,963
1,317,470
1,689,563
1,317,470
1,856,878
1,317,470
129,713
964,872
138,963
1,184,532
115,786
1,278,741
128,963
1,581,372
2020
2021
2022
Expenses 5-Year Forecast
Forecasted Increase
Year
Projected Expenses
3.5%
2019
2023
% of Total
Alexander, Rebekah
Alvarez, Eliza
Bradley, Christine
Caputo, Stephanie
DiAntonio, Avelina
Furfy, Jana
Hernandez, Anna
Grader – Instructions
Excel 2019 Project
Excel_Introductory_Capstone1_Year_End_Report
Project Description:
In this project, you will work with multiple worksheets and enter formulas and functions to calculate totals, averages, maximum
values, and minimum values. Additionally, you will create a summary sheet, format cells, insert charts, insert sparklines, and
create a table in a workbook.
Steps to Perform:
Points
Possible
Step
Instructions
1
Open the Excel file Student_Excel_Intro_Cap1_Year_End_Report.xlsx downloaded with this
project.
0
2
On the Net Sales worksheet, calculate totals in the ranges F4:F8 and B9:F9. Apply the Total
cell style to the range B9:F9.
5
3
Using absolute cell references as necessary, in cell G4, construct a formula to calculate the
percent that the Colorado Total is of Total Sales, and then apply Percent Style with zero
decimals. Fill the formula down through the range G5:G8.
7
4
In the range H4:H8, insert Line sparklines to represent the trend of each state across the four
quarters. Do not include the totals. Display Markers.
4
5
Select the range A3:E8, and then use the Recommended Charts command to suggest an
appropriate chart. Click the first Clustered Column chart that uses the state names as the
category axis. Align the upper left corner of the chart inside the upper left corner of cell A11,
and then size the chart so that its lower right corner is slightly inside cell H24. Apply chart
Style 7. As the chart title, type Quarterly Net Sales by State
10
6
To show the percent that each state contributes to the total sales (in column F), select the
nonadjacent ranges that represent the state names and state totals. Insert a 3-D Pie chart,
and then move the chart to a New sheet. Name the sheet Net Sales by State
6
7
Change the Chart Title to Annual Net Sales by State and then change the chart title Font
Size to 36. Remove the Legend from the chart, and then add Data Labels that display only the
Category Name and Percentage positioned in the Center. Change the data labels Font Size to
14 and apply Bold and Italic. Change the Font color to White, Background 1.
5
8
Select the entire pie and display the Format Data Series pane. From the 3-D Format gallery,
modify the 3-D options by changing the Top bevel and Bottom bevel to first bevel in the first
row. Set all of the Width and Height boxes to 512 and then change the Material to the third
Standard type—Plastic.
3
9
Insert a Custom Footer with the File name in the left section and then save your workbook.
1
10
On the Seattle Inventory worksheet, in cell B5, enter a function that will display the average
retail price. In cell B6, enter a function that will display the median retail price. In cell B7, enter
a function that will calculate the lowest retail price. In cell B8, enter a function that will calculate
the highest retail price. Format the range B5:B8 with Accounting Number Format.
5
11
In cell B10, insert a COUNTIF function that counts the number of Skiing items in the Sport
column.
5
12
On the Seattle Inventory worksheet, in cell G14, enter an IF function to determine the items to
be ordered. If the Quantity in Stock is less than 50, then the cell should display Order If not,
then the cell should display OK Copy the function down through cell G19.
3
Created On: 07/08/2021
1
GO19_XL_INTRO_GRADER_CAP1 – Year End Report 1.7
Grader – Instructions
Excel 2019 Project
Points
Possible
Step
Instructions
13
Format the range A13:G19 as a table with headers. Filter the table on the Sport column to
display only the Skiing types. Display a Total Row in the table, and then sum the Quantity in
Stock for Skiing items. Type the result in cell B11. Remove the total row from the table and
then clear the Sport filter.
5
14
Add Gradient Fill Blue Data Bars to the range A14:A19. Add conditional formatting to the
range G14:G19 so that the cells with text that contain the word Order are formatted with Bold
and Italic. Sort the table by Item # from Smallest to Largest.
3
15
Display the Inventory Summary sheet. In cell B4, enter a formula that references cell B4 in the
Seattle Inventory sheet so that the Seattle Total Items in Stock displays in cell B4. In cell B5,
enter a formula that references cell B5 in the Seattle Inventory sheet so that the Seattle
Average Price displays in cell B5. In cells B6, B7, and B8, enter similar formulas to reference
the Median Price, Lowest Price, and Highest price in the Seattle Inventory sheet.
5
16
In cell C4, enter a formula that references cell B4 in the Denver Inventory sheet so that the
Denver Total Items in Stock displays in cell C4. In cells C5, C6, C7, and C8, enter similar
formulas to reference the Average Price, Median Price, Lowest Price, and Highest price in the
Denver Inventory sheet. Apply Accounting Number format to the range B5:C8.
6
17
On the Annual Expenses sheet, construct formulas to calculate Totals by Quarter in the range
B10:E10 and the Annual Totals in the range F5:F10. Apply the Total cell style to the Totals by
Quarter (B10:F10), and then center the column headings (B4:G4) and apply the Heading 4 cell
style.
5
18
Using absolute cell references as necessary, in cell G5, construct a formula to calculate the %
of Total by dividing the Sales Expense Annual Total by the Annual Totals by Quarter. Fill the
formula down through the range G6:G9.
2
19
Using the data in the nonadjacent ranges B4:E4 and B10:E10, insert a Line with Markers
chart. Position the upper left corner of the chart slightly inside the upper left corner of cell A12.
Change the height of the chart to 1.75 inches and the width of the chart to 8 inches.
5
Note, Mac users, change the height of the chart to 1.5 inches.
20
Apply chart Style 7. Change the chart title to Direct Expenses Edit the Vertical (Value) Axis
so that the Minimum is 8000000 and the Major unit is 1000000
4
21
Use Format Painter to copy the formatting from cell A2 to A20. In cell B23, enter a formula that
references the value in cell F10.
2
22
Using absolute cell references as necessary, in cell C23, construct a formula to calculate the
projected expenses for 2020 after the Forecasted increase in cell B21 is applied. Fill the
formula through cell F23.
4
23
Display the Sales Reps sheet. By using Flash Fill and deleting columns as necessary, place
the last names in column A and the first names in column B. Widen both columns to 100
pixels, and then merge and center the title Sales Reps across the two columns. Apply the
Heading 1 cell style to the title.
4
Created On: 07/08/2021
2
GO19_XL_INTRO_GRADER_CAP1 – Year End Report 1.7
Grader – Instructions
Excel 2019 Project
Points
Possible
Step
Instructions
24
With any sheet except the chart sheet active, group the worksheets. Change the Orientation to
Landscape, center the worksheets Horizontally, and insert a footer in the left section with the
file name. (Mac users, click the Net Sales sheet, hold down SHIFT, and then click the Sales
Reps sheet. Then apply the Page Layout options.)
1
25
Save and close the file and submit for grading.
0
Total Points
Created On: 07/08/2021
3
100
GO19_XL_INTRO_GRADER_CAP1 – Year End Report 1.7