Required: Objectives: To perform CVP analysis with multiple cost drivers To practice using the Goal Seek and Salver functions in Excel Due Date: Thursday, November 14 by 11:59PM Grading Criteria: Point allocation Criteria Correct analysis of casts given in case Correct use of formulas & cell references & general formatting Correct set up and use of Goal Seek Correct set up and use of Solver Total 1. Prepare a spreadsheet to budget income for the club. Your spreadsheet should have three sections. Section 1 should be titled Data and should contain the numbers given in the case. Joe wants to play with the number of players per team, the number of teams, and the fee charged for each player; so the spreadsheet should be set up to allow these three amounts to vary. This is Section 2 of your spreadsheet, title it Variables. Section should contain the schedule calculating forecasted income, title it budget. When constructing your budget, create a schedule that shows the detailed numbers for expenses with subtotals organized by cost driver (club-level, team-level, and player-level costs). Your format should be understandable and useful but does not need to conform to either the traditional or contribution format. The budget should look like a professional product. For a first cut of the budget, assume 6 teams and 12 players per team. Use the goal seek function (under the Data tab / What if Analysis) to determine the minimum fee needed per player to cover all expenses at this level. Rename the tab Goal Seek 10 30 points Scenario Joe Spirito was recently hired as the Director of a regional club baseball organization. Part of his responsibility was to keep the club financially solvent, so one of her initial efforts was to prepare estimates of upcoming costs. The sum of the fees paid by each team member needs to be enough to caver club costs, but not so high that it would preclude membership for interested teenagers. Joe needed to make a decision about the fee charged to each player for the upcoming season as well as decide how many teams would be allowed and how many players would make up each team. Player fees cover all costs other than travel for the team. The largest expense is the gym rental of $13,500 for the season. In addition, each player receives a uniform, $110 cast, and the club will pay dues of $50 per player to the state club baseball association. The state association also charges the club a contract fee of $835 per team for registration. Each team will have one head coach and one assistant coach who are paid $3,500 and $2,000, respectively. The club also covers the $50 state registration for each coach, and will purchase each coach a polo shirt with the club logo, costing $35 apiece. The director supervises all club activities, and will be paid $4,500 per season. The team makes some use of parents as support for the coaches. Background checks for all coaches are budgeted at $45 per coach Joe estimated that background checks and registration for parents would amount to $250 per team. A trainer is hired every season and paid $450 to provide a one-time group training session for all coaches. Further, the club spends $1,200 every season to replace broken or outdated equipment. Other general administrative costs each season include $650 in miscellaneous expenses, 5325 in taxes, and $190 for background checks not associated with a particular team, and $900 for insurance Required: Objectives: To perform CVP analysis with multiple cost drivers To practice using the Goal Seek and Salver functions in Excel Due Date: Thursday, November 14 by 11:59PM Grading Criteria: Point allocation Criteria Correct analysis of casts given in case Correct use of formulas & cell references & general formatting Correct set up and use of Goal Seek Correct set up and use of Solver Total 1. Prepare a spreadsheet to budget income for the club. Your spreadsheet should have three sections. Section 1 should be titled Data and should contain the numbers given in the case. Joe wants to play with the number of players per team, the number of teams, and the fee charged for each player; so the spreadsheet should be set up to allow these three amounts to vary. This is Section 2 of your spreadsheet, title it Variables. Section should contain the schedule calculating forecasted income, title it budget. When constructing your budget, create a schedule that shows the detailed numbers for expenses with subtotals organized by cost driver (club-level, team-level, and player-level costs). Your format should be understandable and useful but does not need to conform to either the traditional or contribution format. The budget should look like a professional product. For a first cut of the budget, assume 6 teams and 12 players per team. Use the goal seek function (under the Data tab / What if Analysis) to determine the minimum fee needed per player to cover all expenses at this level. Rename the tab Goal Seek 10 30 points Scenario Joe Spirito was recently hired as the Director of a regional club baseball organization. Part of his responsibility was to keep the club financially solvent, so one of her initial efforts was to prepare estimates of upcoming costs. The sum of the fees paid by each team member needs to be enough to caver club costs, but not so high that it would preclude membership for interested teenagers. Joe needed to make a decision about the fee charged to each player for the upcoming season as well as decide how many teams would be allowed and how many players would make up each team. Player fees cover all costs other than travel for the team. The largest expense is the gym rental of $13,500 for the season. In addition, each player receives a uniform, $110 cast, and the club will pay dues of $50 per player to the state club baseball association. The state association also charges the club a contract fee of $835 per team for registration. Each team will have one head coach and one assistant coach who are paid $3,500 and $2,000, respectively. The club also covers the $50 state registration for each coach, and will purchase each coach a polo shirt with the club logo, costing $35 apiece. The director supervises all club activities, and will be paid $4,500 per season. The team makes some use of parents as support for the coaches. Background checks for all coaches are budgeted at $45 per coach Joe estimated that background checks and registration for parents would amount to $250 per team. A trainer is hired every season and paid $450 to provide a one-time group training session for all coaches. Further, the club spends $1,200 every season to replace broken or outdated equipment. Other general administrative costs each season include $650 in miscellaneous expenses, 5325 in taxes, and $190 for background checks not associated with a particular team, and $900 for insurance