simulation

please use the instructions that I uploaded for you. you need them to know how to write your paper step by step. there are 2 separate sheet also, and don't for get to use the book to solve the case and please if you can try not to use information out of the book and if you see that you need to have sources put some sources at least 4 to 5 not more than 5 that if you share information out of the book. Required Text: Managerial Decision Modeling with Spreadsheets, 3rd edition; Balakrishnan, Render, and Stair; Prentice Hall (2013); ISBN # 978-0-13-611583-0 Simulation Case Study Rules: You may work with and discuss this case with your group members only. Turn in one group memo with supporting data. The Case: Mark and Judith McKnew own a business which manufactures kayaks. They are considering setting up a separate corporation to manufacture a motorized kayak: they intend to be first in the market to produce Jet-Kayaks. Before they can make the decision to enter this market, however, they want to assess it for profit potential over the next several years. After consulting with economists, market analysts, their own employees and sports enthusiasts, they are confident that the gross revenues for Jet-Kayaks will be around $6.5 million in 2015. They assume that a small percentage of the Jet-Kayaks will be damaged in transit, or some will be returned by dissatisfied customers shortly after the sales. These returns and allowances (R&A) are usually calculated as 2% of the gross revenues. Hence, the net revenues are simply the gross revenues minus the R&As. They believe the 2015 labor cost will be $995,100. The cost of materials, including shipping crates, should be $915,350 for 2015. Finally, the overhead costs (rent, lighting, heating and air conditioning, security, etc.) for 2015 should be $1,536,120. Thus the cost of goods sold is the sum of labor, material and overhead costs. The McKnews figure the Gross Profit as net revenues minus the cost of goods sold. In addition, they must consider the selling, general and administrative (SG&A) expenses. These expenses are more difficult to estimate, but the standard industry practice is to use 18% of the net revenues as the nominal percentage value for these expenses. Therefore, the McKnew’s profit before taxes is the gross profit minus the SG&A value. To calculate taxes, they multiply their profit before taxes times the tax rate, currently 30%. If the company is operating at a loss, however, no taxes would have to be paid. Finally, the McKnew’s net (after tax) profit is simply the difference between the profit before taxes and the actual taxes paid. To determine the numbers for 2015 though 2018, the McKnews assume that gross revenues, labor costs, material costs and overhead costs will increase over the years. Although the rates of increase for these items are difficult to estimate, The McKnews estimate that gross revenues will increase by 9% per year, labor costs will increase by 4% per year, material costs will increase by 6% per year, and overhead costs will increase by 3% per year. They further assume that the tax rate will not change from the 30% mark, and they assume that the SG&A value will remain at 18%. NOTE: Phases 1 and 2, discussed below, have already been created. The spreadsheet is posted on Blackboard. Phase One: Projecting Profits The basic layout of the spread sheet they create is shown in Figure 1 below. (Ignore the competition assumptions for now; we will consider it later.). Construct the spreadsheet and determine the values for the years 2015 through the year 2018. To find out if this is a good investment, find the present value of the investment over the four years (Use the NPV function considering the income stream of profit after tax for the four years. Use the current interest rate of 5% for this computation. At this point in the program, I realize that you may not know what net present value is. However, you should be able to use the Excel Help function to help you figure out how to use this tool. You are given check figures below so you can ensure your model is correct to this point. Ask if you have any questions) To avoid large values in the spread sheet, enter all dollar calculations in thousands. For example, enter labor costs as 995.10 and overhead costs as 1536.12. Figure 1. Phase 1 results. Phase Two: Bringing Competition into the Model With the spreadsheet complete, the McKnews are confident that entering the Jet-Kayak market would be very profitable. However, they have not considered one factor in the calculations: Competition. The current market leader and the company they should be most concerned about is Precision Kayak of Easley, South Carolina. If Precision enters this market, they will make a big difference in the gross revenues. If Precision enters the market, the McKnews believe they would still make money, but the estimate for gross revenues would have to be revised from $6.5 million to about $5.4 million for 2015. To account for the competition factor, you must revise the spreadsheet by adding a Competition Assumptions section. Cell F4 will contain either a 0 (no competition) or a 1 (Precision enters the market). Cells F5 and F6 provide the gross revenue estimates (in thousands of dollars) for the two possibilities Modify your spreadsheet to take these options into account. Use the If() function for the gross revenues for 2015 (Cell C12). If Precision does enter the market, not only would the McKnew’s 2015 gross revenues be lower, but their 2015 costs as well. See the table below: 2015 Costs No Competition Competition Labor $ 995,100 $ 859,170 Materials $ 915,350 $ 702,950 Overhead $ 1,536,120 $ 1,288,750 They believe their growth rate assumptions would stay the same whether Precision enters the market or not. They believe there is a 50/50 chance that Precision will enter this market. Add these possible values to your spreadsheet using the IF() function in the appropriate cells. Look at the NPV for the two scenarios: Precision does not enter the market, (NPV = $5,672,700 as shown in figure 1 above) or does enter (approximately $4,750,000). Phase Three: Bringing Uncertainty into the Model The McKnew’s Chief of Operations and Quantitative Specialist, Orestes Baez, plays a key role in providing them with estimates for the various revenues and costs. He is uneasy about the basic estimates for the growth rates. For example, although market research indicates that a 9% gross revenue increase per year is reasonable, Orestes knows that if this value is 7% for example, the profit values and the NPV would be quite different. Even more troublesome is the potential tax increase, which would have a significant impact. Orestes believes that the tax rate could vary around the expected 30% figure. Finally, he is uncomfortable with the industry’s standard estimate of 18% for the SG&A rate. He thinks this value could be higher or even lower. This problem is too complicated to solve using what-if analysis because seven assumed values could change. The growth rates for gross revenues, labor, materials, overhead costs, tax rate, SG&A percent, and whether or not Precision enters the market. Orestes believes that a Monte Carlo simulation would be a better approach. He believes that the behavior of these variables can be modeled as: Growth in gross revenues (%): Normally distributed, mean = 8.7, s.d. = 2.5 Growth in Labor costs (%): Normally distributed, mean = 3.50, s.d. = 1.9 Growth in Materials (%) Probability 4 .05 5 .15 6 .20 7 .20 8 .30 9 .10 Growth in Overhead (%) Probability 2 .15 3 .30 4 .35 5 .20 Tax Rate(%) Probability 28 .15 29 .25 30 .35 31 .25 SG&A (%) Probability 15 .10 16 .10 17 .10 18 .30 19 .20 20 .20 Use simulation to analyze the McKnew’s problem. Based on your results, what is the expected net profit for the years 2015 through 2018, and what is the expected NPV for this business venture? You should use 1000 replications. The McKnews agree that they would feel comfortable with this business venture if its NPV is at least $5 Million. What are the chances that this will be the case? (Hint: use the COUNTIF function to determine how many times the NPV is at least $5 Million.) Assignment: Write memo with supporting data to explain why you chose simulation to solve this problem, why the thousand replications were required, etc. The information we most want to have as your client is the information you should present to us. Do not confuse your clients with technical terms that they might not understand; our business is Kayaks, not computing. Make sure that you consider the NPV issue mentioned at the end of the case and that you include a recommendation for them. This memo should be professionally prepared. NOTE: On Blackboard you will find an Excel file containing complete Phase 1 and Phase 2 spreadsheets for this problem (i.e., Phases 1 and 2 have been done for you). You should start your analysis with these – don’t start from scratch! Your task is to incorporate Monte Carlo simulation to produce Phase 3, then clearly communicate your results to McKnew’s Kayaks. Below is the matrix that will be used to guide grading of the cases. Quan 2020 Case Study Guidelines Case studies are difficult for many students because they involve some uncertainty; that is, there is usually not a single correct answer or approach. This, however, is a very common situation in the “real world,” where your boss or colleagues may ask you to investigate a problem and come up with solutions, or you may, on your own initiative, choose to tackle an analysis. In many cases the problem itself is not even clearly or completely defined, and that definition becomes your first order of business. Cases are thus included in this course in order to give you practice with real-world type analysis problems. They involve a bit of role-playing; in most cases they involve a fictional customer that you are tasked with providing analysis and answers to specific questions. Keep this in mind – the quality of your work should be consistent with what you would provide to a real customer. The following list is based on problems that have been revealed from past student work on similar cases. You can help ensure better grades on your case studies if you review these notes before you begin work on each case, and as you near the end of your work. 1. Put yourself in your customer’s place – create an analysis that you, as a customer, would be pleased to receive. 2. Thoroughly read all of the case material, and note very carefully any specific questions! Believe it or not, a common mistake is to not answer the questions that are asked. As your customer, I will be very unhappy with your work if you do not answer the questions I am paying you to answer. 3. Before you even start your analysis, consider the organization of the report you will send to the customer. This can be very simple. Typically, it will be something like this: Introduction Analysis Conclusions Supporting Data (typically an Appendix) The introduction should outline the problem and briefly describe your approach. The analysis section should detail the quantitative methods you used to come up with your answers. In this section you should include key formulas, tables, and graphs that support your answers. In the conclusions section you should summarize the results of your analysis, and include answers to the specific questions asked. Finally, the appendix should include more details of the mathematics you employed. For example it might contain supporting tables or graphs, detailed calculations, or results of alternative methods that support your conclusions. 4. Every table and chart in your report (including the Appendix) should be numbered and labeled, so it can easily be referenced in your paper, or by the reader. A common mistake is to include a bunch of tables and charts in the appendix, but not label them, thus making them almost useless to the customer. 5. Don’t assume that your customer is an expert on the techniques you are using. For example, if you talk about forecasting methods, briefly explain them, and any special related terms you use. Your goal should be to create an analysis that is easy for the customer to follow, understand, and agree with! 6. Your analysis section should include key results (NUMBERS!) that support your conclusions. After all, this is a course about numerical methods for decision making – the outputs of those methods are numbers (for example, forecasted sales, simulated inventory, calculated production). Your results will not be very credible if you make statements like, “We believe you should increase your production by 10%.” They will be much more credible if you can say instead, “Based on the results of 1000 simulation runs, increasing production by 10% will result in an average revenue increase of $50,000 per quarter.” 7. You will work on cases in teams – typically of three or four students. Organize your project so that the responsibilities of each team member are clear. Make good use of the time provided in class to work on the cases, and use e-mail, etc. to make efficient use of your time outside of class. 8. EDIT, EDIT, EDIT. I often receive papers that include spelling errors, nonsense “sentences,” duplicated paragraphs, conclusions that are inconsistent with the analysis, questions not answered, etc. Remember – you are submitting your analysis to an imaginary customer (who happens to be me). Your customer will be inclined to dismiss your analysis (and never pay you again) if it is incomplete, unprofessional, hard to follow, or filled with obvious errors. Most of these issues can be caught and corrected if you thoroughly edit your paper before turning it in. It’s a good idea to select one member of your group to play the role of the customer, and thoroughly read and edit your report.