Please show steps to complete this Assignment

Project 4 Hire or Contract Overview

In this project, your consulting company has been hired to help a startup company, BankAssist, Inc. The company develops software for banks and has been growing quickly. Employees have begun to work at all hours of the day, often on projects that require security clearance. The company now needs to utilize security guards to protect the employees and the company assets (mainly the software being developed). They have asked you to perform an analysis to determine if they should hire their own full-time security guards as employees or contract this service with a security guard firm, SecurityPlus. Your first step will be to analyze the costs and construct scenarios to look at the costs of covering more hours. You will compare the costs of hiring your own full-time security guards as employees or contracting this service with SecurityPlus. Then you will write an internal memo to the BankAssist, Inc.s CEO explaining your analysis and recommendation.

Background

A hire-or-contract decision is a type of make-or-buy decision. Such a decision requires a detailed consideration of the cost of each alternative, including such hidden costs as employee fringe benefits, overhead, insurance, uniforms, and equipment. Some of the costs are fixed (a set amount) and some are variable (vary based on a factor which in this case is number of hours worked). Make or Buy Decisions are covered in depth in ACC204-Managerial Accounting.

Project 4 - Hire or Contract Spreadsheet Instructions:

Put a title at the top left of the spreadsheet. Use a Date Function to always display the current date under the spreadsheet title. Put your name under the spreadsheet title as well.

Relevant Cost Data:

The hourly pay rate for in-house guards is $15.50 an hour. The hourly rate for guards from SecurityPlus is $30.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 $15,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 $4,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 an example and then do the following:

Create an input section with all of the data provided.

Calculate the total annual cost of using in-house guards for the minimum coverage desired using the data in the input section. Do not hard code any of the variables.

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 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 $30.00 to $40.00 in increments of $2.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.

Memo:

In the memo state your recommendation to hire or outsource the guard function to cover 7 days a week for 16 hours a day. Explain what you did to analyze the data and the reasoning behind your recommendation. Then state the cost of using each resource to guard the facility 24/7 and recommend which resource to use if the coverage time increases. Give the specific costs behind each of your recommendations. Use block paragraphs (no indentation of the first line) and justification of the text.

Make a recommendation to management if SecurityPlus increases its rates. Be specific at what point the costs will be greater than hiring In House guards.