Description
The directions are on the word document and the template that must be used is also included down below. make sure to include a cover page.
Unformatted Attachment Preview
School of Engineering and Computer Science (SECS)
Department of Computer Science and Engineering
Intro Computer and Programming w/Excel
CSI 1200
Assignment 3
Deadline: Monday 11/13/2023 @ 23:59
Total Mark for this Assignment is 100
Name:
ID:
CRN:45642
Instructions:
• You must submit two separate copies (one Excel file and one PDF file) using the Assignment Template on Moodle via
the allocated folder. These files must not be in compressed format.
• It is your responsibility to check and make sure that you have uploaded both the correct files.
• Email submission will not be accepted.
• You are advised to make your work clear and well-presented. This includes filling your information on the cover page.
• You must use this template.
• You MUST show all your work, and text must not be converted into an image, unless specified otherwise by the question.
• The work should be your own.
• Use Times New Roman font for all your answers.
20 Marks
Question One
You track your medical expenses each month. You developed a worksheet that contains dates,
descriptions, amount billed, and applicable copayments. Your health insurance provider details the
amount not covered and how much was covered by insurance. You want to create formulas, format
the worksheet to improve readability, and copy the worksheet to use as a template for the next month.
Adjust Worksheet Structure: Columns and Rows You notice that the 3/21/2024 Vision expense on row
10 is a duplicate of the expense on row 8. You will delete the duplicate row. In addition, you want to
move the 3/2/2024 Pharmacy expense to be listed in chronological order. After you correct the row
issues, you will insert a column and use Auto Fill to create a coding system consisting of the year,
month, and sequential numbering for the expenses. After filling in the expense numbers, you will
increase the column width to display the data.
1.
Open e01c1Medical and save it as e01c1Medical_LastFirst.
2.
Select and delete row 10 that contains the duplicate 3/21/2024 Vision data.
3.
Select and cut row 11 that contains the 3/2/2024 Pharmacy expense and insert cut cells on
row 5.
4.
Insert a new column A and type Expense # in cell A3.
5.
Type 2024-03-001 in cell A4 and use Auto Fill to complete the expense numbers to the range
A5:A11.
6.
Change the width of column A to 12.
Question Two
20 Marks
Format the Title, Replace Text, and Check Spelling You want to enter a title at the top of the worksheet
and center it over the data columns. You will format the title with a larger size, different color, and
bold. In addition, you will replace Lab with Laboratory to provide a more descriptive expense name.
Finally, you will check and correct spelling errors.
7.
Type March 2024 Medical Expenses in cell A1 and merge and center the title over the range
A1:J1.
8.
Apply bold, Green font color, and 14-pt font size to the title in cell A1.
9.
Find all occurrences of Lab and replace them with Laboratory.
10. Check the spelling in the worksheet and correct all spelling errors.
Question Three
20 Marks
Insert Formulas and Apply Number Formats You are ready to enter formulas to calculate the adjusted
bill, the amount you owe, and the percentage of the adjusted bill that you paid through copays and
amount owed.
11. Calculate the Adjusted Bill in cell F4 by subtracting the Amt Not Covered from the Amount
Billed. Copy the formula to the range E5:E11.
12. Calculate the Amount Owed in cell H4 by subtracting the Insurance Paid and Copay from the
Adjusted Bill. Copy the formula to the range H5:H11.
13. Calculate the % of Adj Bill in cell J4 by adding the Copay and Amount Owed and then dividing
that amount by the Adjusted Bill. Copy the formula to the range J5:J11.
14. Apply Accounting Number Format to the range D4:I4.
15. Apply Comma Style to the range D5:I11.
16. Apply Percent Style with one decimal place to the range J4:J11.
Question Four
20 Marks
Format the Column Labels and Data Columns Now that the worksheet data is structured and
formulas are entered, you are ready to format the column labels on row 3. In addition, you will apply
a cell style for the last three columns that represent your costs.
17. Wrap text, bold, and horizontally center the labels in the range A3:J3.
18. Apply Green fill and apply Thick Bottom Border to the range A3:J3.
19. Apply the Good cell style to the range H4:J11.
20. Apply Align Right and indent once the data in the range J4:J11.
Question Five
20 Marks
Format the Worksheet To finalize the worksheet, you are ready to select landscape orientation so that
it will fit better on the page when printed. In addition, you will set a larger top margin, center the
worksheet between the left and right margins, and insert a footer. Finally, you will rename the sheet
tab, copy the worksheet, and delete some data to use as a template for April.
21. Select Landscape orientation.
22. Set a 1″ top margin and center the worksheet horizontally between the left and right margins .
23. Set 110% scaling.
24. Insert a footer with your name on the left side, the sheet name code in the center, and the file
name code on the right side .
25. Rename Sheet1 as March. Copy the worksheet, place the duplicate to the right, and then
rename it as April.
26. Change the title to April 2024 Medical Expenses in the April worksheet. Delete data in ranges
A4:E11 and G4:H11. The formula results in column J display #DIV/0! because you deleted the
data, which generates a divide by zero error. Type 1 in cell D4 and copy the value to the range
D5:D11 as placeholder values to avoid displaying the error results .
27. Save and close the file. Exit Excel. Based on your instructor’s directions, submit :
e01c1Medical_LastFirst
Date
Description
3/1/2024 Medical Exam
3/5/2024 Radoilogy
3/5/2024 Lab
3/15/2024 Medical Supplies
3/21/2024 Vision
3/25/2024 Pharmacy
3/21/2024 Vision
3/27/2024 Lab
3/2/2024 Pharmacy
Amount Billed
Amt Not Covered
Adjusted BillInsurance Payd
Copay
Amount Owed
223,76
98,16
95,6
30
4277,41
3754,24
280,78
100
94
42,69
51,31
0
356,17
0
0
100
125
27,89
40
30
385
0
225
0
125
27,89
40
30
94
32,69
40
0
267,47
0
133,73
0
% Pd of Adj Bill
Purchase answer to see full
attachment
