Ice Creamery Sales
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 use Goal
Seek, format cells, and insert charts, sparklines, and a table into the
workbook.
Instructions:
For the purpose of grading the project you are required
to perform the following tasks:
|
Step
|
Instructions
|
Points Possible
|
|
1
|
Start
Excel. Open the downloaded Excel file named GO_eV1_Grader_CAP.
|
0
|
|
2
|
On
the Quarterly Sales Summary worksheet, in cell F7, enter a function that
will add together the values in the range B7:E7. Copy the function down
through cell F13.
|
4
|
|
3
|
In
cell G7, enter a formula that will calculate the projected 2017 sales using
the projected sales growth rate in cell B4 and the total 2016 sales in cell
F7. Make the reference to cell B4 an absolute reference, and then copy the
formula down through cell G13.
|
4
|
|
4
|
In
cell B14, enter a function that will add together the Q1 sales for all
products. Copy the function across through cell G14.
|
4
|
|
5
|
Apply
the Total cell style to the range A14:G14.
|
2
|
|
6
|
In
cell H7, insert a line Sparkline using the data range B7:E7. Format the
sparkline by applying the style Sparkline Style Colorful #2. Copy the
sparklines down to H13.
|
5
|
|
7
|
Create
a Pie in 3-D chart using the nonadjacent ranges A7:A13 and F7:F13. Change
the chart style to Style 10.
|
3
|
|
8
|
Reposition
the chart so that the top left corner of the chart is centered in cell A15.
Change the height of the chart to 4.8 inches and the width of the chart to
6.5 inches.
|
3
|
|
9
|
Insert
a title above the chart using the text Ice
Creamery Sales and add data labels to the chart that display
percentages and leader lines in the inside end of each wedge. Move the
legend to the bottom of the chart.
|
6
|
|
10
|
Change
the angle of the first slice of the pie to 200, and then explode the green
slice of the pie by 17%. Change the solid fill color of the green slice to
White, Background 1, Darker 50%.
|
3
|
|
11
|
On
the Bonuses worksheet, in cell J5, enter an IF function that will calculate
whether or not an employee receives a bonus. If their total sales are
greater than $100,000, then the cell should show Yes. If not, then
the cell should show No. Copy the function down through cell J14.
|
4
|
|
12
|
Format
the range A4:J14 as a table with headers using the Table Style Medium 2.
|
3
|
|
13
|
Add
Gradient Fill Orange Data Bars to cells I5:I14. Add conditional formatting
to cells J5:J14 so that cells with a value of Yes are formatted as
Green Fill with Dark Green Text. Sort the table by Last Name from A to Z.
|
5
|
|
14
|
Add
a total row to the table. Count the number of records in the State column
and sum the Total Sales column. Remove the summary field in the Bonus
column.
|
2
|
|
15
|
In
cell C17, enter a function that will count the number of salespeople from
NY. In cell C18, enter a function that will count the number of salespeople
from NJ. In cell C19, enter a function that will count the number of
salespeople from CT.
|
4
|
|
16
|
In
cell C20, enter a function that will display the maximum total sales amount.
|
2
|
|
17
|
In
cell C21, enter a function that will display the minimum total sales amount.
|
2
|
|
18
|
2
|
|
|
19
|
On
the Sales Projections worksheet, change the value in cell B4 to 12%. Copy the range C7:C14 and paste the
values (not the formulas) in cells B18:B25. Change cell B4 to 20% Copy the range C7:C14 and paste the
values in cells C18:C25. Change cell B4 to
25% Copy the range C7:C14 and paste the values in cells D18:D25.
Change cell B4 to 30% Copy the
range C7:C14 and paste the values in cells E18:E25.
|
4
|
|
20
|
In
cell F18, enter a relative cell reference for the value in G7 on the
Quarterly Sales Summary worksheet. Copy this cell reference to cells
F19:F25. Copy the formatting from the range E18:E25 to cells F18:F25.
|
5
|
|
21
|
Insert
a new sheet to the right of the Sales Projections worksheet. Rename it Sales Analysis and change the tab color to
Purple, Accent 4.
|
3
|
|
22
|
Copy
cell A1 from the Sales Projections worksheet and paste it in cell A1 on the
Sales Analysis worksheet. In cell A2, enter the text Sales Goal in the cell. Merge and center
cells A2:F2. Apply the Heading 2 style to the merged cell.
|
4
|
|
23
|
Copy
the range A6:B14 from the Sales Projections worksheet and paste it into
cells A6:B14 of the Sales Analysis worksheet.
|
9
|
|
24
|
Use
format painter to copy the formatting from the range B6:B14 to cells C6:C14.
In cell C6, type % of Total Sales.
Center and wrap the text in cell C6. Autofit columns A and B, and then
change width of column C to 90 pixels.
|
9
|
|
25
|
In
cell C7, enter a formula that will calculate the percent of Ice Cream Bars -
Chocolate sales out of the total 2016 sales. Make the reference to cell B14
an absolute reference. Change the format of the cell to percentage with no
decimals, and then copy the formula down through cell C13.
|
5
|
|
26
|
In
cell A17, enter the text Ice cream cones
Sales Goal in the cell. Copy the range B13:C13 and paste it into
B17:C17. In cell C17, use Goal Seek to determine what ice cream cone sales
need to be (in cell B17) if they are to be 15% of total sales (in cell C17).
Save the results.
|
3
|
|
27
|
Ensure
that the worksheets are correctly named and placed in the following order in
the workbook: Quarterly Sales Summary; Bonuses; Sales Projections; Sales
Analysis. Save the workbook. Close the workbook and then exit Excel. Submit
the workbook as directed.
|
0
|
|
|
Total Points
|
100
|
No comments:
Post a Comment