In the following project, you will perform preliminary work on the sales worksheet. You will total quarterly earnings, create functions to determine sales regions, and bonus eligibility.
accounting
Description
Formulas and Functions
Project Description:
In the following project, you will
perform preliminary work on the sales worksheet. You will total quarterly
earnings, create functions to determine sales regions, and bonus eligibility.
Your last steps will be to calculate basic summary information and finalize
the worksheet.
Instructions:
For the purpose of grading the project you are required
to perform the following tasks:
|
Step |
Instructions |
Points Possible |
|
1 |
Download and open the file named grader 2 from LMS and then save the file as Grader 2. |
0 |
|
2 |
Create a named range for cell C4
named Sales_Goal. |
7 |
|
3 |
Insert a function in cell B25 to
calculate the total sales for Q1 in column B. |
7 |
|
4 |
Copy the total function into the
range C25:E25 to calculate the totals of Q2 through Q4. |
7 |
|
5 |
Insert functions in column F to
calculate the sales totals of each individual sales rep. |
7 |
|
6 |
Insert a function in the cell H11
that looks up the sales rep region based on the table in cells E3:F6. |
7 |
|
7 |
Copy the function down to populate
the remaining portion of column H. Be sure to use absolute or mixed
referencing when necessary. |
7 |
|
8 |
Enter a function in cell I11 to
calculate the sales reps eligibility for bonus. If the reps total sales are
greater than or equal to the sales_goal then the function should return the
value Bonus,
if not the function should return NA. |
7 |
|
9 |
Copy the function down to populate
the remaining portion of column I. Be sure to use absolute or mixed
referencing when necessary. |
7 |
|
10 |
Insert a function in cell B29 to
calculate Median of all sales in column F. |
7 |
|
11 |
Insert a function in cell B30 to
calculate average sales dollar amounts. |
6 |
|
12 |
Insert a function in cell B31 to
calculate the total number of sales reps. |
6 |
|
13 |
Format the values in the range
B11:E11 with Accounting Number Format. |
6 |
|
14 |
Format the values in the range
B12:E24 with Comma Style. |
6 |
|
15 |
Insert a function in cell C3 to
display the current date. |
7 |
|
16 |
Format the totals in cells F11:F24
with Accounting Number Format. |
4 |
|
17 |
Insert your name in Cell A2, Apply
Green, Accent 6, Darker 40% file color and Black. |
2 |
|
18 |
Save the file and close Excel.
Submit the file as directed. |
0 |
|
|
Total Points |
100 |
