See attach word document and excel file.
Wilma Brown, treasurer of Columbia Civic Theater (CCT), was preparing a loan request to the Northeast National Bank in December 2004. The loan was necessary to meet the cash needs of the theater for year 2005. In a few short years, the CCT had established itself as a premier theater. In addition to is regular subscription series, it started a series for new playwrights and offered a very popular holiday production. In fact, the holiday production was the most financially successful of the theater's activities, providing a base to support innovative productions that were artistically important to the theater but did not usually succeed financially.
In total, the theater had done well financially, as shown in Example 1 and 2. Its profitable operations had enabled it to build its own building and generally acquire a large number of assets. It had at least broken even every year since its incorporation, and management anticipates continued profitable operations. The Corporate Community for the Arts in Columbia and several private foundations had made many grants to the theater, and such grants are expected to continue. Most recently, the largest bank in town had agreed to sponsor the production of a new play by a local playwright. The theater's director of development, Richard Talman, expected such corporate sponsorships to increase in the future.
To provide facilities for the theater's anticipated growth, CCT began work on an addition to its building two years ago. The new facilities are intended primarily to support the experimental theater offerings that were becoming more numerous. The capital expansion was to be completed in 2005; all that remained was acquisition and installation of lighting, sound equipment, and other new equipment to be purchased in 2005.
Columbia Civic Theater had borrowed working capital from Northeast National Bank for the past several years. To qualify for the loans, the theater had to agree to
1. Completely pay off the loan for one month during the course of the year.
2. Maintain cash and accounts receivable balances equal to (or greater than) 120% of the loan.
3. Maintain a compensating cash balance of $200,000 at all times.
In the past, the theater has had no problems meeting these requirements. However, in 2004 the theater had been unable to reduce the loan to zero for an entire month. Although Northeast continued to extend the needed credit, the loan manager expressed concern over the situation. She asked for a quarterly cash budget to justify the financing needed for 2005. Ms. Brown began to assemble the data needed to prepare such a budget.
Columbia Civic Theater
Balance Sheets as of December 31 (In thousands of dollars)
2002 2003 2004
Cash $2,688 $229 $208
Accounts receivable 2,942 3,372 4,440
Supplies inventory 700 700 500
Total current assets $6,330 $4,301 $5,148
Plant and equipment 2,643 4,838 5,809
Total assets $8,973 $9,139 $10,957
Liabilities and Equities
Bank loan $0 $0 $1,620*
Accounts payable 420 720 780
Accrued payroll expenses 472 583 646
Mortgage, current 250 250 250
Total current liabilities $1,142 $1,553 $3,296
Other payables 270
Mortgage payable, long-term 3,750 3,500 3,250
Net assetsŧ 3,811 4,086 4,411
Total liabilities and equities $8,973 $9,139 $10,957
* Includes $32 thousand of accrued interest.
ŧ The "Net assets" account for a nonprofit organization is similar to "Stockholders' equity" for a corporation.
Columbia Civic Theater
Income Statement for the Year Ended December 31 (in thousands of dollars)
2002 2003 2004
Ticket sales $3,303 $4,060 $5,263
Contributions 1,041 1,412 1,702
Grant and other revenues 1,202 1,361 1,874
Total revenues $5,546 $6,833 $8,839
Production $4,071 $4,805 $6,307
Operations 271 332 473
Public relations and
community development 1,082 1,421 1,734
Total expenses $5,424 $6,558 $8,514
Excess of revenues over expenses $122 $275 $325
*Expenses include depreciation of $355, $370, and $470 and general and administrative expenses of $1,549, $1,688, and $2,142 in the years 2002, 2003, and 2004, respectively.
CCT received revenue from three main sources: ticket sales, contributions, and grants. Ms. Brown formed Exhibit 1-1 to calculate the accounts receivable balance for each of these sources for 2005. She assumed that CCT would continue its normal practices for collecting pledges and grant revenues.
Most expenses were constant from month to month. An exception was supplies, which were purchased twice a year in December and June. In 2005, CCT expects to purchase $200,000 of supplies in June and $700,000 in December on terms of net 30 days. The supplies inventory at the end of December was expected to be $600,000. Depreciation expense of $500,000 was planned for 2005, and other expenses were expected to run at a steady rate of $710,000 a month throughout the year, of which $700,000 was payroll costs. Salaries and wages were paid on the Monday of the first week following the end of the month. The other $10,000 of other expenses were paid as incurred.
The major portion of the new equipment to be installed in 2005 was to be delivered in September; payments totaling $400,000 would be made in four equal monthly installments beginning in September. In addition, small equipment purchases are expected to run $20,000 per month throughout the year. They will be paid for on delivery.
Ticket Sales Contributions Grants
End of End of End of
Quarter Quarter Quarter
Revenues Receivables Revenues Receivables Revenues Receivables
First Quarter $852 $2,795 $75 $794 $132 $1,027
Second Quarter 1,584 3,100 363 888 448 1,130
Third Quarter 2,617 3,407 1,203 1,083 1,296 1,240
Fourth Quarter 1,519 3,683 442 1,170 528 1,342
Columbia Civic Theater
Estimated Quarterly Revenues and End of Quarter Receivables for the Year Ended December 31, 2005
(in thousands of dollars)
In late 2002 CCT had borrowed $4 million (classified as a mortgage payable) from Farmers' Life Insurance Company. The theater is repaying the loan over 16 years, in equal principal payments in June and December of each year. Interest at 5% annually is also paid on the unpaid balance on each of these dates. Total interest payments for 2005, according to Ms. Brown's calculations, would be $172,000.
Interest on the working capital loan from Northeast was at an annual rate of 8%; payment for 2004's interest would be made on January 10, 2005, and that for 2005's would be made on January 10, 2006. Working capital loans are taken out on the first day of the quarter that funds are needed and they are repaid on the last day of the quarter when extra funds are generated. CCT had tried to keep a minimum cash balance of $200,000 at all times, even if loan requirements do not require it.
1. Compute the cash inflows and outflows for each quarter of 2005. What are CCT's loan requirements each quarter?
2. Prepare a projected Income Statement and Balance Sheet for CCT for 2005.
3. Prepare the projected Statement of Cash Flows for 2005.
4. What financing strategy would you recommend for CCT?
See attach Excel file...
The solution explains how to prepare a cash budget and an income statement