Budget Template For Manchester Auditoriums Use
Templatecash Budget Template For Manchester Auditoriumsuse This Templa
Template cash budget template for Manchester Auditoriums Use this template for the case study to prepare the cash budget. I put some fake numbers in just so you can see how the data flows. The blue cells are unprotected. To unprotect this sheet just click on unprotect sheet in the review tab ($000's) Qtr 1 Qtr 2 Qtr 3 Qtr 4 Beginning cash balance Minimum cash balance desired Available cash balance Cash receipts & disbursements: Collections from customers 4,,,,500 Payments for supplies (1,,,,300) Selling and Admin () Payments for payroll (1,,,,500) Capital spending (1,,,,000) Utilities spending () Payment of mortgage principal () Payment of mortgage interest () Beginning accrued Interest on loan ( Excess (deficiency) of cash (,415 Financing: Borrowing (at beginning of quarter) Repayment incl. interest (at end of quarter) - ) Ending cash balance ,514 Beginning working capital loan Additional borrowing ) Ending working capital loan Accrued interest @ 0.
Ending working capital loan plus accrued interest Interest rate on working capital loan 5% Sheet3 Case Study Notes - Comprehensive Cash Budget Every budget is built on a set of assumptions. This case involves applying the given assumptions to derive the budget for the upcoming year. Oftentimes a budget is based on the previous year with increases/decreases factored into the budget assumptions for the coming year. The numbers in the case are expressed in thousands. When you see a figure such as the minimum cash balance is $250k. $250k means $250,000.
I provided a template for you to use to prepare the cash budget. I put some fake numbers in there so that you can see how the information flows. Please put the figures into the spreadsheet in thousands. For example, put the minimum cash balance into the spreadsheet as 250. You can copy and paste the cash budget into your Word document as you prepare the case.
Regarding supplies: you need to solve for how much the company needs to purchase for the year. You need to use the following formula to calculate the supplies to be purchased for the year: Ending inventory of supplies Plus Supplies Expense Less Beginning inventory Equals Supplies to be purchased You are given the beginning and ending supplies inventory in the case. You are also given the calculation on how to calculate the budgeted supplies expense. If you plug these three numbers into the above equation you can solve for the amount that needs to be purchased. Remember the supplies expense goes on your income statement and the payment of the supplies purchased will go on your cash budget.
Regarding collecting the sales over three months. Try setting up a schedule like this to calculate the money to be collected each quarter: Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Nov sales 65% Dec sales 25% 65% Jan sales 10% 25% 65% Feb sales 10% 25% 65% Mar sales 10% 25% 65% Apr sales 10% 25% 65% etc.... Regarding the current mortgage payable on the balance sheet: this balance represents the principal amount to be paid within the next 12 months. Assume the principal amount is paid evenly throughout the year. The balance sheet will always reflect the next 12 months as a current liability until the mortgage is paid off.
ACC500 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 Manchester Auditoriums Inc Balance Sheet Income Statement As of December 31, 2015 Year ended December 31, 2015 (in thousands) (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 accounts payable: 628 related to supplies, 37 related to utilities 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, Budgeted Sales Cash Collections Jan 2,000 On sales from last year and Q1, Q2 of 2016 Feb 2, % current month of the sale Mar 2, % next month of the sale Apr 2, % 2 months from the month of sale May 2,620 Jun 2,470 On sales for the 2nd half of 2016 Jul 1, % current month of the sale Aug 1, % next month of the sale Sep 2, % 2 months from the month of sale Oct 2,130 Nov 2,510 Projected AR @ Dec 2016 is $3,943k Dec 3,360 Total 28,660 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 Southern New Hampshire University – ACC500 Comprehensive Cash BudgetCase Study Page 3 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.