Homework answers / question archive / Illustrated Excel 2019 Module 3:End of Module Project 1 Madison Holboke 1

Illustrated Excel 2019 Module 3:End of Module Project 1 Madison Holboke 1

MS Excel

Share With

---

Illustrated Excel 2019 Module 3:End of Module Project 1

Madison Holboke

1. Jayden Keller manages the Lawn and Garden Departments at Hargrove Home Improvement stores throughout the Pacific Northwest. He created an Excel workbook to track inventory and details about the stock at one of the stores. He has asked you to complete the calculations in the workbook.

Go to the Inventory worksheet. In cell B3, insert a formula using the DATE function to display the date 5/5/2021 . [Mac Hint: The date will displayed using the format m/d/yy.]

2. In cell E5, insert a formula without using a function that multiplies the amount on hand (cell D5 ) by the wholesale price (cell B5 ). Fill the range E6:E15 with the formula in cell E5, filling the range without formatting.

3. In cell F5, insert a formula without using a function that multiplies the wholesale price (cell B5 ) by the markup amount (cell L3 ). Use an absolute reference to cell L3 in the formula. Copy the formula in cell F5 and paste only the formula and number formatting in the range F6:F15.

4. In column H, Jayden wants to display how long the plants have been in inventory, which is the number of days between the current date and the in stock date.

In cell H5, insert a formula without using a function that subtracts the in stock date (cell G5 ) from the current date (cell B3 ). Use an absolute reference to cell B3 in the formula. Fill the range H6:H15 with the formula in cell H5, filling the range without formatting.

5.  In column I, Jayden wants to display a reminder to reorder plants if the store has 10 or fewer plants on hand. 

In cell I5, insert a formula using the IF function that tests whether the number of plants on hand (cell D5) is less than or equal to 10. If the store has 10 or fewer plants on hand, display "Y" in cell I5. Otherwise, display "N" in cell I5. Fill the range I6:I15 with the formula in cell I5, filling the range without formatting.

6. Jayden puts plants on sale if they have been in inventory for 40 days or more and the store is not reordering more plants of that type. In column J, Jayden wants to display whether to put a type of plant on sale based on these criteria.

In cell J5, insert a formula using the AND function that returns TRUE if the reordering indicator (cell I5) is equal to "N" and if the days in inventory (cell H5) is greater than 40. Use the Fill Handle to fill the range J6:J15 with the formula in cell J5, filling the range without formatting.

7. Jayden rates plants as A, B, or C depending on their quality, with "A" being the highest rating. If a plant type has an "A" rating or is being reordered to fill inventory, Jayden offers it at a discount to the store's garden club. In column L, Jayden wants to display whether to offer a type of plant to the garden club based on these criteria. 

In cell L5, insert a formula using the OR function that returns TRUE if the quality rating (cell K5) equals "A" or the reorder indicator (cell I5) equals "Y". Fill the range L6:L15 with the formula in cell L5, filling the range without formatting.

8.  Jayden likes to track details about all the plants in inventory. 

In cell C18, enter a formula using the COUNTA function that counts the number of plant types (range A5:A15).

9. In cell C19, enter a formula using the AVERAGE function that averages the on hand amounts (range D5:D15).

10. In cell C20, enter a formula using the ROUNDUP and AVERAGE functions that averages the retail prices (range F5:F15) and rounds the result up to 1 decimal place.