Assessment 2: Group Practical Assignment - MS Excel /MS Word (40%) Due Date: March 20, 2024, 11:59 PM EC/ 10:59 PM JA As members of the Customer Service Department within your organization, your team members and you are expected to review feedback provided by a small group of customers related to their experience with your company. Customers were expected to rate services on a scale from 1 to 5, as follows: 1. Highly Satisfied 2. Satisfied 3. Neutral 4. Dissatisfied 5. Highly Dissatisfied This information was shared openly with the team by the customers, and entered into an MS Excel document for analysis. One year into business, Management wishes to get an idea of how well their products and services are meeting customers expectations as well as the impact on revenue and expenses since going digital. The company Digital Government Inc. recently opened its doors in 2022 and aids customers in completing various government transactions online, across one main office in the City, located at #33 Apricot Lane, Castries, St. Lucia. The company's primary objective is to use information and communication technology (ICT) as a tool to integrate public sector processes to improve services to citizens and businesses. Not only is this modality safe and efficient, but it also reduces the overall cost of Government operations. Having completed the analysis, the team is expected to share feedback with Senior Management to assist them in determining the way forward. Follow the components below to attain a greater understanding of the full requirements. The Customer Service Departments number and email address is (758) 454-DIGI and customerservice@digitalgovernment.inc The assignment consists of two (2) gradable components using Microsoft Excel and Microsoft Word applications which should be completed and submitted together. Each component carries with it several questions for which you are required to provide all solutions to fully maximize on mark potential. Please note that in some instances you are required to integrate features between applications. Download all related files. The completed MS Excel and MS Word files are to be submitted via the course website web tool (the Learning Exchange), using the following naming convention: Sub-Group #_open_s2_24xls – MS Excel file - Sub-Group #_open_s2_24wd1; - MS Word File 1 Sub-Group #_open_s2_24wd2; - MS Word File 2 Please note that “#” in the file names is to be replaced by your Sub-Group number. MS Excel Assignment Instructions For this part of the assignment browse through all worksheets before attempting the question. Using the Microsoft excel workbook assgt_mgmt1000_open_s2_24.xls complete the questions below to provide solutions for the senior management of Digital Government Inc, regarding their services: Using the "Results" worksheet: a. Provide the relevant formulas to calculate the average age of respondents accessing services at the company. Please format the values using zero decimal points. [4] b. Determine the average satisfaction level across all respondents using the appropriate function [4] c. Using the appropriate feature, dynamically apply the following formats to the necessary rows as follows: i. Dissatisfied customers, should be red and bolded [2] ii. [2] Highly Satisfied customers, should be green and italicized d. Complete using the “Satisfaction Level" worksheet, the distribution across satisfaction levels [4] e. Having completed question d above, create an exploded 3D pie chart in a new worksheet called “Satisfaction Levels Charts”. Include all relevant labels, titles and legends while changing the default colors. [10] f. Center the titles across the data below and bold and italicize the company name across the "Results" and "Income Statement” worksheets [4] Using the "Income Statement” worksheet: g. Complete the formulas in cells provided for revenues* for 2023. 2023 revenues have increased by 3.5% over 2022 values. [10] h. Having completed question (g) above, create a 3-D 100% stacked column chart representing the values across the 2 years for all revenue items with all relevant labels, titles and legend while changing the default colors. The chart is to be inserted on a separate worksheet, called “Revenue Chart Comparison” Hint: change the font size on the text areas on the chart to assist in resizing the chart. [10] i. Complete the formulas in cells provided for expenses for 2023. 2023 expenses have decreased by 1.5% over 2022 values [10] j. Having completed question (i) above, create a 3-D clustered column chart representing the values across the 2 years for all expense items with all relevant labels, titles and legend while changing the default colors. The chart is to be inserted on a separate worksheet, called "Expense Chart Comparison” Hint: change the font size on the text areas on the chart to assist in resizing the chart. [10] k. Using the correct function or formula calculate the total expenses and losses in the cells provided. [4] 1. Using all relevant cells calculate the income tax expenses for each year. m. Using the correct function or formula calculate the net income. [10] [2] n. Format the comparative income statement using dollars signs where necessary. All values are to have the thousand separator format with ZERO decimal places [4] o. Using functions, complete the "Aggregates” worksheet. (Hint: must use Income Statement worksheet") [3] p. Save your MS Excel workbook as Sub-Group#_open_s2_24xl for submission. For example Sub-Group 1_open_s2_24x1. MS World Assignment Instructions 1. Referencing your completed MS Excel assignment, prepare a brief report in the form of a letter, to be sent to ALL Senior Managers. In the introductory part of the letter: i. ii. iii. iv. Briefly inform these stakeholders of three (3) benefits of conducting customer surveys as a proactive approach to gathering feedback and improving the effectiveness of the new initiative and how Microsoft Excel made it easy to process the data to derive the information presented in the report. Briefly discuss the feature of MS Word being used to generate these reports. Identify two (2) benefits of this feature and why other Managers should aim to use it. Give a brief analysis of the data found in the pie chart as well as those found in the two column charts created (in your MS Excel assignment), considering the recent move to conducting government transactions online and include these graphics in the report. The report should be no more than 1000 words. Note that all employee internal contact information is located in the "Employee Department Contacts” MS Excel worksheet. Prepare the aforementioned by following the additional instructions below. 2. Prepare a professionally constructed letter using the Company name, Department Name, telephone number, and email address as provided in the instructions and a resized graphic of computer as part of the letterhead as relevant salutations. In addition, ensure requirements at points i. to iv. above are included. 3. All formatting stays at the default except for the following: [10] [30] i. Left and right margins : 0.5" ii. iii. Page numbers – bottom right e.g. “3 of 5” where “3” is the page number and “5” is the total number of pages. Page numbers should NOT be shown on first page The description "Customer Satisfaction Analysis for 2022/2023" in the left header. Header information should NOT be shown on first page iv. Your Sub-Group # in the right header. Header information should NOT be shown on first page V. "First line indent” on each paragraph e. c. Include, from your MS Excel assignment, a copy of the Results sheet. In one sentence briefly describe the figure copied in this letter [10] d. Complete the report as your main document using the Mail Merge feature in MS Word. Include and ensure that all necessary fields based on the requirements mentioned above are visible in your main document. Save the main document as Sub-Group #_open_s2_24_wd1 [20] Generate the required letters (to the persons mentioned above only) and save the generated file as Sub-Group #_open_s2_23_wd2. [20] f. Submit both documents along with your MS Excel file to the drop box for grading.