Please see the attached cash management case.

Ms. Amanda Truly is the new chief financial officer of Mind and Body.

Cash Management Case Ms. Amanda Truly is the new chief financial officer of Mind and Body, Inc., which produces popular yoga and Pilates videos. Ms. Truly is concerned about cash flow management at the company and would like to get a better "feel" for the way cash flows are managed at Mind and Body, Inc. The CEO of the company, Mr. Lawrence Jackson, is worried about the state of the company's cash situation. Even though the company has produced positive net income consistently, the level of its short-term borrowing is worrying. Mr. Jackson would like Ms. Truly to construct a cash budget for next year so that they can devise a short-term financial policy that would best suit the company's cash flows. To accomplish this task, Mr. Jackson has provided Ms. Truly with the following disparate information, as well as the company's most recent Income Statement, Balance Sheet, and Cash Budget: Purchases from suppliers = 70% of predicted sales for next month Accounts Payable Period = 30 days Wages and other expenses = 20% of predicted sales Capital Expenditures in the form of a computer system purchase = $500,000 in June Long-term debt Interest expense = $50,000 Dividends = $30,000 per quarter Minimum cash balance = $200,000 Short-term cost of borrowing = 13% APR compounded monthly Long-term cost of borrowing = 10% APR compounded monthly Income taxes from last year's income will be paid monthly in this year Interest expense on accumulated short-term expense must be paid in the following month 50% of customers pay in the month of sales, 30% pay in the month after sales, and the rest pay two months after sales. Bad debt is estimated to be 2% if the customers have not made payment after 60 days. TABLE 1: Last Year's Income Statement Sales Cost of Goods Sold Wages and other expenses FNCE 371v3 $10,944,25 0 7,660,975 2,188,850 Assignment 1, Case 1 of 3 October 2014 Earnings before Depreciation, Interest, and Taxes Depreciation Earnings before Interest and Taxes Interest expense Taxable Income Taxes (36%) Net Income Dividends Additions to Retained Earnings TABLE 2: Last Year's Balance Sheet Cash Inventory Accounts Receivable Current Assets Net Fixed Assets Total Assets $200,000 140,000 792,080 1,132,080 9,004,814 10,136,89 4 1,094,425 100,000 994,425 603,760 390,665 140,640 250,025 120,000 130,025 Accounts Payable Notes Payable Current Liabilities 140,000 41,520 181,520 Long-term debt Common Stock Retained Earnings Total Owners' Equity 6,000,000 2,500,000 1,455,374 3,955,374 Total Liabilities & Owners' Equity 10,136,894 TABLE 3: CASH BUDGET Cash collections: Sales Month 0 Collections Month -1 Collections Month -2 Collections Total Collections Beginning Accounts Receivable Sales Cash Collections Ending Accounts Receivable January February March 185000 370000 740000 92500 185000 370000 540000 55500 111000 172872 352800 36260 805372 593300 517260 712872 185000 805372 452500 452500 370000 593300 222000 April May June 2035000 203500 407000 1017500 101750 203500 222000 610500 61050 72520 145040 398860 1312020 857290 663410 222000 444000 740000 2035000 517260 1312020 444000 1165500 1165500 203500 857290 508750 508750 407000 663410 244200 Cash disbursements: Beginning Accounts Payable Purchases Payment of Accounts Payable Ending Accounts Payable FNCE 371v3 January February March April May June 129500 259000 518000 1424500 142450 284900 259000 518000 1424500 142450 284900 466200 129500 259000 518000 1424500 142450 284900 259000 518000 1424500 142450 284900 466200 Assignment 1, Case 1 of 3 October 2014 Payment of Accounts Payable Wages and other expenses Taxes Capital exp ST interest expense LT interest expense Dividends Cash disbursements Cash Collections Cash Disbursements Net cash inflow Cash Budget: Beginning cash balance Net cash inflow Ending cash balance Minimum cash balance Surplus/deficit Short-term borrowing Repayment of ST debt Cumulative ST debt ST interest expense 129500 37000 20833 0 400 50000 0 237733 805372 237733 567639 259000 74000 20833 0 0 50000 0 403833 593300 403833 189467 518000 148000 20833 0 0 50000 30000 766833 517260 766833 -249573 1424500 407000 20833 0 0 50000 0 1902333 1312020 1902333 -590313 142450 40700 20833 0 897 50000 0 254880 857290 254880 602410 284900 81400 20833 0 0 50000 30000 467133 663410 467133 196277 200000 567639 767639 200000 567639 0 0 0 0 767639 189467 957105 200000 757105 0 0 0 0 957105 -249573 707532 200000 507532 0 0 0 0 707532 -590313 117219 200000 -82781 82781 0 82781 897 200000 602410 802410 200000 602410 0 82781 0 0 719629 196277 915905 200000 715905 0 0 0 0 TABLE 3: CASH BUDGET (Cont.) Cash collections: Sales Month 0 Collections Month -1 Collections Month -2 Collections Total Collections Beginning Accounts Receivable Sales Cash Collections Ending Accounts Receivable July August September October November December 666000 2442000 305250 610500 980000 2000000 333000 1221000 152625 305250 490000 1000000 122100 199800 732600 91575 183150 294000 39886 79772 130536 478632 59829 119658 494986 1500572 1015761 875457 732979 1413658 244200 666000 494986 414400 414400 2442000 1500572 1354200 1354200 305250 1015761 641025 641025 610500 875457 366300 366300 980000 732979 612100 612100 2000000 1413658 1196000 Cash disbursements: Beginning Accounts Payable Purchases Payment of Accounts Payable Ending Accounts Payable Payment of Accounts Payable Wages and other expenses FNCE 371v3 July August September October November December 466200 1709400 213675 427350 686000 1400000 1709400 213675 427350 686000 1400000 140000 466200 1709400 213675 427350 686000 1400000 1709400 213675 427350 686000 1400000 140000 466200 1709400 213675 427350 686000 1400000 133200 488400 61050 122100 196000 400000 Assignment 1, Case 1 of 3 October 2014 Taxes Capital exp ST interest expense LT interest expense Dividends Cash disbursements Cash Collections Cash Disbursements Net cash inflow Cash Budget: Beginning cash balance Net cash inflow Ending cash balance Minimum cash balance Surplus/deficit Short-term borrowing Repayment of ST debt Cumulative ST debt ST interest expense 20833 0 0 50000 0 670233 494986 670233 -175247 20833 0 0 50000 0 2268633 1500572 2268633 -768061 20833 0 2464 50000 30000 378022 1015761 378022 637739 20833 0 0 50000 0 620283 875457 620283 255174 20833 0 0 50000 0 952833 732979 952833 -219854 20833 0 0 50000 30000 1900833 1413658 1900833 -487175 915905 -175247 740658 200000 540658 0 0 0 0 740658 -768061 -27403 200000 -227403 227403 0 227403 2464 200000 637739 837739 200000 637739 0 227403 0 0 610336 255174 865509 200000 665509 0 0 0 0 865509 -219854 645655 200000 445655 0 0 0 0 645655 -487175 158480 200000 -41520 41520 0 41520 450 TABLE 4: Sales Forecasts for next 13 months are: January 200000 February 400000 March 800000 April 2200000 May 220000 June 440000 July 720000 August 2640000 September 330000 October 660000 November 1080000 December 3960000 January 220000 Mr. Jackson asks Ms. Truly to produce a report on the current state of the company's cash flows and short-term financing needs for a meeting next week. Ms. Truly wrote down the following task list that must be accomplished prior to writing her report: (a) Construct the monthly cash collections table. FNCE 371v3 Assignment 1, Case 1 of 3 October 2014 (b) Construct the monthly cash disbursements table. (c) Calculate the monthly net cash inflow. (d) Construct the monthly cash budget. In the report, Ms. Truly plans to include the cash budget, as well as answers to the following questions (just sent in by Mr. Jackson): 1. What will be the monthly cash deficits and surpluses, and how much short-term financing will the company need in this coming year? What can be inferred from the pattern of cash deficits and surpluses, and the pattern of requirements for short-term financing? 2. Why is depreciation expense (a large amount) not included in the cash budget? 3. Evaluate the company's minimum cash reserve policy. What will happen to the cash budget if we changed the minimum cash reserve to $0? Or $5,000? Or $50,000? Or $500,000? Should the company stick with its $200,000 minimum cash balance? 4. The Bank of Scotia is offering to invest the company's surplus cash at 6% APR compounded semiannually for a fee of $2,000 per year, payable at the end of the year. Earnings on the investment will be calculated and deposited at the end of each month. Should the company invest with the bank? 5. The sales estimates were provided by the sales department. Can we trust these figures? What can we do to overcome the forecasting risk? FNCE 371v3 Assignment 1, Case 1 of 3 October 2014 Side Note #1: Mr. Jackson has told Ms. Truly that he does not like looking at Excel spreadsheet (his actual words were: \"these gobbledygooks give me a headache\"), and he requested that Ms. Truly not show him any Excel spreadsheet. A word processed document containing the cash budget and analyses will do. Side Note #2: To make things easier and more efficient, Ms. Truly asked the office intern (Mr. John Jones, a third-year student in the Princess University undergraduate business program) to build a spreadsheet program that will allow them to simply enter the sales figures and quickly produce the cash budget. Unfortunately, Mr. Jones returned to his studies before he could check the accuracy of the spreadsheet program, and there appears to be some errors in the program, as the numbers do not add up when Ms. Truly input the sales figures from last year. Ms. Truly can either look through the spreadsheet program (Cash Budget Builder.xlsx) and find and fix the errors, or build her own cash budget manually. NOTE TO STUDENTS: Since it is a big undertaking to set up and build a budget spreadsheet, you can use the incorrect spreadsheet program (Cash Budget Builder.xlsx), or you can build your own. If you elect to use the incorrect spreadsheet program, make sure that you find and fix the errors first. One way to know that the errors have been fixed is when your cash budget has the same numbers as the one provided in the case. FNCE 371v3 Assignment 1, Case 1 of 3 October 2014