Reference no: EM131231154

Case Study- Comprehensive Cash Budget

Manchester Auditoriums Inc. provides the venue for many performers and talent companies touring the Southern New Hampshire region. Kathleen Johnson, treasurer of Manchester Auditoriums, needs to prepare a loan request to the Granite Cooperative Bank to meet the cash needs for the upcoming year which begins on January 1, 2016. Manchester Auditoriums has become a premier venue in the area and has grown considerably in the last few years. Below are the latest balance sheet and income statement for the year that just ended on December 31, 2015.

Manchester Auditoriums Inc Balance Sheet As of December 31, 2015 (in thousands)		Manchester Auditoriums Inc Income Statement Year ended December 31, 2015 (in thousands)
Assets		Revenues	$ 23,250
Cash	$ 350
Accounts receivable	3,930	Expenses
Supplies inventory	700	Salaries and wages	11,160
Unexpired insurance	36	Depreciation	1,100
Total current assets	5,016	Insurance expense	12
Net fixed assets	8,700	Supplies expense	5,120
Total assets	$ 13,716	Selling expense	1,805
		Administrative expense	1,900
Liabilities and Equity		Utilities expense	444
Equity Line of credit	$ 180	Interest expense	261
Accounts payable*	665	Total expenses	21,802
Accrued payroll	684
Accrued expenses (S&A)	309	Pretax income	$ 1,448
Accrued Interest	3
Mortgage payable, current	500	Return on sales	6.2%
Total current liabilities	2,340
Mortgage payable, long-term	4,500
Stockholders' equity	6,876
Total liabilities and equity	$ 13,716

Johnson is asking the bank to extend the existing line of credit to $1 million to help with the cash flow during the seasonal demands of the business and for the expansion of the business. The bank is requiring Manchester to maintain a minimum cash balance of $250k. Granite will charge interest at 6% per annum of the outstanding loan and accrued interest balance at the end of the quarter to be paid next quarter when cash becomes available; however, the accrued interest at the end of December 2015 must be paid in the first quarter of 2016. In the past, Manchester has not had any problem meeting the requirements of the existing line of credit. However, with the planned capital expenditures needed for expansion and the growing business needs, the loan manager has expressed concern with Johnson over the situation. The loan manager has asked for a quarterly cash budget and projected balanced sheet and income statement for 2016 to justify the need for the additional line of credit and to ensure that the company will be in compliance with the loan requirements.

Kathleen Johnson has put together the following information for the upcoming year (all numbers are in thousands):

2015 Actual Sales	$
Nov	2,100
Dec	2,850
2016 Budgeted Sales
Jan	2,000
Feb	2,300
Mar	2,410
Apr	2,910
May	2,620
Jun	2,470
Jul	1,970
Aug	1,920
Sep	2,060
Oct	2,130
Nov	2,510
Dec	3,360
Total	28,660

Cash Collections
On sales from last year and Q1, Q2 of 2016
10%   current month of the sale
25%   next month of the sale
65%   2 months from the month of sale

On sales for the 2nd half of 2016
20%   current month of the sale
30%   next month of the sale
50%   2 months from the month of sale

Projected AR @ Dec 2016 is $3,943k

All other budget assumptions:

Supplies:
- same percentage of sales as 2015
- purchased in the ratio of budgeted sales, paid a month later
- ending balance in supplies is projected to be $650k

Salaries & Wages:
- 48% of budgeted sales, paid twice a month on the 1st and 15th
- December 2015 accrued wages represents one-half of December wages

Utilities:
- 5% increase over 2015. Spread evenly each month, paid a month later

Depreciation: - $1,240k spread evenly each quarter

Selling & Admin:
- 3% increase over 2015. Spread evenly each month, paid a month later
- In the fourth quarter, additional $50k per month of selling expense

Expired Insurance: - $12k

Interest Expense: - obtain figures from cash budget worksheet

Capital Spending: - Q1 $1,400k; Q2 $350k; Q3 $500k; Q4 $500k

Mortgage:
- the current payable is paid evenly throughout the year
- interest is 5% per annum on the beginning total mortgage balance for the quarter

Required:

1. Prepare a cash budget by quarter for 2016 using the provided template. Will Manchester keep their projected borrowing needs in line with the $1 million line of credit they are asking the bank for?

2. As we see in the budgeted assumptions, Manchester wants to improve its cash flow by concentrating on collecting receivables sooner in the second half of 2016. What else can Manchester do to improve its cash flow?

3. Prepare a projected income statement and balance sheet for 2016.

4. Manchester's goal is to have return on sales of 8% in 2016. Based on the projected income statement calculated for 2016 will Manchester achieve this goal? What are some things Manchester can do to improve its return on sales?

5. Based on the cash budget and projected financial statements, do you recommend that the company keep growing and spend money on capital expenditures? Why?

Prepare your response in accordance with the grading rubric for a short paper/case study, and please show the detail of your calculations used to arrive at your answers. Prepare one Word document with both the narrative and schedules included. I recommend preparing the schedules/calculations in Excel where necessary and pasting them into the body of the paper.

Attachment:- Cash_Budget_Template_Manchester_Auditoriums.xlsx