Massage
Item
A9
A10
A11
A12
Item
A1
A2
A3
A4
A5
A6
A7
A8
A9
A10
A11
A12
A13
A14
A15
A16
A17
A18
A19
A20
Description
Healthy Trail Mix
Resort Polo
Healthy Energy Bar
Spirits Lifted Through Yoga
Golf Tees
20171204190157pack_e03_grader_h1_inventory.xlsx
Golf
Item
A16
A17
A18
A19
Projection
4
5
6
6
Max_On_Hand
45
35
25
15
50
WholeSell Price
$
15,99
$
29,48
$
3,90
$
6,90
$
1,30
Sale Price
$ 21,99
$ 45,34
$
9,95
$ 14,95
$
2,59
Projection
5
5
5
5
Category
Food
Clothing
Food
Reading
Golf
Food
Item
A1
A3
A20
Projection
4
4
4
Delivery Time Current Inventory ReOrder_Point RushReOrderPoint
10
23
20
10
14
15
17
10
4
5
15
10
7
5
15
10
11
27
20
10
20171204190157pack_e03_grader_h1_inventory.xlsx
Novelty
Item
A13
A14
A15
Discontinue
N
N
N
Y
N
Reading
Item
Projection
A5
A6
A7
A8
Projection
5
6
7
ReorderStatus
Vendor
Vendor 1
Vendor 2
Vendor 1
Vendor 4
Vendor 5
20171204190157pack_e03_grader_h1_inventory.xlsx
Clothing
Item
A2
A4
Projection
Massage
Category
Category Item
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Projection
Actual
Categories
Massage
Golf
Food
Novelty
Reading
Clothing
Mon
Tues
Wed
Thur
Fri
Item
Beginning
inventory
Total out
Total in
Ending
Actual
A1
A2
A3
A4
A5
A6
A7
A8
A9
A10
A11
A12
A13
A14
35
33
48
10
52
10
22
15
20
30
22
10
23
25
20171204190157pack_e03_grader_h1_inventory.xlsx
A15
A16
A17
A18
A19
A20
12
14
16
33
27
15
20171204190157pack_e03_grader_h1_inventory.xlsx
Sat
Sun
Max Stock
Inventory
Discrepancy
On Target
On Target
On Target
On Target
On Target
On Target
On Target
On Target
On Target
On Target
On Target
On Target
On Target
On Target
25
20
15
10
30
30
30
30
30
30
30
30
30
30
Turnover
0,00%
0,00%
0,00%
0,00%
0,00%
0,00%
0,00%
0,00%
0,00%
0,00%
0,00%
0,00%
0,00%
0,00%
Description
Healthy Trail Mix
Resort Polo
Healthy Energy Bar
Spirits Lifted Through Yoga
Golf Tees
20171204190157pack_e03_grader_h1_inventory.xlsx
30
30
30
30
30
30
On Target
On Target
On Target
On Target
On Target
On Target
0,00%
0,00%
0,00%
0,00%
0,00%
0,00%
20171204190157pack_e03_grader_h1_inventory.xlsx
Transaction
Trans_Item
Trans_Qty
Trans_Category
WeekDay
Trans_Sold
Trans_Category
Food
Food
Golf
Food
Massage
Food
Food
Clothing
Clothing
Golf
Food
Massage
Food
Clothing
Clothing
Food
Food
Clothing
Golf
Novelty
Reading
Golf
Golf
Clothing
Novelty
Golf
Novelty
Novelty
Reading
Clothing
WeekDay
Mon
Mon
Wed
Thur
Fri
Fri
Fri
Fri
Sat
Sun
Thur
Mon
Wed
Thur
Fri
Fri
Sat
Sat
Mon
Mon
Mon
Tues
Wed
Wed
Thur
Thur
Thur
Fri
Fri
Sat
Trans_Sold
Trans_Sold
Sum
Average
Transaction
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
Trans_Item
A1
A3
A5
A3
A6
A3
A1
A2
A7
A19
A1
A10
A1
A8
A2
A1
A3
A15
A13
A14
A4
A9
A5
A15
A17
A9
A14
A17
A18
A7
Trans_Qty
-14
5
-7
-14
-15
-16
-7
-10
-4
-2
15
-18
-6
-6
-13
-2
-17
-8
5
-11
-4
0
-18
-6
-4
-18
-13
-15
-14
-9
20171204190157pack_e03_grader_h1_inventory.xlsx
Trans_Delivered
Trans_Delivered
20171204190157pack_e03_grader_h1_inventory.xlsx
Office 2016 – myitlab:grader – Instructions
Excel Project
YO16_XL_BU03_GRADER_PS1_HW - Inventory 1.6
Project Description:
The gift shop staff wants to track inventory so it can reduce out-of-stock inventory items and reorder inventory
more efficiently. The staff needs a template designed that will hold all the product information and then analyze
daily transactions at the end of every month. You have been asked to help the gift shop employees develop a
workbook that will help them manage the store’s inventory. Because the staff currently does not collect this data,
the gift shop manager has given you a template with a small amount of fictitious data so you can create the
needed formulas and worksheets. The worksheet also contains named ranges that you will use to create formulas
in the template.
Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step
1
Points
Possible
Instructions
Open the downloaded file e03_grader_h1_Inventory.xlsx. Save the file as
e03_grader_h1_Inventory_LastFirst using your last and first name.
0.000
On the Inventory worksheet, in cell L19, enter nested IF and AND functions to determine the
reorder status.
For the logical test, determine if the current inventory in cell H19 is blank and the reorder
point in I19 is blank. If the value is true, return a blank.
2
3
But if the value is false, nest a second nested IF and AND function in which the logical test
determines if the Discontinue in K19 is equal to "N" and the Current Inventory is less than or
equal to the rush reorder point in J19. If the value is true, "Rush" should be returned. If the
value is false, then nest a third IF and AND function in which the logical test determines if
Discontinue (K19) is equal to "N" and Current Inventory (H19) is less than the Reorder Point
(I19). If the value is true, return "Reorder". If the value is false, return "Ok".
Copy the formula down through cell L38.
On the DailyTransactions worksheet, in cell F11, the data in the Trans_Sold column is the
number of items sold.
(Negative Trans_Qty numbers are the quantity that was sold or outgoing. Positive Trans_Qty
numbers are the total number received from the supplier or incoming).
6.000
6.000
Enter an IF function to determine if the Trans_Qty in C11 is less than zero. If the Trans_Qty is
less than zero, then return the absolute value of the Trans_Qty, or else the cell is left blank.
Copy the formula through cell F40.
4
On the DailyTransactions worksheet in cell G11, enter an IF function to determine if there
were transactions delivered. If the value in the Trans_Qty is greater than zero, then the result
is the value, or else the cell is left blank. Copy the formula through cell G40.
6.000
5
On the DailyTransactions worksheet, in cell D2, type Food and in cell E2, type Fri
2.000
On the DailyTransactions worksheet, select the range A10:G40. Name the range
InventoryTable
6
In cell C6, enter a DSUM function to sum the date in the InventoryTable of the field name in
C5 and the criteria range established in D1:E2.
Updated: 10/02/2017
1
6.000
Current_Instruction.docx
Office 2016 – myitlab:grader – Instructions
Step
7
Excel Project
Points
Possible
Instructions
In cell C7, enter a DAVERAGE function to determine the average of the field in cell C5 from
the table range data in the InventoryTable and the criteria range established in D1:E2.
6.000
Format cell C7 with no decimal places.
On the InventoryAudit worksheet, the cells in range A3:D17 need to be filled in based on the
category that is listed in cell B1, "Massage". The table will then contain the information about
each product within the listed category. Using named ranges already created in the Inventory
worksheet, complete the following:
8
9
In cell B3, create a formula to retrieve the item within each category in cell B1. Enter an
INDIRECT function to return the item numbers by category. The function will reference cell
$B$1 (absolute) and index the value of the intersection of row 3 (A3) and column 1.
Nest the function in an IFERROR function to return a blank cell if no items exist within the
category.
On the InventoryAudit worksheet in cell C3, create a formula to retrieve the projected sales
for each item within the category in cell B1. Enter an INDIRECT function to return the item
numbers by category. The function will reference cell $B$1 (absolute) and index the value of
the intersection of row 3 (A3) and column 2. Nest the function in an IFERROR function to
return a blank cell if no items exist within the category.
6.000
6.000
On the InventoryAudit worksheet in cell D3, create a formula to determine the total quantity
sold within the category in cell B1. Enter a SUMIF function nested in an IF function to
determine if B3 has a value. If B3 has a value, then you will sum the Trans_Sold if the
Trans_Item meets the criteria in B3. If B3 does not have a value, then the cell is left blank.
10
11
12
13
14
Select range B3:D3, and then double-click the Fill handle to copy the three formulas down
through cell range B4:D17.
Rows 7 through 17 will be blank because the Indirect function is referring to the named range
for the value in cell B1, "Massage". The Massage named range only contains the four items.
To test your formula, type Golf in cell B1, and then view the results. Type Massage in cell
B1.
In cell C21, using a SUMIFS function, calculate the total quantity sold (Trans_Sold) per
category (Trans_Category) per day (Weekday) in range B20:I26 based on two sets of criteria,
C20 and B21. Use an appropriate mixed reference on the criteria cell references to be able to
copy the formula through cell I26.
Copy the formula through cell range D21:I26.
Calculate the total quantity sold per category in range D32:D51 based on the item in column
B. Once this has been calculated, the total quantity delivered and ending inventory can be
calculated.
In cell D32, create a formula to sum the range Trans_Sold from the DailyTransactions
worksheet if the criteria in B32 is found in the range Trans_Item from the DailyTransactions
worksheet.
On the InventoryAudit worksheet, in cell E32, create a formula to sum the range
Trans_Delivered if the criteria in B32 is found in the range Trans_Item.
In cell F32, create a formula that subtracts the Total out (D32) from the Beginning inventory
(C32) and then adds the Total in (E32).
In cell G32, create a VLOOKUP formula for the value in cell B32 from the range A19:M38 on
the Inventory worksheet in the seventh column with an exact match.
6.000
6.000
6.000
12.000
6.000
Select range D32:G32, and then copy the formulas through cell range D33:G51.
Updated: 10/02/2017
2
Current_Instruction.docx
Office 2016 – myitlab:grader – Instructions
Excel Project
Step
Instructions
Points
Possible
15
There is an error in a table array in the formulas in K33:K51. In cell K32, review the formula,
and make any necessary corrections to fix the formula. Copy the corrected formula in cell K32
through cell range K33:K51.
4.000
16
17
18
Make a copy of the DailyTransactions worksheet, and place it to the right of the
DailyTransactions worksheet. Name the new sheet DailyTransactionsFilter.
In cell D2, enter Clothing.
Format A10:G40 as a Table with headers.
Create an advanced filter. Verify the criteria range is A1:G2.
Click cell A11 of the DailyTransactions worksheet. Create a PivotTable, and place it on a new
worksheet named PivotTableAnalysis. Add the Trans_Category, and Trans_Item fields as
row fields (in that order). Add Transaction as the value field.
Sort the transaction categories in Descending order.
Count the number of transaction items.
Change the text in A3 to Items Sold, and the text in cell B3 to # of Transactions.
Apply Pivot Style Dark 2 to the PivotTable. Note, depending on the version of Office you are
using, the style may be Dark Blue, Pivot Style Dark 2.
Filter the PivotTable for Golf, Food, and Clothing.
Save the workbook, exit Excel, and then submit your file as directed by your instructor.
3
10.000
0.000
Total Points
Updated: 10/02/2017
6.000
100.000
Current_Instruction.docx
...

Purchase answer to see full
attachment