Illustrated Excel 365 | Modules 1-4: SAM Capstone Project A Luxurious Getaways

GETTING STARTED

1. Save the file IL_EX365_CS1-4A_FirstLastName_1.xlsx as IL_EX365_CS1-4A_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_CS1-4A_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. Jamar Aspena directs the Santa Fe office of Luxurious Getaways, a travel company that specializes in coordinating vacation services. He has been tracking revenues and expenses along with customer data in an Excel workbook, including charts to help him visualize the data. He has asked you to help him complete the workbook and insert additional charts. Go to the Revenue & Expenses worksheet. In cell K1, insert a formula using the TODAY function to display today's date.

2. Fill the range D4:F4 with a series based on the value in cell C4 to provide the missing month names.

3. Format the text in cell A4 as follows to make it readable and more meaningful:

a. Merge and center the contents of the range A4:A18.

b. Rotate the text in the merged cell up to 90 degrees so it reads from bottom to top.

c. Middle-align the merged cell.

d. Resize column A to a width of 5.00.

4. Use AutoFit to resize column B to its best fit to display all the revenue and expense types.

5. Complete the calculations for the Revenue data as follows:

a. In cell E6, enter 37,855 as the Adults Only revenue for June.

b. In cell C9, use the SUM function to total the April Revenue values.

c. Copy the formula in cell C9 to the range D9:F9 and to cell H9 to complete the totals.

6. Format the nonadjacent ranges C13:F17 and H13:H17 using Comma style and no decimal places to match the formatting of the Revenue data.

7. Jamar wants to track the trends for each type of revenue and expense and for the profit analysis. Provide this information for him as follows:

a. Go to the Revenue & Expenses worksheet. In cell G5, insert a Line sparkline based on the data in the range C5:F5.

b. Include markers in the sparkline, and then change the marker color to Black, Text 1.

c. Copy cell G5, and then paste it in the range G6:G9, the range G13:G18, and cell G23.

8. Jamar wants to display the highest and lowest revenue amounts from April to July. Enter and format this information as follows:

a. In cell C25, enter a formula using the MIN function to display the lowest revenue in the range C5:F8.

b. In cell C26, enter a formula using the MAX function to display the highest revenue in the range C5:F8.

c. Apply Outside Borders to the range B25:C26 using Green, Accent 2 as the border color to show the information belongs together.

9. In the clustered column chart in the range J3:P18, Jamar wants to show the expenses by type, not by month. He also wants to make the contents of the chart clearer. Provide this information for him as follows:

a. Switch the rows and columns to display expenses by type.

b. Move the legend to the right side of the chart.

c. Add Monthly Amount as the primary vertical axis title.

d. Add Expenses by Type as the chart title.

e. Change the fill color of the July data series to Green, Accent 3.

f. Add a chart border using the Black, Text 1, Lighter 50% shape outline color.

10. Jamar wants to include a chart showing the monthly profits for the Santa Fe office to determine which months have been more favorable. Create a new chart as follows:

a. Create a pie chart based on the range C22:F23.

b. Resize and reposition the chart so that its upper-left corner is within cell J20 and its lower-right corner is within cell P32.

c. Enter April to July Profit as the chart title.

d. Apply Style 3 to the chart to clearly display percentages on each part of the pie chart.

11. Jamar also wants to include a chart showing the revenue earned from family resorts, all-inclusive resorts, adults only resorts, and cruises. Create and format a chart for him as follows:

a. Create a Stacked Column chart based on the range B4:F8.

b. Move the chart to a new sheet named Revenue Chart.

c. Change the chart style to Style 7 to match the style of the clustered column chart on the Revenue & Expenses worksheet.

d. Change the font size of all the chart text to 12 point to make it easier to read.

e. Remove the chart title since the sheet tab indicates the purpose of the chart.

12. Clarify the data in the chart as follows:

a. Format the values in the vertical axis using the Accounting number format with no decimal places to clarify the values are dollar amounts.

b. Add a data table with legend keys to the chart to display the revenue values.

c. Remove the legend, which is now redundant.

13. Go to the Business Customer Analysis worksheet, which compiles data about the New Mexico customer accounts that Jamar handles. Calculate the number of years a customer has been with Luxurious Getaways as follows:

a. In cell E5, enter a formula without using a function that subtracts the start date for the customer from the current date and divides the result by 365.25, the number of days in a year, accounting for leap year.

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

c. Display the value in cell E5 with one decimal place.

d. Fill the range E6:E19 with the formula in cell E5.

14. Luxurious Getaways offers a discount to customers who have been with the company for at least five years. Determine whether each customer qualifies for a discount as follows:

a. In cell H5, enter a formula using the IF function that tests whether the number of years is greater than or equal to 5. If it is, display "Y" in cell H5. If it is not, display "N" in cell H5.

b. Fill the range H6:H19 with the formula in cell H5.

15. Jamar plans to offer new, more favorable contracts to customers who are now receiving a discount and book their flights through Luxurious Getaways. Determine whether each customer should receive a new contract as follows:

a. In cell I5, enter a formula using the AND function that tests whether the Flights value is equal to "Y" and whether the Discount value is equal to "Y".

b. Fill the range I6:I19 with the formula in cell I5.

16. Jamar also plans to offer a free night to customers who live in southern New Mexico and are currently booking an adults only resort vacation. Determine whether each customer should receive a free night as follows:

a. In cell J5, enter a formula using the OR function that tests whether the location is equal to "S" or whether the plan type is equal to "Adults Only".

b. Fill the range J6:J19 with the formula in cell J5.

17. Jamar wants to display the total number of business customers. In cell M4, enter a formula using the COUNTA function to count the customer IDs.

18. Jamar wants to determine the average number of years customers have been with Luxurious Getaways. In cell M5, enter a formula using the AVERAGE function to average the number of years.

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: Revenue Chart

Final Figure 2: Revenue & Expenses

Final Figure 3: Business Customer Analysis