The hourly pay rate for in-house guards is $18.50 an hour. The hourly rate for guards from SecurityPlus is $35.00 an hour.

Fringe benefits (health benefits, vacations, etc.) apply only to BA, Inc employees. They are 35% of the hourly pay rate.

Overhead (all costs on the income statement except for direct labor, direct materials, and direct expenses that are allocated to departments within a company) applies only to BA, Inc. employees. It is 50% of the hourly pay.

The annual increase in the liability insurance will be $10,000 for the in-house guards. There is no increase for the hired guards because SecurityPlus insures its own employees.

The cost of uniforms/cleaning will be $5,000 for the in-house guards. This will not be a cost for the contracted guards because SecurityPlus supplies its employees with clean uniforms every day.

You estimate that in-house guards will require $2,500 worth of miscellaneous equipment in the course of the year. This will not apply to the contracted guards because SecurityPlus supplies all the necessary equipment used by its guards.

The desired security guard coverage is a minimum of 7 days a week for 16 hours a day. There are 52 weeks in a year.

Review the Excel Chapter 6 Chapter Assessment as a example and then do the folowing:

Create an input section with all of the cost data provided.

Calculate the total annual cost of using in-house guards for the minumum coverage desired using the data in the input section. Do not hard code any of the variables. Leave any intermediate calculations with 3 decimal places and show the total annual cost with no decimal places.

Set up the rows for a one-variable data table to calculate the cost using substitution values from 16 hours a day to 24 hours a day. Perform a What If analysis to complete the data table to see the change in cost varying the hours covered per day. If you did not hard code any of the variables in your initial cost formula, it will calculate the cost correctly.

Do the same analysis on a separate one-variable data table if the services of SecurityPlus are used.

Analyze the changes in the costs if SecurityPlus increases their hourly rate. Prepare a Two-Variable Data Table for Security Plus calculating the cost using substitution values from 16 hours a day to 24 hours a day and hourly rates from $35.00 to $40.00 in increments of $1.00. Change the font color to red on all the costs where the rate increase causes the Security Plus cost to be more than for In House Guards for each hour.