Styies Problem 1 Instructions (20 Points; four (4) points each) For Problem 1, use the Excel workbook named "Midterm_CheckPt_Problems_v2" found on BlackBoard. Use the worksheet ply named "Problem 1" to develop a model that could be used to address the following scenario. Scenario: Saint Joseph's School has 1,200 students. Each currently pays $8,000 per year to attend St. Joseph. The school receives an appropriation from a church in order to sustain its activities. The budget is projected to be $15 million and the church appropriation may be as high as $4.8 million. In anticipation that in the upcoming year the funds to cover the costs/expenses in the budget may be difficult to attain. One board member thinks that it may be possible that tuition can be tiered with children who are members at St. Joe's (currently 960) paying less than non-church member students. Another board member thinks that it would be hard to increase non-church members' tuition, so she thinks the school should seek to recruit more students. Hints: 1. Develop a model that has two sections-Incoming funds and Budget (Costs/Expenses); 2. Calculate the Over Budget or Shortfall, 3. Include separate items for member and non-church member information and calculations-don't "lump" both of them together. Develop a model in Excel that will help you answer the following questions. Develop your model in the Excel ply called Problem 1. After you develop the model, answer the questions below (using the space given in this document. ** 1. How much will the school be over budget (surplus) or have a shortfall for the next year if the information given above is used? 2. Sensing resistance to the idea of raising tuition from members of St. Joseph's Church, a board member suggested that the 960 children of church members could pay $8,000 as usual. Children of non-church members should pay more. What would non-church member tuition per year be if St. Joseph's wanted to continue to plan for a breakeven if it has a budget of $15 million? 3. Another board member believes that if church members pay $8,000 in tuition, the most St. Joseph's increase non-church member tuition is $1,000 per year. She suggests that another solution might be to cap non-church member tuition at $9,000 and attempt to recruit more non-church member students to make up the maintain breakeven. Under this plan, how many new non-church member students will need to be can recruited? 4. Suppose the tuition was increased to $9,000 for both types of students, how many church member students would be needed to have an overall budget (surplus) of $100,000? 5. With no changes to the original values, suppose the board could lobby the church to increase its appropriation, how much would the church need to appropriate to breakeven? Page 1 of 6 Styies Problem 1 Instructions (20 Points; four (4) points each) For Problem 1, use the Excel workbook named "Midterm_CheckPt_Problems_v2" found on BlackBoard. Use the worksheet ply named "Problem 1" to develop a model that could be used to address the following scenario. Scenario: Saint Joseph's School has 1,200 students. Each currently pays $8,000 per year to attend St. Joseph. The school receives an appropriation from a church in order to sustain its activities. The budget is projected to be $15 million and the church appropriation may be as high as $4.8 million. In anticipation that in the upcoming year the funds to cover the costs/expenses in the budget may be difficult to attain. One board member thinks that it may be possible that tuition can be tiered with children who are members at St. Joe's (currently 960) paying less than non-church member students. Another board member thinks that it would be hard to increase non-church members' tuition, so she thinks the school should seek to recruit more students. Hints: 1. Develop a model that has two sections-Incoming funds and Budget (Costs/Expenses); 2. Calculate the Over Budget or Shortfall, 3. Include separate items for member and non-church member information and calculations-don't "lump" both of them together. Develop a model in Excel that will help you answer the following questions. Develop your model in the Excel ply called Problem 1. After you develop the model, answer the questions below (using the space given in this document. ** 1. How much will the school be over budget (surplus) or have a shortfall for the next year if the information given above is used? 2. Sensing resistance to the idea of raising tuition from members of St. Joseph's Church, a board member suggested that the 960 children of church members could pay $8,000 as usual. Children of non-church members should pay more. What would non-church member tuition per year be if St. Joseph's wanted to continue to plan for a breakeven if it has a budget of $15 million? 3. Another board member believes that if church members pay $8,000 in tuition, the most St. Joseph's increase non-church member tuition is $1,000 per year. She suggests that another solution might be to cap non-church member tuition at $9,000 and attempt to recruit more non-church member students to make up the maintain breakeven. Under this plan, how many new non-church member students will need to be can recruited? 4. Suppose the tuition was increased to $9,000 for both types of students, how many church member students would be needed to have an overall budget (surplus) of $100,000? 5. With no changes to the original values, suppose the board could lobby the church to increase its appropriation, how much would the church need to appropriate to breakeven? Page 1 of 6