The objective:

This project aims to assess your ability to use Microsoft Office software in a simulated practice environment. You are working as an office assistant reporting to the Clinic Manager of Wellpoint Medical Clinic. Your analysis will include the creation of formulas and charts in EXCEL and querying using ACCESS. In addition, you will be writing a report written in WORD discussing software development methodologies and will deliver a presentation reviewing the process of how you developed your project in POWERPOINT.

Time and Deliverables:

Deadline: Monday December 11th, 11:59PM

(Late submissions (upto2dayslate) will be accepted but there will be a penalty of-20%)

You should submit the following files for final submission (save into a zipped folder named:

Student First Name_Course ID_Final.zip e.g.Tony_BUS160_Final.zip) .rar files not accepted:

1)      FirstName_Wellpoint.xlsx

2)      First Name_Wellpoint.accdb

3)      First Name_Wellpoint.pptx

4)      First Name_Wellpoint.mp4

5)      First Name_Wellpoint.docx

Make sure you check the list of required files. Missing files will not be allowed to be submitted afterwards.

There is a limit of2 (TWO)Submissions(before the deadline)

Part1: Excel Analysis

Wellpoint Medical Clinic has recently completed its first year of operation. An Excel workbook has been started to prepare for the annual report, which will include data about revenue and the clinic's health studies. You will be helping by calculating totals, incorporating data from Access, and inserting charts to complete the workbook.

Open the work book Wellpoint.xlsx

           Format the content on the Department worksheet to ensure consistency in appearance between the datasets. Format the numeric data to ensure it is in the most appropriate format.

           Enter the most efficient formulas/functions to complete the Quarterly Revenue section.

Note: % of Total column–is looking for the % each department contributes to the overall total (you do not need to include a formula in cell I9)

           Providers and staff collectively are eligible for a 20 percent bonus if the total quarterly revenue this year is more than $230,000. Otherwise, the bonus is 15 percent of the quarterly revenue. Next year, the departments will have to earn more than $250,000 per quarter to be eligible for the 20 percent bonus.

Apply conditional formatting as follows to highlight the revenue totals eligible for the higher bonus:

·         In the range C9:F9, apply a conditional formatting Highlight Cells Rule that formats cells greater than 230,000 with Green Fill with Dark Green Text.

·         In the range C24:F24, apply the same conditional formatting to cells with totals greater than 250,000.

.

           Go to the Bonuses Earned data in the range C12:H15.The amount eligible for a bonus depends on the quarterly revenue. The providers and staff reimburse the clinic $1250 per quarter for nonmedical services. The final bonus is 35 percent of the remaining amount

Complete the following tasks:

·         Using the text in cell C12, fill the range D12:F12 with the names of the other three quarters.

·         In cell C13:F13,enter a formula that will calculate the bonus amount based on the value in cell C9 (refer to previous step for bonus amounts)

·         In cell C15:F15,enter a formula to show the net bonus(refer to information above)

·         Add Line spark lines to G13:G15.

The total projected revenue for next year in cell H24 is less than $1,000,000, which is the clinic's goal for the coming year. The Clinic Manager wants to know how much more revenue the General Medicine Department would have to earn to result in total revenue of $1,000,000.

           Use Goal Seek to set cell H24 to 1,000,000 by changing the projected increase percentage for General Medicine

Part2: Database Design and Analysis

Open the Access database file Wellpoint.accdb.

           Create a new table to hold a list of clinic departments –give it a suitable name. Data to be entered is shown below:

           Add restrictions on data entered and modify field properties to ensure that the tables are setup accepting only sensible data and reducing the likelihood of mistakes in data entry.

           Create a suitable relationship between the Department and Providers table.

           Create a form for each table and a user-friendly professional looking Navigation Form for accessing the forms.

           Create FIVE queries. The queries should be seeking information that could be useful to the Clinic ManagerofWellpointMedicalClinic.Yourqueriesshouldallowyoutodemonstratearangeofquery techniques, including ONE crosstab query and ONE action query (note: an action query is one that will modify a table/tables in some way - students using remote access will not be able to run this query)

           Choosetwoquerieswheretheresultswouldbeappropriateforbeingrepresentedinchart format. Export the queries in Excel format and copy and paste into the studies worksheet.

Create suitable charts for each query, using different chart types. Format the charts professionally.

           Create ONE report as none of the queries that you have made, ensure it is formatted professionally.

Part3: Report

Wellpoint Clinic’s current Information System has been under pressure recently due to an increase in patient numbers. The Clinic Manager has decided new information systems that will improve patient care and streamline internal operations are needed. However, the clinic's management team is uncertain about which software development methodology would be most suitable for the project.

The following information about the project has been shared with you:

·         Not all the requirements are clear and may evolve overtime as the development process progresses.

·         The development team will include both in-house developers and external contractors.

·         The clinic's staff is already using a legacy system, and a smooth transition to the new system is essential.

·         The system will need to integrate with existing clinic management systems, such as billing and insurance.