Project 5 - Hire or Contract Spreadsheet

Instructions Open Excel. Create a new blank Worksheet. Name it LastNameFirstName_Project5.xlsx. Use a Date Function to always display the current date under the spreadsheet title. Relevant Cost Data: The hourly pay rate for an in-house guard is $10 an hour. The hourly rate for a guard from SecurityPlus is $20 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 $17,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 $3,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 $1,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 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. Create 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 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, you can calculate the cost correctly using that formula. Do the same analysis if the services of SecurityPlus are used.