The Sawmill Spreadsheet was a relatively simple case to get you started on evaluation. Now, we will move on to a typical case study that you might be asked to prepare in a firm. As a business manager for your firm, Global Biotechnology, you have a new process for Biosynthetic Human Insulin (BHI) in development. Since you are in the position of business evaluation for Global, you will be assigned to "take a look" at this job and see if it's a good project. To evaluate the BHI Project, a spreadsheet analysis will be required. Since this project is just now coming to a point of evaluation in R&D, there is not much to go on. Note that you must distinguish between assumptions that you, the business evaluator, must make from those items of information that you would be required to obtain from others to create a defensible recommendation. Since time is of the essence, you will need to decide which items of information are more important than others. R&D has just issued their report on the new Biosynthetic Human Insulin process and plant. They have recommended that Global Biotechnology pursue this project immediately as it appears to have a high return. The preliminary flowsheet has been developed and a first pass capital cost estimate has been issued with the recommendation that Global spend $122 million on the new facility. As business manager you have been asked to evaluate this project and make a recommendation to senior management. Here is the key data (and the only data) in the research division report: Estimated Direct Fixed Capital-$122 million Estimated Initial working capital-$8 million Required Plant capacity at 100%-1500Kg/yr Estimated BHI unit production cost-$41/gram Estimated BHI selling price-$110/gram BHI project return -greater than 35% There is not much to go on here. You will have to start by making appropriate evaluation assumptions. Some important areas that drive evaluations are: 1) Project life 2) Tax life of the assets 3) Corporate tax rate 4) Inflation rate for costs/prices 5) Proportion of fixed cost/variable cost in $41/gram 6) Length of time to build the plant and spending curve (expenditures per year) Although there are many possible evaluation assumptions you can select. For your work to proceed, and to create a common base case, you will use these base case assumptions: 1) Project life-3 years for capital construction, and 15 years of operations for a total scenario of 18 years. 2) Tax life of the assets-composite average 10 years MACRS class. 3) Corporate tax rate-35%. 4) Inflation for all costs/prices-3%. 5) Proportion of fixed to variable cost-75% fixed, 25% variable. 6) Length of time to build-3 years, capex - 30%, 40%, 30%. 7) The sales rate, which is the plant capacity utilization in the initial operating years. You will use 50% of capacity year 1, 75% of capacity year 2 and 100% of capacity in year 3. The initial working capital is the cost of the initial charge of raw materials and work in process supplies, plus the funding of accounts receivable until cash receipts from sales are received. The $8 million is deducted from the first operating year after tax cashflow and is recovered at the end of the project life. Remember to build your spreadsheet with formulas for relevant variables so that if you change assumptions you can let the spreadsheet do the computational work. One last comment about these assumptions: In business, important decisions are made everyday with incorrect and incomplete information. The issue we face is how accurate does our information and resulting assumptions have to be to make correct decisions. Decisions MUST be made on a timely basis. If we wait for all the information to be known, then the decision is history before it is made. Also, we should always be evaluating our assumptions and be prepared to make appropriate corrections as they become necessary. Additional information about the Sawmill Spreadsheet Project, and the Major Capital Project (BHI) evaluation. The Sawmill Project is a very basic first pass evaluation that develops your skills in performing the basic evaluation calculations. It does not contain the density of business assumptions that are typical of a major capital project evaluation. The BHI project is the extension of the basic work that you did in Sawmill to a fully described major capital project evaluation with all the pertinent business assumptions that are characteristic of a decision process at the major capital level. To provide a path to your development of your own spreadsheet, the answer spreadsheet in .pdf form is provided. It shows the results of the base case calculation that is required to successfully complete the initial calculations for the project. You must develop your own spreadsheet that embeds the business assumptions in formulas that lead to the base case results shown in the .pdf. Pay particular attention to direct fixed capital, selling prices, revenues, and manufacturing costs as variables in your formulas. These are key inputs that are estimates of the future. These areas are usually tested by changing assumptions to see what the effect is on the measures of economic efficiency for the project. For purposes of the BHI Quiz next week (Week 9), you will be given different values for the business assumptions that you will enter into your spreadsheet and compute the results. This is just like a sensitivity case for deterministic risk assessment. Once you have computed the new results, you will select the answers for the quiz based on your new results from the given inputs. If your spreadsheet has been developed correctly, then you will have the proper answers for the quiz. please create spreadsheet