Illustrated Excel 365 | Module 3: SAM Project A Décor Online

GETTING STARTED

1. Save the file IL_EX365_3A_FirstLastName_1.xlsx as IL_EX365_3A_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_3A_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. Clara Fontes is a project manager for Décor Online, an e-commerce retailer of home décor with headquarters in Schaumburg, Illinois. She is tracking the wall art projects she commissioned and the revenue generated in the first three months of the year. She asks you to help her complete the analysis. Go to the Wall Art worksheet. In cell B4, insert a formula using the DATE function to display the current date as 6/5/2029 (m/d/yyyy) so that Clara can use the date in calculations. [Mac Hint: The date will display using the format m/d/yy.]

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

a. In cell F7, insert a formula without using a function that subtracts the start date (cell B7) from the due date (cell E7).

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

3. In column G, Clara 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 G7, insert a formula without using a function that subtracts the start date (cell B7) from the current date (cell B4).

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

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

4. In column H, Clara 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 H7, insert a formula using the IF function that tests whether the number of days on the project (cell G7) is greater than the project length (cell F7).

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

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 B4 in the formula. Use the Fill Handle to fill the range H8:H25 with the formula in cell H7 to complete the Days Overdue column.

5. Clara schedules an online 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. Provide a quick way to identify those vendors as follows:

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

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

6. Clara 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 J7, insert a formula using an IF function to test whether the previous sales amount (cell D7) is less than 1800 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 D7) is less than 2200 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 J8:J25 with the formula in cell J7 to complete the Rating column.

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

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

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

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

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

10. Go to the Sales Analysis worksheet. In cell B4, 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 B7:D16) and display the results in the range E7:E16. [Mac Hint: Use the Auto Sum tool to display the results in the range E7:E16.]

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

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

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

b. Use the Fill Handle to fill the range G8:G16 with the formula in cell G7.

14. Clara also wants to display the average quarterly revenue rounded up to the next whole number. In cell C18, enter a formula using the ROUNDUP and AVERAGE functions that averages the current quarterly revenue amounts (the range E7:E16) 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: Wall Art Worksheet

Final Figure 2: Sales Analysis Worksheet