Need to figure out fixed and variable cost with the equation

CIS 311 - Project Management Make or Buy Analysis (44 points) This assignment will give you experience analyzing pertinent data from a possibly meaningless and entirely over-written \"scenario\" narrative and then performing the simple mathematical steps required for a basic Make-or-Buy (Buy-or-Lease / Buy-or-Build) Analysis. What you need to do: Watch video lecture L18 Read / re-read the \"Make-or-Buy\" section of Chapter 5 (pages 208-211) Using the concepts introduced in the text and video lecture, provide a \"Build-or-Buy\" recommendation based on the following scenario (using Excel): Your company has suffered a server-farm meltdown, and the person responsible for the backups hasn't returned from a trip he took to Tahiti early last year. All your company's data will need to be re-entered by hand and all hardware will be replaced and updated. You, the Project Manager, have to decide whether you should perform this monumental task in-house (Build), or outsource it to Luke's Sensitive Data and Recovery, located in Portland, Oregon (Buy). The costs for this project will be substantial, so a careful analysis is required. Estimates for each option are as follows: (20) BUILD - Create the equation to build using the following information: Hardware Costs: nine (9) new computers will be needed for this project, since all the available computers your company currently owns need to be updated. Each new computer will cost $1,855.00. You will also need to purchase the Extended Warranty at a price of $450.00 per machine. Service Costs: Your current Computer Maintenance Services Company will cover the new machines at 1/3 their normal rate of $165 per machine per day for the first year, at which time the machines will be useless, completely obsolete, and given away as Door Prizes at the semi-annual company Arbor Day party. The per-day charge is non-negotiable, applied every day of the week (whether the machines are in use or not), and each year's charges must be paid for up front, in cash. Labor Costs: Since your company CEO has a really bad habit of hiring unqualified and inexpensive staff, you will need to staff your new computers with Software Engineers (SE) and Computer Programmers (CP). Each of the three (3) SE's assigned to your project make $88 per hour (8 hour day), and each of the four (4) CP's make $67 per hour (8 hour day). Since this project is so important, and since all your SE's and CP's have threatened to quit because they want a raise, management has also agreed to pay Key Assignment Bonuses to the seven (7) employees to get them on-board. Each SE will be paid a $10,100 cash bonus, and each CP will be paid a $7,800 cash bonus. SE's and CP's are both assigned to work on this project 7 days per week (8 hour day), with no additional overtime or benefit costs incurred. Training will be provided for all new employees. Two trainers will be brought in, each charging $50 per hour. The training will be for 2 weeks; 10 - 8hr days total. The trainers will be paid in full before training begins. Nine employees will also be given iPhones and will be required to keep in close contact at all times, including weekends. The iPhones were purchased several months ago. However, there will be a monthly contract fee of $1,086.90 (based on a 30 day month) for each phone. (16) BUY - Create the equation to buy using the following information: Hardware Costs: nine (9) new computers will cost $9,600.00 each. Luke offers a 10% discount per machine. You decide not to purchase an extended warranty. Your current Computer Maintenance Services Company will cover the new machines at a price of $45.00 per machine per month for the first year. Yearly service contracts must be paid in full, up front. Hourly Fees: $35.80 per Senior Data Entry Clerk (SDEC), and $26.42 per Junior Data entry Clerk (JDEC). Luke runs his shop seven days per week (8 hour day), and has guaranteed you 25 SDEC's and 20 JDEC's every day until the project is complete. Nine employees will also be given iPhones and will be required to keep in close contact at all times, including weekends. The iPhones were purchased several months ago. However, there will be a monthly contract fee of $1,086.90 (based on a 30 day month) for each phone. Twelve employees will be sent to training. Training fees for 5 days for each employee will be $100 per day paid in full before training begins. Travel and hotel fees will also need to be paid for the 12 employees participating in the training. Each flight costs $480 round trip. Hotel rates are $90ight for 5 nights. All employees are required to share a room with another employee. All travel fees are paid before employees travel. (8) To receive credit for this assignment, you must answer these additional questions showing formulas and all calculations: (2) At which day do the BUILD and BUY options become equal? (Round your answer to the nearest day). Show your equation and all calculations as to how you arrived at it. (3) If Luke's Sensitive Data and Recovery could finish the data entry in 23 days, and your in-house team could finish it at the same time, which option would you recommend, and why? What costs are associated with each option at this point in time? Show your equation and all calculations as to how you arrived at it. (3) If Luke's Sensitive Data and Recovery could finish the data entry in 27 days, and your in-house team could finish it at the same time, which option would you recommend, and why? What costs are associated with each option at this point in time? Show your equation and all calculations as to how you arrived at it. You must show all calculations using the correct formulas to earn credit for this assignment (how did you obtain the numbers used in your analysis?) Label all. You must also have clear answers to the questions posed above to receive credit for this assignment. Make it clear WHAT your numbers are and HOW you arrived at them. When finished, save your analysis as an .xlsx file to submit to Blackboard. See below for extra hints. Extra hints: First watch the lecture video. When you are deciding whether to make (in-house) or buy (from another vendor), you need to figure out the costs for each option. Look at each option separately. Each option has both fixed costs (pay immediately) and variable costs (spread payment over the life of your project); you keep them separate because they are different. First thing to do is to look at the build option. Identify all costs that must be paid up-front - these are fixed costs; add them up but identify each one. fixed cost1.......(how you determine this cost)........$ fixed cost2.......(how you determine this cost)........$ Total fixed costs...........................$ Then, identify all those costs that will be paid on a daily basis; those are your variable costs; add them up but identify each one. variable cost1.......(how you determine this cost)........$.../D (per day) variable cost2.......(how you determine this cost)........$.../D (per day) Total variable cost..................$.../D (per day) The build side of the equation has a fixed side and a variable side; do not simply add them together - they are different: total fixed costs + total variable costs (D) ---- "D" is the number of days you are solving (you don't know how many days your project will go on at this point) Next, do the same thing for the buy option. On the lecture video, I've explained how to come up with the equation using 4 examples going from very easy to more complex. This assignment is a step beyond that because in business, there are always many costs to deal with; you need to add up all of the fixed costs and variable costs on each side to determine the equation. build side buy side total fixed costs + total variable costs (D) = total fixed costs + total variable costs (D) Now, use simple math to get "D" on one side of the equation and that will give you the number of days that build and buy are equal; this means that if you had this number of days, you could either choose build or buy because the cost would be exactly the same. The most challenging part of this assignment is to determine which costs are fixed (paid up front) and which are variable (paid on some sort of schedule - daily, weekly, monthly, etc). It may not be straight forward because business costs are more complex, but the math is not difficult; keep them on opposite sides of the equation for build and again for buy. As you watch the video, I invite you to stop play and attempt to solve the problem; then start it again to see if you got the correct answer. Just do that. Get used to working a simpler problem and work up to the assignment. Once you have your equation and you know the day when the costs are equal regardless of build or buy, you use the equation to determine alternative options; what if you have more days to play with, or less? How does that affect your decision? You build the equation so that you don't need to come up with a huge spreadsheet of figures that calculate each day's costs. No one really wants to have to look at miles and miles of values to make a quick decision about money (especially your sponsor). "Bottom-line" it for them. Be sure to use the 'official class file format' for naming your file: first initial Lastname-A3.xlsx (points will be deducted for incorrect filenames). (e.g. tbarry-A3.xlsx) Email submissions to the professor will NOT be accepted under ANY circumstances; all submissions must go through Blackboard