CISA 315 EXCEL FINAL SKILL ASSESSMENT

Welcome to the Final Exam:

This exam covers the performance objectives and skills presented in Modules 4, 5, and 11. Therefore, limit your formulas, functions, and charts only to what was covered in these Modules.

Important Reminders:

1. You are not allowed to communicate with another person about any part of this exam for any reason while the exam period is active.
2. You have five (5) hours to complete the exam and upload it back to its CANVAS folder.
3. Do not exceed your exam time.
4. Carefully read all instructions. Treat all instructions as part of the exam.
5. Use your textbook as a reference.
6. Check your formulas, functions, and results before you submit your exam for grading.
7. You can only take the exam once within the exam period and can only submit one exam file.
8. Do not submit your exam as an attachment, PDF, or ZIP file.
9. You must submit your exam only to its CANVAS folder.

Best of Luck.

CASE: Today is your first day at work after returning from a very long vacation. As you anticipated, a lot of work is sitting on your desk seeking your attention. The most pressing assignment is from Ms. Mary Watson, Reason2Go (R2G)’s vice president of operations. Ms. Watson is requesting a series of worksheets that will help her analyze R2G’s corporate data and make sound decisions on the company’s operations, investments, and expansion opportunities.

Let’s Begin:

1. Open the R2G workbook.
2. Apply a worksheet theme that is not the default Office theme.
3. Save the file with your Student ID number and Final as the new file name.

Sales Reps Worksheet:

CASE: Ms. Watson wants the sales representative data she received from HR formatted into a more useful layout.

1. Merge and center A1 across A1:M1. Apply the Title Style and 18-point font.
2. Apply Header 2, 14-point font, and center alignment to A2:M2. Enter your name in A2.
3. On row 3, place two or more text on two rows within the cell. Center and Bold.
4. While maintaining the original data in column A, break this data into columns B:E.
5. Use the data in columns B:E to create proper names in columns G:I.
6. In column K, consolidate the data in columns G:I into the following single string:
   Last Name, First Name Office Years of Service.
   Example: Student, Perfect Sacramento 23.
7. AutoFit/Best Fit all column and row data on this sheet.

2027 Projects Worksheet:

Case: Reason2Go uses tables to analyze project data. Ms. Watson asks you to build a table that will help her manage 2027 project data.

Worksheet:

1. Format all worksheet text with the Heading 4 cell style and 14-point font size.
2. Format labels in A2:I2 onto two rows and center align within their cell.
3. Add a thick outside border to A2:I2.
4. Format Depart Date values with the short date format.
5. Format Price values with the Comma [0] Cell Style.
6. Center align the contents of columns A and C:I.
7. Merge and Center N1:O1, N2:O2, N3:O3, N4:O4, N5:O5, N6:O6, N7:O7, N8:O8, N9:O9.
8. Center align L1:P2 and M11:P20. Right align M3:M9. Left align N3:N9.

Table:

1. Use Table Style Light 17 to create the 2027 Project Profiles table that includes headers.
2. Add "Spaces Available" as a new field name. Format this field like all other fields within the table.
3. Sort the table by Project Code in ascending order.
4. Display the table total row.
5. L1:P20: Is not a table. Apply the same area coloring as A1:J65.

Ranges:

Select the field data and create the following range names to be used in all formulas and functions that involve their data:

• Project
• Depart Date
• Project Capacity
• Spaces Reserved
• Price
• Spaces Available
• Project Lookup: Assign this name to cell M3.

Table Calculations:

1. Use range names in all formulas and functions that reference their data.
2. Use "Spaces Reserved," "Project Capacity," and a formula to determine the "Spaces Available" field value.
3. On the Total row display the following conclusions:
   • The number of active projects.
   • Total departure dates.
   • Average Number of Days formatted as whole days.
   • Total capacity, reserved, and available fields.
   • Highest-priced project.
4. Center all values. Add a comma separator for thousands and remove all decimal places.

Project Calculations:

1. Use Project Code 158A, Project Lookup, and a function to fill in the cell information: N3:N9.

Project Statistics:

Note: R2G conducts advanced sales for its projects. These projects do not have a Depart Date, instead, they are listed as “Advanced” in the Depart Date field.

1. For each project’s Advanced Sales, use a formula to determine its total number, reservations, revenue, and remaining reservations.
2. Bring the Advanced Sales Statistic data to conclusions on its Total row.
3. Apply a comma separator for thousands and remove all decimal places to L20:P20.
4. AutoFit/Best Fit all column and row data on this sheet.

NY and LA Worksheets:

