Shelly Cashman Excel 365 | Modules 4-7: SAM Capstone Project A Rhodes Technical College

GETTING STARTED

1. Save the file SC_EX365_CS4-7A_FirstLastName_1.xlsx as SC_EX365_CS4-7A_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. To complete this Project, you will also need the following files:

Support_EX365_CS4-7A_Enrollment.xlsx

Support_EX365_CS4-7A_Potential.html

Support_EX365_CS4-7A_Departments.txt

3. With the file SC_EX365_CS4-7A_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. Antoine Fontaine is a development officer for Rhodes Technical College, which has three campuses in New Hampshire. He is tracking enrollment for the year and asks for your help in projecting future enrollment and visualizing the data. The Manchester, Nashua, and Concord worksheets have the same structure and contain similar data. Group the Manchester, Nashua, and Concord worksheets to make changes to the three worksheets at the same time. The first change is to display today's date. In cell C4 of the Manchester worksheet, enter a formula using the TODAY function to display today's date.

2. Use the text in cell I6 to fill the range J6:L6 with the names of the remaining quarters in the year.

3. The Manchester campus has a goal of increasing quarterly enrollment to 2,200 by the fourth quarter of next year. For Quarter 1, Antoine estimates an enrollment of 1,978, which is the average enrollment per quarter in the current year. Project the enrollment in Quarters 2 and 3 by filling the series for the first projection (range I8:L8) with a linear trend.

4. Antoine estimates that enrollment will increase by at least 3 percent per quarter next year. He wants to calculate these enrollment goals in a second projection. Project next year's enrollment in the second projection (range I10:L10) based on a growth series using 1.03 as the step value.

5. Antoine wants you to consolidate the enrollment data in the Manchester, Nashua, and Concord worksheets on the All Campuses worksheet. Ungroup the worksheets, go to the All Campuses worksheet, and then consolidate the data as follows:

a. In cell C7, enter a formula using the SUM function and a 3D reference to total the Quarter 1 enrollment in the Accounting department (cell C7) for the Manchester, Nashua, and Concord campuses.

b. Copy the formula in cell C7 to calculate the enrollment for the other departments for all four quarters (range C8:C12 and D7:F12).

6. In cell G15, Antoine wants you to display the total enrollment from the previous year. This data is stored in another workbook. Insert the total as follows:

a. Open the file Support_EX365_CS4-7A_Enrollment.xlsx.

b. In cell G15 of Antoine's workbook, insert a formula using an external reference to the total revenue (cell G13) in the All Campuses worksheet in the Support_EX365_CS4-7A_Enrollment.xlsx workbook.

7. Antoine wants to visualize how the enrollment for each department compares to the total enrollment for the four quarters. Create a chart as follows to illustrate this information:

a. Create a 3-D Pie chart that shows how the revenue from each department (range B7:B12) contributed to the total enrollment (range G7:G12).

b. Move and resize the chart so that the upper-left corner is in cell B17 and the lower-right corner is in cell G31.

8. Format the 3-D Pie chart as follows to make it easier to interpret:

a. Add data labels to the chart on the Outside End of each slice.

b. Display only the Category Name and Percentage amounts in the data labels.

c. Change the number format of the data labels to Percentage with 1 decimal place.

d. Explode the largest slice (Information Systems) by 10 percent.

e. Change the chart colors to Monochromatic Palette 1 to coordinate with the colors in the workbook.

f. Apply Style 5 to make the chart more striking.

g. If displayed, remove the chart title, which is not necessary for this chart.

9. Antoine also wants to visualize the enrollment for each department per quarter. Create a chart as follows to illustrate this information:

a. Use the Quick Analysis tool to create a Stacked Bar chart that compares the enrollment for each department for Quarters 1–4 (range B6:F12). [Mac Hint: Select Stacked Bar chart from Recommended Charts on the Insert tab.]

b. Switch the rows and columns to compare the four quarters of data rather than the six departments.

c. Move and resize the chart so that the upper-left corner is in cell I13 and the lower-right corner is in cell L31.

10. Antoine asks you to change the chart so it compares enrollment from departments with four-year programs only. Modify the Stacked Bar chart as follows to meet his request and make the chart more meaningful:

a. Remove the Physical Therapy data series from the chart.

b. Add a Data Table with legend keys to the chart.

c. Use Four-Year Programs as the chart title.

d. Remove the legend, which repeats information in the data table.

e. Change the chart colors to Monochromatic Palette 1 to match the colors of the pie chart.

11. Antoine has a text file that describes the departments at the college. Import the text file as follows:

a. Get data from the Text/CSV file Support_EX365_CS4-7A_Departments.txt.

b. Transform the data before loading it to use the first row as headers.

c. In the Power Query Editor window, choose to close and load to a location in the worksheet. [Mac Hint: Skip substeps a. and b. and use Text Import Wizard to import data as tab delimited text.]

d. View the imported data as a table and insert the data in cell I6 of the existing worksheet.

