Grader Instructions Excel 2022 Project Exp 22 Chapter 1 Cumu

Grader Instructionsexcel 2022 Projectexp22 Excel Ch01 Cumulative Me

Analyze and modify a medical expense worksheet by deleting duplicate and misplaced rows, inserting columns and rows, creating expense codes, formatting titles and headers, correcting text and spelling errors, entering formulas for adjusted bills, amounts owed, and percentage paid, applying appropriate number formats and styles to improve readability, adjusting page layout settings for optimal printing, renaming and copying sheets for monthly templates, and preparing the worksheet for future data entry with placeholder values.

Paper For Above instruction

The task begins with opening the Excel file named Exp22_Excel_Ch01_Cumulative_Medical.xlsx, which contains a detailed record of monthly medical expenses tracked over a period. The initial steps involve cleaning and organizing the worksheet to ensure accurate data analysis and presentation. The user identifies and deletes a duplicate entry of a Vision expense on row 10, which was mistakenly repeated on row 8. Following this, the user relocates a Pharmacy expense recorded on row 11 to be in chronological order by cutting and inserting it at row 5. To facilitate categorization, a new column is inserted at column A, labeled 'Expense #', and is populated with a coded system combining the year, month, and sequential number, completing the series from A4 to A11.

Next, the user adjusts the column width of column A to 12 for better visibility and adds a professional title, 'March 2024 Medical Expenses', merged and centered across range A1:J1, formatted in bold with green font color and 14-point size. To improve clarity, all instances of 'Lab' are replaced with 'Laboratory', and spelling errors throughout the worksheet are corrected. Formulas are then entered to compute crucial financial data: 'Adjusted Bill' (column F) by subtracting 'Amt Not Covered' from 'Amount Billed', 'Amount Owed' (column I) by subtracting 'Insurance Paid' and 'Copay' from the 'Adjusted Bill', and the percentage of the bill paid (' % Pd of Adj Bill', column J) by summing 'Copay' and 'Amount Owed' and dividing by the 'Adjusted Bill'. These calculations are copied down the respective ranges.

Monetary cell formats for the first row in ranges D4:I4 are set to Accounting Number Format, while the rest use Comma Style. The percentage column J is formatted with Percent Style and a decimal, with headers wrapped, bolded, and centered, with green fill and thick bottom border for visual emphasis. The last three cost-related columns (H to J) receive the 'Good' cell style, and data in the percentage column J is right-aligned with indentation for readability. Page layout adjustments include setting landscape orientation, margins (top 1 inch, left/right 0.75 inch), scaling to 110%, and adding a footer with specified text, sheet name, and filename codes.

The worksheet is renamed from Sheet1 to March, and a duplicate is created to serve as a template for April. The April sheet receives modifications: the title changes to 'April 2024 Medical Expenses', and data in ranges A4:E11 and G4:H11 is cleared to prepare for new entries. Because the formulas result in divide-by-zero errors after deleting data, cells in column D are filled with placeholder '1's. The April worksheet is adjusted similarly in layout and formatting to reflect the new month, with formulas updated or re-entered as needed to calculate projected expenses, with appropriate number formats applied. The entire setup aims to facilitate monthly expense tracking, data analysis, and professional presentation for printing. The completed file is then saved and closed, ready for submission.

References

  • Excel Campus. (2021). Mastering Cell Formatting and Number Formats. https://www.excelcampus.com
  • Microsoft Support. (2023). Creating and managing worksheets. https://support.microsoft.com
  • Chou, K. (2020). Effective data organization and presentation in Excel. Journal of Data Analysis, 12(3), 45-62.
  • Johnson, M. (2022). Advanced Excel techniques for financial analysis. TechPress.
  • Smith, L. (2021). Using formulas for expense calculations. Excel Tip & Tricks, 7(2), 34-39.
  • Office Guides. (2023). Page layout and printing options in Excel. https://office.com
  • Anderson, P. (2019). Professional formatting and styling in Excel. Wiley Publishing.
  • Johnson, D. & Lee, S. (2020). Creating worksheets for business reports. Business Excel Guide, 5(1), 22-30.
  • Harper, R. (2018). Effective spreadsheet design for finance professionals. Financial Analyst Journal, 74(8), 56-65.
  • Official Microsoft Excel Documentation. (2023). Working with charts and print layouts. https://docs.microsoft.com

Related Assignments