Data Mining for Business Decisions (BSAN 54000-001) Summer 2022 Midterm Exam Note: Please submit your excel sheets along with word documents of your answers. Also, on problem 2 please submit your decision tree. 1) Chipper Payroll Services knows the demand for its services during the current year is around 50,000 worker hours and with current operations covers all customer demand (i.e., Chipper's capacity currently is 50,000 worker-hours). Chipper is planning on a 5% growth rate each year. Chipper's current office space and staff will eventually outgrow demand. Expanding the office space and staff depends mostly on the hours that will be worked total in the facility. If we let x = total worker-hours, Chipper will incur a one-time cost of $15x to expand [i.e., if Chipper goes from 50,000 worker-hour capacity to 70,000 worker-hour capacity, it will incur a cost of $15 x (70,000 - 50,000) = $300,000]. Chipper needs to expand its current space and staff, since any new business will be lost to its competitors if it cannot accommodate the new customers. Each customer serviced incurs a variable cost of $3.00 per worker-hour. It also costs Chipper $6.00 per worker-hour of capacity per year (i.e., if Chipper has 70,000 worker hours total then Chipper incurs $6 x 70,0000 = $420,000 per year in costs). Chipper garners $25 per worker hour from its customers. 2) Construct an Excel spreadsheet model to determine what the projected revenue, costs, and potential profits would be over the next 10 years based on the current capacity, 50,000 worker-hours, an expansion to 70,000 worker-hours, and an expansion to 90,000 worker- hours. 15points James Bernard, head of leasing at New Braunfels Inc., has to decide whether to build a new state-of the-art processing facility. If the new facility works, the company could realize a profit of $200,000. If it fails, New Braunfels could lose $150,000. At this time, he estimates a 60% chance that the new process will fail. The other option is to build a pilot plant and then decide whether to build a company-wide facility. The pilot plant would cost $10,000 to build. James estimates a 50-50 chance that the pilot plant will work. If the pilot plant works, there is a 90% probability that the complete plant, if it is built, will also work. If the pilot plant does not work, there is only a 20% chance that the complete project (if it is constructed) will work. James faces a dilemma. Should he build the plant? Should he build the pilot project and then make a decision? Help James by analyzing this problem: Create a decision tree and please submit. What recommendation, based on expected value, would you give James? 15 Points PAGE 1 OF 2 Data Mining for Business Decisions (BSAN 54000-001) Summer 2022 Midterm Exam 3) Historical data indicate that a student's income for any month of school from work, parents, scholarships, and loans is consistent with the following probability distribution INCOME PROBABILITY $750 0.20 $950 0.36 $1150 0.30 $1350 0.14 Expenses for the same student are believed to be consistent with the following probability distribution: EXPENSE PROBABILITY $900 0.40 $1000 0.25 $1100 0.20 $1200 0.15 Assuming the student begins the school year with a balance of $1200, use Excel to simulate 12 months of activity and to predict the position of the student at the end of the year. Please explain your results. 20 Points