Information Technology Technological Institute (or IT Tech as it is more commonly known) is located in downtown Greensboro, North Carolina. The Institute provides computer training for companies in the central area of the State. Recently IT Tech has signed a fixed-payment contract to provide specialized computer training for a biotechnology company moving to the North Carolina Research Campus. The Campus is a 350-acre massive scientific and economic revitalization project that encompasses the former Pillowtex textile plant and the entire downtown area of Kannapolis. Specifically IT Tech has agreed to provide the biotechnology company with at least 30 trained IT specialists. Once enrolled in the training program, the students would agree to do their best to complete it, and would agree to work for the biotechnology company for at least two years if they did so.

The specialized IT training consists of both hardware and software instruction. The hardware training consists of instruction on the operation of the testing devices and computer equipment in use. The software training comprises instruction on both the systems programming and applications programming necessary to meet the needs of the biotechnology company. IT Tech estimates that the setup cost for the training is $35,000, while the variable cost is $2,250 per trainee. Based on past experience, IT Tech estimates that every student independently has a 15 percent chance of failing to master the training and will have to be dropped from the program. So, for example, if 40 students start training, then the expected number who pass will be 34 (85% of 40), but the actual number who pass will be a random variable with values from 0 to 40 (not all of which are equally likely). (Question: what is the probability distribution of the number of students who successfully complete the training?)

While IT Tech has classroom capacity to train up to 60 students, they are trying to decide how many students to enroll. They plan to enroll at least 30 students. Naturally they would hope that at least 30 of the students who started the training could complete it. Of course enrolling too many students might just incur unnecessary costs. If fewer than 30 students completed the training, IT Tech figured that they could always identify some of their best former students, lure them away from their current employers with a cash advance, and quickly provide them with the necessary specialized training to fulfill the contract. However, this would be an expensive undertaking, and would cost approximately $25,000 per student. Fortunately IT Tech expected that all of these former students could successfully complete the training.

Create an Excel model that simulates the training of one group of students. The model should have one input cell (the number of students trained) and one output cell (the total cost of the training, to include any secondary hiring). In building your Excel model you should make sure that if 40 students are enrolled and 29 pass, then the total cost is $150,000, and that if 40 students are enrolled and 31 pass, then the total cost is $125,000.