CASE: Ms. Watson asks you to analyze and provide her with information about the New York and Los Angeles branches’ major project sales data for January 2027.

1. While both sheets are the same, they should have different formatting colors.
2. A1:H2: Any Lighter 80% fill. Title Style. 18-points. Bold. Merge and Center A1:H1 and A2:H2.
3. A3:H3 and E4:H4: Format appropriate labels onto two rows. Center align and bold all cells.
4. Add a thick outside Darker 25% border to A3:H3.
5. Format Sale Date values with the short date format.
6. Format all values over a thousand with the Comma style and no decimal places.
7. Apply the Total cell style to A39:C39 and E9:H9 and 16-points to both.
8. Use Excel functions, relative and absolute references to Sum, Count, and Average African Wild Dog, Great White Shark, Dolphin, and Elephant experiences on both worksheets.
9. Bring each worksheet to appropriate conclusions on the Total Sales and Total Experience rows.
10. AutoFit/Best Fit all column and row data on this sheet.

Summary Worksheet:

CASE: Ms. Watson asks you to prepare a January 2027 sales summary. Watson sheet that consolidates New York and Los Angeles sales data in one sheet.

1. Set the font size for the entire worksheet to 16-points.
2. Merge and center A1:E1 and bold the text.
3. Consolidate NY and LA Experiences Sold, Revenue, and Average.
4. For each Experience category, calculate its percentage of revenue.
5. On the Total row, bring cells B, C, and E to a conclusion.
6. AutoFit/Best Fit all column and row data on this sheet.

Annual Revenue Worksheet:

CASE: Ms. Watson asks you to create a series of charts showing revenue trends over the past four years.

Chart 1:

1. Use annual revenue values plus annual and project labels to create a 3-D Clustered Column chart that shows how project revenue varied across the years.
2. Add an appropriate chart title that is Times New Roman and 18-points.
3. Format the title with any WordArt Gradient Fill Reflection style, color, and bold.
4. Insert "Major Projects" as the primary horizontal axis title, Times New Roman, bold, and 10-points.
5. Insert "Project Years" as the primary vertical axis title, Times New Roman, bold, and 10-points.
6. Use the Snap To Grid function to move the chart to H1:N24.
7. Add a Two Period Moving Average trendline to the 2025 data series. Change its width to 3.

Chart 2:

1. Add an Average column to the worksheet. Use a formula to calculate each project’s average revenue.
2. Use annual revenue and average values, and annual and project labels to create a clustered column and line Combo chart.
3. "Revenue and Averages by Major Projects" is the chart title. Times New Roman and 16-points.
4. Format the title with any style, color, and bold.
5. All projects are clustered column charts, and the average is a line chart on the Secondary Axis.
6. Use the Snap To Grid function to move the chart to O1:U24.

Chart 3:

1. Create a 3-D Pie chart that shows only the Total yearly revenue data for 2023 - 2026.
2. Add Center Data Labels and White Background 1 to the slices. Layout 4 to the chart.
3. Explode the smallest slice.
4. Add the chart title: "Total Revenues 2023 - 2026" to the chart.
5. Format the chart title with any WordArt style and bold.
6. Use the Snap To Grid function to move the chart to A12:G28.
7. AutoFit and Best Fit the worksheet.

Funding Sheet:

CASE: Ms. Watson wants you to summarize various expansion funding to include three loan options, savings, and investments.

1. Apply a basic amount of formatting to the sheet that is consistent with formatting on prior sheets.
2. For all calculations: a. Avoid entering a hard value, except for percentages, rate, and term values. b. Use an appropriate Excel Financial function.

Loan options:

• Option 1: $500,000 loan, 20% downpayment, 20 years, 7.00% interest rate with monthly payments.
• Option 2: $500,000 loan, 25% downpayment, 20 years, 7.25% interest rate with quarterly payments.
• Option 3: $500,000 loan, 0% downpayment, 20 years, 8.00% interest rate. The interest rate will not change from an annual value. However, payments will be made twice a month.

Savings:
$5,000 deposit, 10 years, 3.25% interest rate monthly deposits.

Investment:
Monthly payments equal $750,000 mature value in 17 years, 3.25% interest rate.

3. AutoFit/Best Fit all column and row data on this sheet.

BONUS Worksheet: Complete these questions only as time permits.

Ending the Project:

Before you submit this workbook:

1. Check for spelling errors and noticeable mistakes.
2. Check to ensure your project is accurate and complete.
3. Submit your completed project back to its CANVAS folder, with your Student ID number and Final as the file name.