Complete the Excel Spreadsheet: Remaining to be done are some subtotals, totals, and then transferring the totals to the master cash budget worksheet in order to complete the cash budget.

TOTAL ITEM/MONTH Tuition Athletic Fees February 63,600 500 300 270,800 2,000 1,200 17,000 Late Fees Telethon Income Foundation Grant State Grant Registration Fees Club Donations Pre-and Post-Care Store Certificate Sales $ $ $ $ $ $ $ $ $ $ $ $ PROJECTED CASH RECEIPTS March April $ 80,000 $ 63,600 $ $ 500 $ 500 $ $ 300 $ 300 $ $ $ $ $ $ $ $ $ $ $ 10,000 $ $ $ 2,000 $ 2,000 $ $ 1,900 $ 1,900 $ $ $ $ $ $ $ May 63,600 $ 500 $ 300 $ 17,000 $ $ $ $ 2,000 $ 1,900 $ $ 30,000 2,000 1,900 40,000 8,000 7,600 Other $ TOTAL 98,300 PROJECTED CASH DISBURSEMENTS March April February May TOTAL $ $ $ $ 72,000 $ $ 700 $ 72,700 6,250 $ 72,000 $ 2,656 $ 700 $ 72,000 $ $ 700 $ 72,000 $ 5,000 $ 700 $ 288,000 7,656 2,800 $ 6,250 $ 6,250 $ 6,250 $ 25,000 $ 750 350 $ 2,987 1,482 7,501 600 $ 1,000 100 $ $ ITEM/MONTH PAYROLL EXPENSES Faculty and Staff Substitute Teachers Retirement Withholding SUBTOTAL PAYROLL EXP. Payment to Church OTHER EXPENSES Copier PSI & IN Gas Misc. Repair and Maintenance Fundraising Expenses Office Supplies Postage Athletic Phone Supplies Yearbooks and Ribbons Sports Banquet Testing Evaluation SUBTOTAL OTHER EXP. Store Certificate Remitt. TOTAL REG. EXP. Cash Expenditures TOTAL CASH DISBURSE. $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 500 $ 220 $ 500 $ 300 $ $ 1,600 $ 300 1,000 $ 400 $ 3,000 $ $ $ $ 7,820 $ 86,770 987 $ 562 $ 5,001 $ 100 $ 6,625 $ 5,560 $ 616 $ 1,800 $ 272 $ 1,080 $ $ 2,400 750 $ 350 $ 1,000 $ 100 $ $ 3,000 $ 450 $ 1,200 $ 300 $ 2,250 $ $ $ 1,800 3,000 $ 450 $ 1,200 $ 300 $ 2,250 $ $ 2,400 $ $ 6,625 13,160 1,816 5,200 1,272 8,580 8,500 4,800 1,800 8,500 $ $ 43,000 $ 43,000 $ $ $ $ 86,770 May TOTAL $ $ ITEM/MONTH BEGINNING CASH Cash Receipts Cash Disburse. + NET CASH FLOW = ENDING CASH - Minimum Cash Bal. = CASH SURPLUS/SHORT. PROJECTED CASH BUDGET February March April 5,000 98,300 86,770 11,530 16,530 25,000 $ 25,000 $ 25,000 $ (8,470) $ $ $ $ 25,000 $ 25,000 CASH BUDGET CONSTRUCTION First, complete the boxes that are left blank on the cash receipt template at the bottom of the next page. (If you are working from the Excel file, click on left tab in the Excel file.) You will see the specific locations of those boxes that you need to fill in listed underneath the table, near the bottom of that template/worksheet. Second, fill in the blank items in the cash disbursements worksheet (if working from the Excel file, click on the middle tab to go to that worksheet). Finally, go to the cash budget template (rightmost tab if using the worksheet file), and fill in the numbers or formulas there. Near the top, you will mainly be entering copy and paste numbers (or enter a formula and copy it across the remainder of the row if using the computer spreadsheet) to pull numbers from your now-completed cash receipts and cash disbursements worksheets. [If using the computer spreadsheet, begin your formula by pressing = (the equals key) and then you can click on the appropriate tab (e.g., cash receipts tab) and on the appropriate totals cell within that worksheet (you will see the referenced cell address show up in the formula bar near the top of the Excel screen once you do so).] Do the same for other cash receipts items in your cash budget template. The first total has been completed for you. (In the computer spreadsheet as well, the first total has been completed, so you can view that cell formula to see how the formulas look once complete.) 386 Now work downward in your cash budget by summing columns to calculate each month's net cash flow. Then, as shown in the template in Chapter 8, add this to beginning cash to get ending cash. Subtract the "minimum cash required" the same amount each month to get the adjusted cash position. If that number is negative, this implies a shortfall of cash, and if the organization cannot increase revenues or decrease expenses, this amount would be total dollar amount of the credit line "drawdown" (credit line balance borrowed). If that number is positive, a cash surplus, this amount would presumably be invested in a savings account, interest-bearing securities, or a money market mutual fund. Note that the number shown in each month's column, for either a shortfall or a surplus, is cumulative. For two consecutive surpluses, say $40,000 one month and $50,000 the next month, this implies that the organization has invested an additional $10,000 in short-term investments or its savings account. CASH BUDGET a. c. ANALYSIS For each of the questions in (a) through (d), write one short paragraph to answer the question: What is the largest surplus cash amount, if any, over the four months, and in what month does it occur? b. What is the largest shortage cash amount, if any, over the four months, and in what month does it occur? If there is a cash surplus in one or more months (refer to your answer in (a)), what could Tri-City Academy do with the surplus (after reviewing pertinent sections of Chapters 7 and 8, give some recommendations for how to utilize or deploy those funds)? If there is no month with a surplus, just answer "not applicable" for part (c). d. If there is a shortage in one or more months (refer to your answer in (b)), what could Tri-City Academy do to deal with the shortfall (after reviewing Exhibit 3.3 in Chapter 3 as well as pertinent sections of Chapters 7 and 8, give some recommen- dations for how to obtain or free up the needed funds, bearing in mind that your recommendations have to be done in the middle of a school year and must have an effect on cash inflows or cash outflows within the next several months)? If there is no month with a shortage, just answer "not applicable" for part (d). Apr May TOTAL 270800 500 63600 500 300 17000 1200 17000 Projected Cash Receipts for Tri-City Academy (Feb-May 2020) Month Item Feb Mar Tuition 63600 80000 63600 Athletic Fees 500 500 Late Fees 300 300 300 Telethon Income 0 0 Foundation Grant 0 0 State Grant 0 0 Registration Fees 30000 10000 0 Club Donations 2000 2000 2000 Pre- & Post-Care 1900 1900 1900 Store Certificate Sales 0 0 Other 0 0 TOTAL: 98300 0 0 ... 0 0 0 2000 1900 40000 8000 7600 0 0 0 TOTAL 25,000 220 Projected Cash Disbursements for Tri-City Academy (Feb-May 2020) Month Feb Mar Item Apr May Payroll Expense: Faculty $72,000 $72,000 $72,000 $72,000 & Staff Substitute Teachers 0 2,656 0 5,000 Retiremt. Withholding 700 700 700 700 Subtotal: Payroll & 72,700 Other Empl. Exp. Payment to Church 6,250 6,250 6,250 6,250 Other Expenses: Copier 500 987 750 750 PSI & IN Gas 562 350 350 Misc. 500 5,001 1,000 1,000 Repair & Mainten. 300 100 100 100 Fundraising Expenses 6,625 Office Supplies 1,600 5,560 3,000 3,000 Postage 300 616 450 450 Athletic 1,000 1,800 1,200 1,200 Phone 400 272 300 300 Supplies 3,000 1,080 2,250 2,250 Yearbooks & Ribbons 8,500 Sports Banquet 2,400 2,400 Testing Evaluation 1,800 Total Other Exps. 7,820 Store Certificate 0 0 0 Remittances TOTAL REGULAR 86,770 EXPENSES: Capital Expenditures: 0 43,000 0 0 TOTAL CASH DISBURSEMENTS: 86,770 43,000 Projected Cash Budget (Feb-May 2020) Month Item Feb Mar Apr May TOTAL BEGINNING CASH $5,000 CASH RECEIPTS 98,300