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 31^st 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.