Based on this Case Study, I need a " MS Excel spreadhseet showing your analysis to determine whether the product is most profitable to make or buy " If you could help me outline and calculate this spreadsheet, that would be amazing. Doesn't even have to be perfect!

Case Discussion - Total Cost of Ownership - Personal Computers What is Total Cost of Ownership? The Total Cost of Ownership can be defined as the present value of all costs associated with a product, service or capital equipment that are incurred over its expected life. Typically, these costs can be broken into four broad categories: Purchase Price - The amount paid to the supplier for the product, service or capital equipment. Acquisition Costs - All costs associated with bringing the product, service or capital equipment to the customer's location. Examples of acquisition costs are sourcing, administration, freight, and taxes. Usage Costs - In the case of a product, all costs associated with converting the purchased part/material into finished product and supporting it through its usable life. In the case of a service, all costs associated with the performance of the service that are not included in the purchase price. In the case of capital equipment all costs associated with operating the equipment through its life. Examples of usage costs are inventory, conversion, scrap, warranty, installation, training, downtime and opportunity costs. End of Life costs - All costs incurred when a product, service or capital equipment reaches the end of its usable life, net of amounts received from the sale of remaining product or the equipment (Salvage Value) as the case may be. Examples of End of Life costs are obsolescence, disposal, clean-up and project termination costs. Building a Total Cost of Ownership model Building a TCO model is not an easy task. It requires input from different parts of the organization and a thorough understanding of the process through the entire life cycle. The following steps must be taken to ensure that all costs are captured correctly: Step 1: Map the process and develop TCO categories - Construct a process map from the time a need for the product, service or capital equipment is identified all the way through the life cycle. The activities that you identify will help to develop broad TCO categories. Step 2: Determine cost elements for each category - Using the process map as a guide, identify the sub cost elements that make up each TCO category. See example. Step 3: Determine how each cost element is to be measured - This is a critical step. The metrics must be determined to quantify each of the cost elements identified in Step 2. For example, to quantify the costs of sourcing labor the hourly rate of the individual's performing the sourcing activity and the amount of time he or she spends or will spend doing it will need to be known. Step 4: Gather data and quantify costs - This is the most difficult and time-consuming step. In this step gather data for each of the metrics identified in Step 3 and quantify the respective costs. This requires information from various sources including interviews,surveys, the A/P system and other internal databases. If information from internal databases is used, make sure to validate the numbers. Input errors can sometimes cause the numbers generated by these databases to be significantly inaccurate. Step 5: Develop a Cost Timeline - Construct a Cost Timeline for the length of the life cycle. Place each cost element quantified in Step 4 in the appropriate period. Then calculate totals for each period as shown in the example. Step 6: Bring costs to present value - Computing the present value allows decisions to be made based on present dollars. This is important because a dollar spent one year from now is not worth the same as a dollar spent now. The value of money spent anytime in the future will depend on the organization's cost of capital. To calculate the present value, therefore, obtain the organization's cost of capital from its finance department. Then calculate the present value of each total in the cost timeline by using a present value table or a financial calculator. The sum of present values for each period represents the Total Cost of Ownership. See Example. The importance of "Opportunity Costs" When considering "Usage Costs" make sure to identify opportunity costs, if any. An "opportunity cost" is defined as the cost of the next best alternative. Typical opportunity costs include lost sales, lost productivity and downtime. The absence of these costs in an analysis could lead to an entirely different decision and, possibly, a wrong one as illustrated below. A supply manager looking to purchase a machine was evaluating two alternatives. Alternative A was priced at $100,000 and B at $125,000. The delivery lead-time for Machine A was 90 days and Machine B was 30 days. When determining "Usage costs" for A it was important to add the Lost Revenue that would have been generated during the 60 days (90-30 = 60) had machine B been installed. By including the cost of Lost Revenue, B became the better alternative even though it was priced higher. In another case, a supply manager made the decision, based primarily on price, to purchase Machine Y instead of Machine X. His analysis, however, omitted the opportunity cost from the difference in production capacity between the two machines. Machine X was capable of producing 10% more units than machine Y. In a market upswing, sales potential increased by 10%. Machine Y was unable to handle the increase and a new machine had to be purchased. Had the supply manager selected Machine X, the purchase of a new machine could have been deferred thereby saving hundreds of thousands of dollars. Mistakes like this can easily be avoided by ensuring that all costs, especially opportunity costs, are captured in the TCO. Important factors to consider when building a TCO model Building a TCO can be a costly and time intensive activity. Use it for evaluating larger purchases. Make sure to obtain senior management buy-in before embarking on a full-fledged TCO.It will make data gathering much easier, especially if several people from different parts of the organization have to be interviewed. Work in a team. This will greatly reduce the time required for data collection activities, which can be distributed among team members. Focus on the big costs first. Spending extended periods of time quantifying small cost elements will only delay the decision, which in most cases will not be impacted by them. Make sure to obtain a realistic estimate of the life cycle. A life cycle that is too short or too long could result in a WRONG decision Whether evaluating a purchase option or making an outsourcing decision, a TCO model will ensure that the right decision is made, at least from a cost perspective. Source: Purchasing Today, By Sanjit Menezes, vice president of the Anklesaria Group, Inc., Del Mar, California, January 2001. EXAMPLE Supply manager Patrick Mittlestadt was considering the purchase of 1,000 desktop PC's for his organization. The life cycle was 3 years and the organization's cost of capital was 12%. He calculated the TCO for one of the purchase options as follows: Cost Elements Cost Measures Purchase Price: (step 1) . Equipment (step 2) Supplier quote: $1,200 per PC (Steps 3 and 4) . Software License A Supplier quote: $300 per PC . Software License B Supplier quote: $100 per PC . Software License C Supplier quote: $50 per PC Acquisition Cost: . Sourcing 2 FTE @ $85K and $170K for 2 months . Administration 1 PO @ $150, 12 invoices @ $40 each Usage Costs: . Installation $700 per PC (PC move, install, network) . Equipment Support $120 per month per PC - supplier quote . Network Support $100 per month - supplier quote Warranty $120 per PC for a 3-year warranty . Opportunity Cost - Lost Productivity Downtime 15 hours per PC per year @ $30 per hour End of Life: . Salvage Value $36 per PC 1. Determine the total cost of this contract over 3 years. 2. How would you approach this supplier about reducing the total cost of ownership for computers over the life of this contract?Cost Elements Present Year 1 (Step Year 2 Year 3 5) Purchase Price: Equipment $120,000 Software $300,000 License A Software $100,000 License B Software $50,000 License C Acquisition Cost: Sourcing 542,500 Administration $150 5480 5480 5480 Usage Costs: Opportunity $450,000 $450,000 $450,000 Cost - Lost Productivity Installation $700,000 Equipment $1,440,000 $1,440,000 $1,440,000 Support Network $1,200,000 $1,200,000 $1,200,000 Support Warranty $120,000 End of Life Costs: Salvage Value ($36,000) TOTAL $2,512,650 $3,090,480 $3,090,4805 $3,054,480 Present Values $2,512,650 $2,759,799 $2,463,113 $2,174,790 @ 12% (Step 6) Total Cost of Ownership Based on this model, I would explore the possibilities of reducing service costs such as equipment support and network support - these appear to be the highest value, and contribute most to costs. This is also typically the most profitable area for the supplier, as services are often not audited.Your Assignment Purchasing manager Meg Tilley is considering the purchase of 300 shipping label printers for the ten manufacturing plants that comprise her company. The amortized life of the printers is 5 years and the company's cost of capital is 10%. The target supplier offers a purchase outright or a lease option. The lease may be cancelled given a 60-day notice. The target supplier offers a one-year factory warranty covering parts and onsite service. supplier. For the initial purchase of the printers, the supplier will invoice one time and if extended service is requested, the supplier will invoice quarterly. Installation may be done by the supplier or by in-house maintenance personnel. However, the equipment warranty gives several exclusions if the equipment is installed by technicians other than certified supplier technicians. Based on the below scenario. Answer the following questions: Which ownership option is most advantageous for the company - Purchase outright or lease? Which installation option is most advantageous for the company - company installed, or supplier installed? Which maintenance agreement is advantageous - one-time up-front agreement or paid quarterly? Based on your recommendations above, calculate the total cost of ownership based upon your proposalCost Elements Cost Measures Supplier quote: $6,500 per printer Equipment Costs: purchased up front or $100 per printer per month . Maintenance agreement year one: N/A . Maintenance agreement year two: Supplier quote: $200 per printer . Maintenance agreement years 3-5: Supplier quote: $250 per printer per year . One time up-up front maintenance agreement included in the purchase cost Supplier quote: Printer cost increases by $250 per printer. of the printer covering years 2-7: Acquisition Cost: One capital buyer and one purchasing . Purchasing costs: manager for one month. Purchasing Manager annual salary: $110,000 per year. Capital buyer $80,000 per year. . Installation costs: 30 maintenance technicians making $35 per hour for 30 hours each. . Fringe benefit costs: Estimated at base 22% of base salary 1 PO @ $150, 12 invoices @ $40 each . Administration Extended service agreements will be invoiced quarterly. Usage Costs: . Installation if done by supplier: $25,000 flat fee. End of Life: . Salvage Value $8 per printer