Create a budget in Microsoft Excel utilizing the following information. Put the category and the dollar amount in the spreadsheet. Show your work for the calculations and make sure your answers are labeled. If you miss one item in Parts 1A-E, it will affect parts 1F-G.

Salary Costs-Please put the job position in the left column of the Excel Spreadsheet. Enter the hourly wage cost. Then add a column for the number of hours worked per week. Enter the number of weeks per year. In the next column, you will enter the total number of hours worked per year. Calculate the total annual salary for each employees position. You should have a total cost for each employee for the salary without benefits and one salary cost with benefits. You should have a separate line for each position. Also, calculate the total cost of the salaries for all the positions in a line called Total Salary Expense without benefits. Calculate the Total Benefits costs for all employees. Calculate the total Cost of salaries (with the benefits).

HIM Director- $40.00 per hour for 40 hours per week

ROI Manager- $18.00 per hour for 40 hours per week

Coder 1- $20.00 per hour for 40 hours per week

Coder 2- $22.00 per hour for 32 hours per week

Health Information Management Clerk 1- $12.50 per hour for 40 hours per week

Health Information Management Clerk 2- $14.50 per hour for 40 hours per week

Data Analyst- $30.00 per hour for 40 hours per week

DRG Validator- $30.00 per hour for 32 hours per week

Privacy Officer- $45.00 per hour for 40 hours per week

B. Fringe Benefit Cost- The fringe benefit cost (Health Insurance, Dental, Vision, etc.) is 25% of the salary cost for each full-time employee. It is 15% of the salary cost for those individuals who work 32 hours per week.

C. Contract Services- $150,000- This cost is the total amount, so you just need to enter it into the spreadsheet.

D. Training Costs- Calculate the total training costs utilizing the following information. You have 3 RHIAs who will be attending the AHIMA Meeting in Miami and then the IHIMA Meeting in Indianapolis. There is no food, hotel, mileage, and flight for the meeting in Indianapolis. The department will just need to pay the registration fee for each employee.

The AHIMA registration fee for each person is $795.00

Each person is given $55 per day for food for three days.

The hotel in Miami is $200.00 per night per person for three nights.

On ground transportation for all three employees- $300.00

American Flight Cost per person- 270.00

IHIMA Registration fee- 330.00 per person for all three days

E. Supply Costs- Calculate the total supply costs utilizing the information below. You must show all of the supplies within this expense category-

Paper- $17,000.00

Postage- $1,000.00

Toner- $12,000.00

Books-$4,500.00

F. Calculate the total cost for all expenses.

G. The HIM Director budgeted $780,000 for operating expenses. Was she over or under her budget? Is this a favorable or unfavorable variance?