concerts because of the difficulty they have accessing the bandstand. City Band's trustees would like to accommodate both groups of musicians. They have gotten an estimate of $10,000 to make the bandstand accessible. You have lined up a 10-year, $500 per year grant from the State Office of Disabilities and a 5-year, $750 per year grant from the Federal Office of the Aging to help pay for the modifications to the bandstand. In addition, the local chapter of the Knights of Columbus has offered to donate $1,500 toward the project. New City has arranged financing for a potential loan that the City Band trustees can borrow from First United Bank, agreeing to repay the full amount plus interest at a 8 percent rate in 10 annual installments due by December 31st of each year. Part 01 When building spreadsheet models, it is good modeling practice to identify all of the data that you will be inputting into the model and isolate the data in a separate worksheet or at the top of the worksheet where you intend to build your analytical model. The input labels for the New City Band case are shown at the top of the Excel document. The first part of your assessment is to correctly identify and input all of the information needed to solve Parts 2 and 3 of this assessment. Part 02 Prepare an operating budget for City Band for the coming fiscal year assuming the band performs on each of its 20 scheduled concert dates and a flexible budget showing what would happen if the band could only perform on 80 percent of its scheduled concert dates. Part 03 Prepare a Summary (by year) Cash Flow budget for the ten years estimated to pay for this project. How much in total will the City Band trustees need to pay the bank (including interest and principal) each year and in total? Will the cash flow be sufficient to pay off the loan in time or will the trustees need to secure additional funding? UNIT 02 Assessment Directions: This Assessment is divided into three parts. Each part should be completed in Microsoft Excel Spreadsheet attached with this Assessment text. New City Band As the volunteer business manager for the New City Band (City Band), you are responsible for preparing the operating budget for the organization's upcoming summer concert season. Each year, City Band presents up to 20 weekend performances, depending on weather conditions. The concerts are free to the public, but the band hangs a pot from the bandstand and people leave small donations in it. On average, City Band gets $100 in donations at each of its performances. In addition to donations, New City pays the band $3,000 per season plus $125 for each performance. City Band also has a small endowment of $100,000 on which it expects to earn 3.5 percent in the coming fiscal year. City Band's trustees have decided to use that money to pay for operating expenses if they need to. City Band pays its conductor $3,000 for the summer season and has an insurance policy to protect it against any loss of equipment or damage to the bandstand. That policy costs the band $500 for the summer plus $25 per performance. New music costs the band $200 per year. Following Generally Accepted Accounting Principles, the band recognizes music acquisitions as expenses in the year the music is acquired. In addition, City Band pays music publishers an average of $40 per concert for the rights to perform certain pieces in its repertoire. The band has an average of 60 musicians at each of its performances. Each musician is paid $5 per performance. Lately, some of City Band's older musicians have been having difficulty climbing the stairs to get up to the bandstand. In addition, there are two disabled musicians who play at all of the band's rehearsals but are reluctant to play at the

concerts because of the difficulty they have accessing the bandstand. City Band's trustees would like to accommodate both groups of musicians. They have gotten an estimate of $10,000 to make the bandstand accessible. You have lined up a 10-year, $500 per year grant from the State Office of Disabilities and a 5-year, $750 per year grant from the Federal Office of the Aging to help pay for the modifications to the bandstand. In addition, the local chapter of the Knights of Columbus has offered to donate $1,500 toward the project. New City has arranged financing for a potential loan that the City Band trustees can borrow from First United Bank, agreeing to repay the full amount plus interest at a 8 percent rate in 10 annual installments due by December 31st of each year. Part 01 When building spreadsheet models, it is good modeling practice to identify all of the data that you will be inputting into the model and isolate the data in a separate worksheet or at the top of the worksheet where you intend to build your analytical model. The input labels for the New City Band case are shown at the top of the Excel document. The first part of your assessment is to correctly identify and input all of the information needed to solve Parts 2 and 3 of this assessment. Part 02 Prepare an operating budget for City Band for the coming fiscal year assuming the band performs on each of its 20 scheduled concert dates and a flexible budget showing what would happen if the band could only perform on 80 percent of its scheduled concert dates. Part 03 Prepare a Summary (by year) Cash Flow budget for the ten years estimated to pay for this project. How much in total will the City Band trustees need to pay the bank (including interest and principal) each year and in total? Will the cash flow be sufficient to pay off the loan in time or will the trustees need to secure additional funding? UNIT 02 Assessment Directions: This Assessment is divided into three parts. Each part should be completed in Microsoft Excel Spreadsheet attached with this Assessment text. New City Band As the volunteer business manager for the New City Band (City Band), you are responsible for preparing the operating budget for the organization's upcoming summer concert season. Each year, City Band presents up to 20 weekend performances, depending on weather conditions. The concerts are free to the public, but the band hangs a pot from the bandstand and people leave small donations in it. On average, City Band gets $100 in donations at each of its performances. In addition to donations, New City pays the band $3,000 per season plus $125 for each performance. City Band also has a small endowment of $100,000 on which it expects to earn 3.5 percent in the coming fiscal year. City Band's trustees have decided to use that money to pay for operating expenses if they need to. City Band pays its conductor $3,000 for the summer season and has an insurance policy to protect it against any loss of equipment or damage to the bandstand. That policy costs the band $500 for the summer plus $25 per performance. New music costs the band $200 per year. Following Generally Accepted Accounting Principles, the band recognizes music acquisitions as expenses in the year the music is acquired. In addition, City Band pays music publishers an average of $40 per concert for the rights to perform certain pieces in its repertoire. The band has an average of 60 musicians at each of its performances. Each musician is paid $5 per performance. Lately, some of City Band's older musicians have been having difficulty climbing the stairs to get up to the bandstand. In addition, there are two disabled musicians who play at all of the band's rehearsals but are reluctant to play at the

Essentials of Business Analytics (MindTap Course List)

2nd Edition

ISBN:9781305627734

Author:Jeffrey D. Camm, James J. Cochran, Michael J. Fry, Jeffrey W. Ohlmann, David R. Anderson

Publisher:Jeffrey D. Camm, James J. Cochran, Michael J. Fry, Jeffrey W. Ohlmann, David R. Anderson

Chapter11: Linear Optimization Models

Section: Chapter Questions

Problem 9P: The Westchester Chamber of Commerce periodically sponsors public service seminars and programs....

See similar textbooks

Part 02:
Base & Flexible Budget
	Base Budget	Flexible Budget	Fixed or Variable
Revenue
City Contributions
Fixed Annual Contribution			F
Per Concert Contributions			V
Public Contributions			V
Endowment Earnings			F
Total Revenue

Expenses
Conductors Stipend			F
Musicians Stipend			V
Insurance
Fixed Insurance Premium			F
Per-Concert Insurance Premium			V
Music Costs
Music Acquisitions			F
Performance Rights			V
Total Expenses

Surplus/(Deficit)