Excel small project.

Anonymous
timer Asked: Dec 4th, 2017
account_balance_wallet $20

Question Description

Need some help with these questions for access assignment. I am stuck on this one as It seems to be quite different from the last project.

Unformatted Attachment Preview

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

Tutor Answer

criss53
School: Cornell University

Here is your file.

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

20171205070043pack_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

20171205070043pack_e03_grader_h1_inventory.xlsx

Novelty
Item
A13
A14
A15

Discontinue
N
N
N
Y
N

Reading
Item
Projection
A5
A6
A7...

flag Report DMCA
Review

Anonymous
Good stuff. Would use again.

Similar Questions
Related Tags

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors