Illustrated Excel 365 | Module 3: SAM Project B Retro Mod

GETTING STARTED

1. Save the file IL_EX365_3B_FirstLastName_1.xlsx as IL_EX365_3B_FirstLastName_2.xlsx

a. Edit the file name by changing “1” to “2”.

b. If you do not see the .xlsx file extension, do not type it. The file extension will be added for you automatically.

2. With the file IL_EX365_3B_FirstLastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet.

a. If cell B6 does not display your name, delete the file and download a new copy.

PROJECT STEPS

1. Audrey O'Niel is a project manager for Retro Mod, a retailer of custom retro apparel with headquarters in Pittsburgh, PA. She is tracking the wall art projects she commissioned and the revenue generated in the last three months of the year. She asks you to help her complete the analysis. Go to the Accessories worksheet. In cell B3, insert a formula using the DATE function to display the current date as 5/8/2029 (m/d/yyyy) so that Audrey can use the date in calculations. [Mac Hint: The date will display using the format m/d/yy.]

2. In column F, Audrey wants you to display the number of days in each project. Calculate the project length as follows:

a. In cell F9, insert a formula without using a function that subtracts the start date (cell B9) from the due date (cell E9).

b. Use the Fill Handle to fill the range F10:F25 with the formula in cell F9 to complete the Length column.

3. In column G, Audrey wants you to display how long the vendor has been working on the project, which is the number of days between the start date and the current date. Provide this information as follows:

a. In cell G9, insert a formula without using a function that subtracts the start date (cell B9) from the current date (cell B3).

b. Use an absolute reference to cell B3 in the formula.

c. Use the Fill Handle to fill the range G10:G25 with the formula in cell G9 to complete the Days on Project column.

4. In column H, Audrey wants you to calculate the number of days the project is overdue. If the number of days the vendor has been working on the project is greater than the project length, it is overdue. Calculate the days overdue as follows:

a. In cell H9, insert a formula using the IF function that tests whether the number of days on the project (cell G9) is greater than the project length (cell F9).

b. If the number of days on the project is greater than the project length, subtract the due date (cell E9) from the current date (cell B3).

c. If the number of days on the project is less than or equal to the project date, display 0 to show that the project is not overdue.

d. Use an absolute reference to cell B3 in the formula. Use the Fill Handle to fill the range H10:H25 with the formula in cell H9 to complete the Days Overdue column.

5. Audrey schedules a status meeting with the vendor if they have been working on a project for more than 30 days and their previous sales are more than $2000. She wants you to provide a quick way to identify those vendors as follows:

a. In cell I9, insert a formula using the AND function that returns TRUE if the number of days on the project (cell G9) is greater than 30 and if the previous sales amount (cell D9) is greater than 2000.

b. Use the Fill Handle to fill the range I10:I25 with the formula in cell I9 to complete the Meeting? column.

6. Audrey assigns each vendor a rating of A, B, or C depending on their previous sales amount. Display this information using a nested IF function as follows:

a. In cell J9, insert a formula using an IF function to test whether the previous sales amount (cell D9) is less than 1500 and displays a rating of "C" if the condition is true.

b. The next condition uses another IF function to test whether the previous sales amount (cell D9) is less than 2100 and displays a rating of "B" if the condition is true.

c. Display a rating of "A" if the conditions are false.

d. Use the Fill Handle to fill the range J10:J25 with the formula in cell J9 to complete the Rating column.

7. Audrey recommends rehiring vendors if they have a rating of "A" or "B". Provide this information as follows:

a. In cell K9, insert a formula using the OR function that returns TRUE if the rating (cell J9) equals "A" or the rating (cell J9) equals "B".

b. Use the Fill Handle to fill the range K10:K25 with the formula in cell K9 to complete the Rehire? column.

8. Audrey likes to keep track of the total number of vendors she is working with. In cell B5, enter a formula using the COUNTA function that counts the vendors (the range C9:C25).

9. Audrey also likes to keep track of the approximate amount of sales the vendors have generated. In cell B6, enter a formula using the ROUND and SUM functions that totals the previous sales amounts (the range D9:D25) and rounds the result to 0 decimal places.

10. Go to the Sales Analysis worksheet. In cell B3, enter a formula using the TODAY function to insert the current date.

11. Use the Quick Analysis tool to sum the monthly revenue for each product category (the range B8:D17) and display the results in the range E8:E17. [Mac Hint: Use the Auto Sum tool to display the results in the range E8:E17.]

12. Remove the automatic bold formatting from the range E8:E17, if necessary, because the totals do not need to be emphasized.

13. Audrey wants you to calculate the percentage increase from last quarter to the current quarter. Complete the formula as follows:

a. In cell G8, insert a formula without using a function that first subtracts the last quarter revenue amount (cell F8) from the current quarter revenue amount (cell E8), and then divides the result by the last quarter revenue amount (cell F8).

b. Use the Fill Handle to fill the range G9:G17 with the formula in cell G8.

14. Audrey also wants to display the average quarterly revenue rounded up to the next whole number. In cell C5, enter a formula using the ROUNDUP and AVERAGE functions that averages the current quarterly revenue amounts (the range E8:E17) and rounds the result to 0 decimal places.

Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the website to submit your completed project.

Final Figure 1: Accessories Worksheet

Final Figure 2: Sales Analysis Worksheet