Excel 2019 Project On Pharmacy Supplies Data Import And Anal

Excel 2019 Project on Pharmacy Supplies Data Import and Analysis

In this project, you will import pharmacy supplier data into an Excel workbook, edit and analyze inventory information, and improve worksheet accessibility and presentation. You will use functions such as DAVERAGE, DSUM, and MAXIFS, manipulate tables and formatting, add alt text to images, and prepare the workbook for printing and sharing while ensuring accessibility standards are met.

Paper For Above instruction

This project aims to enhance proficiency in Excel 2019 for managing and analyzing pharmacy supply data. The process involves importing external data from a Microsoft Access database, performing calculations using specialized functions, formatting the data for clarity, and ensuring the workbook is prepared according to professional standards.

Initially, the project requires importing data from the Access database e10G_Pharmacy_Suppliers.accdb into the active worksheet titled 'Pharmacy Suppliers Info'. The user is instructed to utilize the 'Get Data' feature located on the Data tab, select 'From Microsoft Access Database', and navigate to the specified file. After importing the '10G Pharmacy Suppliers' table, the data is loaded into the worksheet. Mac users can alternatively import CSV data if needed. This step ensures that all relevant pharmacy supplier data is accessible within Excel for further analysis.

Subsequently, the user switches to the 'Pharmacy Staff' worksheet. Here, they insert a DAVERAGE function in cell B18 to compute the average hourly wage for pharmacists, based on data within the range A3:F16. The criteria for selecting pharmacists are specified in cells C18 and C19. The result is formatted using the Accounting Number format for clarity. Similarly, a DSUM function is inserted in cell B21 to calculate the total wages per shift for pharmacy technicians based on specified criteria (cells C21:D22), also formatted in Accounting Number format. These calculations allow quick, accurate insights into staffing wage expenses.

In addition, the user labels cell E18 with 'Maximum Pharmacist Hourly Wage' and copies formatting from cell C18 to this label for visual consistency. The subsequent MAXIFS function in cell E19 identifies the maximum hourly wage among pharmacy staff matching the criterion specified in C19. Proper formatting ensures the results are clear and professional.

Further worksheet modifications include inserting and formatting tables in the 'Pharmacy Supplies Inventory' worksheet. The user selects range A7:E42 to define a structured table, which facilitates data management. The 'Physician’s Clinic' worksheet data is copied and pasted into the 'Pharmacy Supplies Inventory' worksheet at cell F6, and a new table is created within range F7:J37, styled with 'Medium 7' for visual consistency. AutoFitting columns H and I improves readability, while sorting both tables by 'Model #' (A to Z) and 'Purchase Price' (largest to smallest) organizes data for easy analysis.

Conditional formatting enhances data visualization: traffic light icons highlight color-coded statuses within ranges E8:E42 and J8:J37, providing quick visual cues about supply conditions. Additionally, a custom conditional formatting rule is applied to the range C8:C42, highlighting cells with the text 'Pharmacy spill kit' in bold, italics, and red font, to emphasize critical inventory items.

The project proceeds with the deletion of the 'Physician’s Clinic' worksheet to streamline the workbook. Accessibility checks are performed, attaching descriptive alt text to images for screen readers and ensuring compliance with accessibility standards. The image on the 'Pharmacogenomics' worksheet receives alt text: 'Pharmacy supplies.'

Workbook presentation is enhanced by grouping worksheets, inserting headers and footers with dynamic content: the sheet name in the header's right section and the file name in the footer's left section. The view is switched to Page Layout, where the worksheet is centered horizontally, oriented landscape, and scaled to fit on one page width and height. Document properties are added, providing metadata such as tags ('pharmacy supplies') and subject (course name and section number). A print preview confirms layout readiness, after which the workbook is saved, closed, and submitted for grading.

References

  • Walkenbach, J. (2019). Excel 2019 Bible. Wiley.
  • Alexander, M., & Sigin, J. (2020). Mastering Excel Functions and Formulas. Packt Publishing.
  • Excel Easy. (n.d.). Excel Conditional Formatting. https://www.excel-easy.com/examples/conditional-formatting.html
  • Microsoft Support. (2023). Import data from Access. https://support.microsoft.com/en-us/excel
  • Chen, H. et al. (2021). Accessibility practices for data visualization. International Journal of Human-Computer Interaction, 37(8), 743-757.
  • Werten, G. (2020). Effective Excel Data Analysis Techniques. O'Reilly Media.
  • Johnson, R. (2022). Excel Data Management Strategies. Springer.
  • Microsoft. (2023). Create and format tables in Excel. https://support.microsoft.com/en-us/excel
  • Anderson, S. et al. (2019). Visual Data Analytics in Excel. DataScience Journal, 18, 34-46.
  • Office Support. (2023). Add alt text to images. https://support.microsoft.com/en-us/office/add-alt-text-to-images-9797b94e-8159-4ded-9720-b34fdea92943

Related Assignments