e. Apply Red, Table Style Medium 16 to the imported table to coordinate with the rest of the worksheet contents. [Mac Hint: Format as a Table using the Red, Table Style Medium 16 with the "My table has headers" option and update the table name from "Table2" to "Support_EX365_CS4_7A_Departments]

f. Clear the formats from the range I6:J6 to display the column headers.

12. Antoine asks you to provide a way to sort and filter the enrollment data. Format the range B6:G13 as a table with headers using the Red, Table Style Medium 16 table style.

13. Go to the Programs worksheet, which includes the Programs table listing funding requested for academic programs and indicates whether the funding has been approved for the year. Antoine suspects a program has been listed twice in Programs table. Remove the duplicate record from the table.

14. Antoine wants to list the approved programs in a separate part of the worksheet. Use an advanced filter as follows to list these projects in a new range:

a. In cell G31, type Yes as the value to filter on in the criteria range.

b. Create an advanced filter using the Programs table (range B5:G27) as the List range.

c. Use the range B30:G31 as the Criteria range.

d. Copy the results to another location, starting in the range B33:G33.

e. Insert a table using the range B33:G43 as the data and specifying that the table has a header row.

f. Filter the new table to display only data for the Bioscience and Information Systems departments.

15. Antoine asks you to identify the projects with funding requests of $15,000 or more, those with funding requests of $10,000 or more, and those with funding requests less than $10,000.

a. In the Funding column (range F6:F27), create a new Icon Set Conditional Formatting rule using the 3 Traffic Lights (Unrimmed) indicators.

b. Display the green circled symbol in cells with a Number type value greater than or equal to 15000.

c. Display the yellow circled symbol in cells with a Number type value greater than or equal to 10000.

d. Display the red circled symbol in cells with a Number type value less than 10000.

16. The range I5:J17 lists program information, including the ID code that Rhodes Technical College uses to identify the programs. Antoine asks you to find a simple way to look up a program name based on its ID. Create a formula that provides this information as follows:

a. In cell J7, begin to enter a formula using the XLOOKUP function.

b. Use the Program ID (cell J6) as the lookup value.

c. Use the range B6:B27 as the lookup_array.

d. Use the range C6:C27 as the return_array.

17. Antoine also wants you to list the start date of the funding for the program identified in cell J6. In cell J8, enter a formula using the XLOOKUP function that looks up the value in cell J6 in the range B6:B27, and then returns the corresponding start date as an exact match.

18. Antoine also wants you to calculate the number of programs with a funding request of more than $15,000 and determine the average funding amount for Physical Therapy programs. Create formulas that provide this information as follows:

a. In cell J12, create a formula using the DCOUNT function to count the number of programs with funding amounts more than $15,000, using the Programs table (Programs[#All]) as the database, "Funding" as the field, and the range I10:I11 as the criteria.

b. In cell J17, create a formula using the DAVERAGE function to average the funding amounts for programs in the Physical Therapy department listed in the Programs table, using the range I15:I16 as the criteria.

19. Finally, Antoine wants you to summarize the number of programs by department and calculate their total and average funding amounts. Calculate this information for Antoine as follows:

a. In cell J20, enter a formula using the COUNTIF function that counts the number of Accounting programs, using Programs[Department] as the range and cell I20 as the criteria.

b. Fill the range J21:J25 with the formula in cell J20.

c. In cell K20, enter a formula using the SUMIF function that totals the funding for Accounting programs, using Programs[Department] as the range, cell I20 as the criteria, and Programs[Funding] as the sum_range.

d. Fill the range K21:K25 with the formula in cell K20.

e. In cell L20, enter a formula using the AVERAGEIF function that averages the funding amounts for Accounting department programs.

f. Fill the range L21:L25 with the formula in cell L20.

20. Antoine asks you to compare the programs with January funding requests by department, start date, and funding amounts. Insert a chart as follows to provide this comparison:

a. Insert a Treemap chart based on the range D5:F12.

b. Use January Programs as the chart title.

c. Move the chart so that its upper-left corner is in cell I27 and its lower-right corner is in cell L40.

d. Remove the legend since the data series are identified in the chart itself.

21. Go to the Funding Subtotals worksheet, which lists the same programs as on the Programs worksheet. Antoine wants you to display the data by department, and then list the programs by start date. (Hint: You must complete all actions in this step and the following step correctly to receive full credit.) Sort the data in the table in ascending order first by department and then by start date, both in ascending order.

22. Antoine also wants to calculate subtotals for each funding type.

a. Convert the table to a range.

b. Insert a subtotal at each change in the Department value.

c. Use the Sum function to calculate the subtotals.

d. Add subtotals to the Funding values only.

e. Include a summary below the data.

f. Collapse the outline to display only the subtotals for each department and the grand total.

23. Go to the Student Center worksheet. Rhodes Technical College is considering whether to purchase a building to use as a new student center on the Manchester campus. Antoine is seeking funding for the building and wants to create a loan analysis to cover the cost of the loan. First, Antoine asks you to update and define names in the worksheet as follows:

a. Delete the Loan_Calculator defined name.

b. For cell C10, edit the defined name to use Loan_Amount as the name.

c. In the range E6:E10, create defined names based on the values in the range D6:D10.

24. Antoine wants you to calculate the monthly payment for a loan to purchase office space to convert and use for the student center. Calculate the payment as follows:

a. In cell E8, start to enter a formula using the PMT function.

b. Divide the Rate by 12 to use the monthly interest rate.

c. Use the Term_in_Months to specify the number of periods.

d. Use the Loan_Amount to include the present value.

e. Display the result as a positive amount.

25. Calculate the total interest and cost as follows:

a. In cell E9, enter a formula without using a function that multiples the Monthly_Payment by the Term_in_Months, and then subtracts the Loan_Amount from the result to determine the total interest.

b. In cell E10, enter a formula without using a function that adds the Price to the Total_Interest to determine the total cost.

26. Antoine asks you to compare monthly payments for interest rates that vary from 4.05 to 5.25 percent and for terms of 120, 180, and 240 months. He has already set up the structure for a data table in the range B14:E27. Create a two-variable data table as follows to provide the comparison that Antoine requests:

a. In cell B14, enter a formula without using a function that references the Monthly_Payment amount because Antoine wants to compare the monthly payments.

b. Based on the range B14:E27, create a two-variable data table that uses the term in months (cell E7) as the row input cell and the rate (cell E6) as the column input cell.

27. Antoine has three other options for purchasing a student center building. In the first scenario, he would pay off the loan in 10 years at an interest rate of 4.55 percent. He wants to determine the monthly payment for the first scenario. In cell H12, insert a formula using the PMT function using the monthly interest rate (cell H8), the loan period in months (cell H10), and the loan amount (cell H6) to calculate the monthly payment for the 10 Years scenario.

28. In the second scenario, Antoine could pay back the loan in 15 years and make a monthly payment of $30,000 at an annual interest rate of 4.65 percent. He wants to know the loan amount he should request with those conditions. In cell I6, insert a formula using the PV function and the monthly interest rate (cell I8), the loan period in months (cell I10), and the monthly payment (cell I12) to calculate the loan amount for the 15 Years scenario.

29. In the third scenario, Antoine could pay back the loan for 5 years with a monthly payment of $40,000 at an annual interest rate of 4.75 percent and then renegotiate better terms. He wants to know the amount remaining on the loan after 5 years, or the future value of the loan. In cell J13, insert a formula using the FV function and the rate (cell J8), the number of periods (cell J10), and the monthly payment (cell J12) to calculate the future value of the loan for the 5 Years scenario.

30. Antoine is also considering other properties to purchase. The worksheet should list information about these properties, which is contained in a webpage. Import data from the webpage as follows:

a. In the Student Center worksheet, get data from the webpage Support_EX365_CS4-7A_Potential.html. (Hint: Use Windows Explorer to copy the path to the webpage, and then type \Support_EX365_CS4-7A_Potential.html at the end of the path.) [Mac Hint: Open the Support_EX365_CS4-7A_Potential.html file in Excel.]

b. Import only the Potential Properties data. [Mac Hint: Copy the range A2:E7 from the Support_EX365_CS4-7A_Potential.html file.]

c. If necessary, transform the data to use the first row as headers.

d. Load the webpage data as a table to cell K29 in the existing worksheet. [Mac Hint: Paste the range copied above into cell K29 of the Student Center worksheet.]

e. Clear the formats from the range K29:O29 to display the column headers.

31. Antoine wants you to list the property information in the range G16:J21 using his preferred format. Incorporate the imported data in the range G17:J21 as follows:

a. In cell G17, enter a formula using the PROPER function to capitalize the first letter of the word in the Type text in cell K30.

b. Fill the range G18:G21 with the formula in cell G17 without formatting to list the remaining property types using the proper case.

c. In cell H17, enter a formula using the CONCAT function that displays the first name shown in cell M30 followed by a space (" "), and then the last name shown in cell N30.

d. Fill the range H18:H21 with the formula in cell H17 without formatting to list the full names of the remaining contacts.

e. In cell I17, enter a formula using the RIGHT function to insert the last 2 characters on the right of cell L30. Copy the formula in cell I17 to the range I18:I21 without formatting.

f. In cell J17, enter a formula using the LEFT function to insert the first 2 characters on the left of cell O30. Copy the formula in cell J17 to the range J18:J21 without formatting.

g. Hide rows 29 to 34 so the worksheet does not display duplicated data.

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: Manchester Worksheet

Final Figure 2: Nashua Worksheet

Final Figure 3: Concord Worksheet

Final Figure 4: All Campuses Worksheet

Final Figure 5: Programs Worksheet

Final Figure 6: Funding Subtotals Worksheet

Final Figure 7: Student Center Worksheet