Excel is a tool that can be used to do everything from simple calculations like adding, subtracting, multiplying, and dividing to complex statistical calculations and data visualizations. Throughout the course, you will complete 6 assignments that will allow you to learn some of basic Excel techniques. Assignment 1 is designed to give you an understanding of how to write basic formulas in Excel using relative and absolute references. The instructions can be found in the file Excel Assignment 1 – Instructions.docx Download Excel Assignment 1 – Instructions.docxand are also shown below.
The steps to complete this assignment are below.
Watch the video entitled “Hospital Personnel Data File Orientation” for a brief overview of the data file used for the assignments. The data file is quite large. For the first 3 assignments, you will use a smaller subset of the data file. For the last 3 assignments, the entire file will be used.
Watch the video entitled “Excel Assignment 1 Demo – References and Formulas in Excel”. The video uses a different data file to demonstrate the techniques needed to complete the assignment.
Open the demo file called “Excel Assignment 1 Demo.xlsx Download Excel Assignment 1 Demo.xlsx”. This is the file used in the demo video. You can review the completed formulas in the demo file to help with the assignment.
Open the assignment file called “Excel Assignment 1.xlsx Download Excel Assignment 1.xlsx” and complete the following columns:
Pay Period Salary (Column Q) – You are given the information that there are 26 periods in the year in the file. Write a formula using the annual salary and the number of pay periods to calculate the values for the column. You must use only references in the formula. No typed numbers!
Pay Period OECs (Column R) – Other Employee Costs (OECs) are additional costs that organizations incur for each employee beyond just the employee salary. They include things like Social Security, Medicare, contribution to pension plans and health care plans. You are given the information that OECs are 38% of the salary for each employee. Write a formula using the pay period salary and OEC rate to calculate the additional costs the hospital incurs for each employee. You must use only references in the formula. No typed numbers!
Salary + OECs (Column S) – Write a formula using the pay period salary and pay period OECs to calculate total employee cost for each employee. You must use only references in the formula. No typed numbers!