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

GETTING STARTED

1. Save the file SC_EX365_CT_CS4-7C_FirstLastName_1.xlsx as SC_EX365_CT_CS4-7C_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_CT_CS4-7C_Enrollment.xlsx

Support_EX365_CT_CS4-7C_Potential.html

Support_EX365_CT_CS4-7C_Departments.txt

3. With the file SC_EX365_CT_CS4-7C_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 three worksheets to make changes to them at the same time. The first change is to display today's date. To the right of the "Date:" text on the first worksheet, enter a formula using a function to display today's date.

2. Use the "Quarter 1" text in the Enrollment Projections section to fill in 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 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 based on a growth series using the appropriate step value for a 3 percent per quarter increase.

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

a. Enter a formula using the SUM function and a 3D reference to total the Quarter 1 enrollment in the Accounting department for the Manchester, Nashua, and Concord campuses.

b. Copy the formula to calculate the enrollment for the other departments for all four quarters.

6. Antoine also 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_CT_CS4-7C_Enrollment.xlsx.

b. To the right of the "Total enrollment last year" text, insert a formula using an external reference to the total revenue in the All Campuses worksheet in the Support_EX365_CT_CS4-7C_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 contributed to the total enrollment.

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, and change the number format to Percentage with 1 decimal place.

c. Explode the largest slice by 10 percent.

d. Change the chart colors to Monochromatic Palette 1 to coordinate with the colors in the workbook, and then apply Style 5 to make the chart more striking.

e. 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. [Mac Hint: Select Stacked Bar chart from Recommended Charts on the Insert tab.]

b. Modify the chart so it compares 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 and use Four-Year Programs as the chart title.

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

d. 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_CT_CS4-7C_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 the upper-left cell of the Departments section in 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 first row of the table to display the column headers.

12. Antoine asks you to provide a way to sort and filter the enrollment data. Format the range in the Enrollments section 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 the Approved Programs section, below the first set of column headings, type Yes for Approved? as the value to filter on in the criteria range.

b. Create an advanced filter using the Programs table as the List range.

c. Use the first set of column headings and the data row in the Approved Programs section as the Criteria range.

d. Copy the results to another location, starting in the range containing the second set of column headings in the Approved Programs section.

e. Insert a table using the range containing the column headers and results of the advanced filter 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, 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 Program Information section of the worksheet includes 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. In the cell that should display the program name, enter a formula using the XLOOKUP function that looks up the Program ID value in the ID column, and then returns the corresponding program name as an exact match.

17. Antoine also wants you to list the start date of the funding for the program identified in the cell containing the program ID to look up. In the cell that should display the start date, enter a formula using the XLOOKUP function that looks up the Program ID value in the ID column, 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 the cell that should display the number of programs, create a formula using the DCOUNT function to count the number of programs with funding amounts more than $15,000, using the Programs table as the database, "Funding" as the field, and the range with the specified conditions as the criteria.

b. In the cell that should display the average funding, 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 with the specified conditions 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 the cell that should display the number of programs in the Accounting department, enter a formula using the COUNTIF function that counts the number of Accounting programs, using Programs[Department] as the range and the cell containing the appropriate department name as the criteria.

b. Fill in the number of programs for the other departments.

c. In the cell that should display the total funding for the Accounting department, enter a formula using the SUMIF function that totals the funding for Accounting programs, using Programs[Department] as the range, the cell containing the appropriate department name as the criteria, and Programs[Funding] as the sum_range.

d. Fill in the total funding sums for the other departments.

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

f. Fill in the average funding sums for the other departments.

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 containing the column headers, department names, dates, and funding amounts for January.

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 using the Sum function at each change in the Department value for Funding values only and include a summary below the data.

c. 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 the appropriate cell, edit the defined name to use Loan_Amount as the name.

c. In the cells for the rate, term in months, monthly payment, total interest, and total cost values, create defined names based on the values in the corresponding range.

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 the cell that should display the monthly payment, start to enter a formula using the PMT function.

b. Divide the rate by 12 to use the monthly interest rate, use the term in months to specify the number of periods, and use the loan amount to include the present value.

c. Display the result as a positive amount.

25. Calculate the total interest and cost as follows:

a. In the cell that should display the total interest, 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 the cell that should display the total cost, 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 Varying Interest Rates and Terms section. Create a two-variable data table as follows to provide the comparison that Antoine requests:

a. In the first cell below the Rate column heading, 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 containing the numeric values in the Varying Interest Rates and Terms section, create a two-variable data table that uses the term in months as the row input cell and the rate 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. Insert a formula using the PMT function using the monthly interest rate, the loan period in months, and the loan amount 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. Insert a formula using the PV function and the monthly interest rate, the loan period in months, and the monthly payment 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. Insert a formula using the FV function and the rate, the number of periods, and the monthly payment 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_CT_CS4-7C_Potential.html. (Hint: Use File Explorer to copy the path to the webpage, and then type \Support_EX365_CT_CS4-7C_Potential.html at the end of the path.) [Mac Hint: Open the Support_EX365_CT_CS4-7C_Potential.html file in Excel.]

b. Import only the Potential Properties data. [Mac Hint: Copy the range A2:E7 from the Support_EX365_CT_CS4-7C_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 the first cell to the right of the Potential Properties section and one row below the Varying Interest Rates and Terms table in the existing worksheet. [Mac Hint: Paste the range copied above into this cell in the Student Center worksheet.]

e. Clear the formats from the imported table header row to display the column headers.

31. Antoine wants you to list the property information in the Potential Properties section using his preferred format. Incorporate the imported data in the Potential Properties section as follows:

a. In the first cell below the Type column heading, enter a formula using the PROPER function to capitalize the first letter of the first word in the Type column of the imported data.

b. Fill the rest of the Type column without formatting to list the remaining property types using the proper case.

c. In the first cell below the Contact heading, enter a formula using the CONCAT function that displays the first name shown at the top of the Contact First column followed by a space, and then the last name shown at the top of the Contact Last column.

d. Fill the rest of the Contact column without formatting to list the full names of the remaining contacts.

e. In the first cell in the Zone column, enter a formula using the RIGHT function to insert the last 2 characters on the right of the first cell in the Location column. Copy the formula without formatting to list the other zones in the Zone column.

f. In the first cell in the Months on Mkt column, enter a formula using the LEFT function to insert the first 2 characters on the left of the first cell in the On Market columns. Copy the formula without formatting to list the other months in the Months on Mkt column.

g. Hide the rows containing the imported data 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