Ms. Am Truly is the new CFO of M and B, Inc., which produces popular yoga and Pilates videos. Ms. Truly is concerned about the company's cash flow management, and would like to get a better "feel" for the way cash flows are managed at M and B, Inc. The CEO of the company, Mr. LJackson, is worried about the company's cash situation. Although the company has consistently produced positive net income, the level of its short-term borrowing is worrisome. 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 effectively suit the company's cash flows.

To this end, Mr. Jackson has provided Ms. Truly with the company's most recent Statement of Comprehensive Income, Statement of Financial Position, and Cash Budget, and the following disparate information:

Purchases from suppliers = 70% of predicted sales for the next month

Accounts payable period = 30 days

Wages and other expenses = 20% of predicted sales

Capital expenditures (computer system purchase) in June = $500,000

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

Customer payments: 50% in the month of sales, 30% pay in the month after sales, and 20% two months after sales

Bad debt = ~ 2% if customers have not made payment after 60 days

Table 1: Last Year's Statement of Comprehensive Income

Sales $10,944,250

Cost of goods sold 7,660,975

Wages and other expenses 2,188,850

Earnings before depreciation, interest, and taxes 1,094,425

Depreciation 100,000

Earnings before interest and taxes 994,425

Interest expense 603,760

Taxable income 390,665

Taxes 140,640

Net income 250,025

Dividends 120,000

Additions to retained earnings 130,025

Table 2: Last Year's Statement of Financial Position

Cash $200,000 Accounts payable 140,000

Inventory 140,000 Notes payable 41,520

Accounts receivable 792,080 Current liabilities 181,520

Current assets 1,132,080 Long-term debt 6,000,000

Common stock 2,500,000

Net fixed assets 9,004,814

Retained earnings 1,455,374

Total owners' equity 3,955,374

Total assets 10,136,894

Total liabilities & owners' equity 10,136,894

Table 3: Cash Budget

Cash collections:

January February March April May June

Sales 185,000 370,000 740,000 2,035,000 203,500 407,000

Month 0 collections 92,500 185,000 370,000 1,017,500 101,750 203,500

Month -1 collections 540,000 55,500 111,000 222,000 610,500 61,050

Month -2 collections 172,872 352,800 36,260 72,520 145,040 398,860

Total collections 805,372 593,300 517,260 1,312,020 857,290 663,410

Beg accts receivable 1,076,400 452,500 222,000 444,000 1,165,500 508,750

Sales 185,000 370,000 740,000 2,035,000 203,500 407,000

Cash collections 805,372 593,300 517,260 1,312,020 857,290 663,410

Ending acts recei 452,500 222,000 444,000 1,165,500 508,750 244,200

Cash disbursements:

January February March April May June

Beginning accounts payable 129,500 259,000 518,000 1,424,500 142,450 284,900

Purchases 259,000 518,000 1,424,500 142,450 284,900 466,200

Payment of accounts Payable 129,500 259,000 518,000 1,424,500 142,450 284,900

Ending accounts payable 259,000 518,000 1,424,500 142,450 284,900 466,200

Payment of accounts payable 129,500 259,000 518,000 1,424,500 142,450 284,900

Wages and other expenses 37,000 74,000 148,000 407,000 40,700 81,400

Taxes 20,833 20,833 20,833 20,833 20,833 20,833

Capital expense 0 0 0 0 0 0

ST interest expense 400 0 0 0 897 0

LT interest expense 50,000 50,000 50,000 50,000 50,000 50,000

Dividends 0 0 30,000 0 0 30,000

Cash disbursements 237,733 403,833 766,833 1,902,333 254,880 467,133

Cash collections 805,372 593,300 517,260 1,312,020 857,290 663,410

Cash disbursements 237,733 403,833 766,833 1,902,333 254,880 467,133

Net cash inflow 567,639 189,467 -249,573 -590,313 602,410 196,277

Cash Budget:

January February March April May June

Beginning cash balance 200,000 767,639 957,105 707,532 200,000 719,629

Net cash inflow 567,639 189,467 -249,573 -590,313 602,410 196,277

Ending cash balance 767,639 957,105 707,532 117,219 802,410 915,905

Minimum cash balance 200,000 200,000 200,000 200,000 200,000 200,000

Surplus/deficit 567,639 757,105 507,532 -82,781 602,410 715,905

Short-term borrowing 0 0 0 82,781 0 0

Repayment of ST debt 0 0 0 0 82,781 0

Cumulative ST debt 0 0 0 82,781 0 0

ST interest expense 0 0 0 897 0 0

Table 3: Cash Budget (Cont.)

Cash collections:

July August September October November December

Sales 666,000 2,442,000 305,250 610,500 980,000 2,000,000

Month 0 collections 333,000 1,221,000 152,625 305,250 490,000 1,000,000

Month -1 collections 122,100 199,800 732,600 91,575 183,150 294,000

Month -2 collections 39,886 79,772 130,536 478,632 59,829 119,658

Total collections 494,986 1,500,572 1,015,761 875,457 732,979 1,413,658

Beg acts receivable 244,200 414,400 1,354,200 641,025 366,300 612,100

Sales 666,000 2,442,000 305,250 610,500 980,000 2,000,000

Cash collections 494,986 1,500,572 1,015,761 875,457 732,979 1,413,658

Ending acts recei 414,400 1,354,200 641,025 366,300 612,100 1,196,000

Cash disbursements:

July August September October November December

Beg acts payable 466,200 1,709,400 213,675 427,350 686,000 1,400,000

Purchases 1,709,400 213,675 427,350 686,000 1,400,000 140,000

Pment acts p.able 466,200 1,709,400 213,675 427,350 686,000 1,400,000

Ending acts p.able 1,709,400 213,675 427,350 686,000 1,400,000 140,000

Pment acts p.able 466,200 1,709,400 213,675 427,350 686,000 1,400,000

Wages & other exp 133,200 488,400 61,050 122,100 196,000 400,000

Taxes 20,833 20,833 20,833 20,833 20,833 20,833

Capital expense 0 0 0 0 0 0

ST interest expense 0 0 2,464 0 0 0

LT interest expense 50,000 50,000 50,000 50,000 50,000 50,000

Dividends 0 0 30,000 0 0 30,000

Cash disbursements 670,233 2,268,633 378,022 620,283 952,833 1,900,833

Cash collections 494,986 1,500,572 1,015,761 875,457 732,979 1,413,658

Cash disbursements 670,233 2,268,633 378,022 620,283 952,833 1,900,833

Net cash inflow -175,247 -768,061 637,739 255,174 -219,854 -487,175

Cash budget:

January February March April May June

Beg cash balance 915,905 740,658 200,000 610,336 865,509 645,655

Net cash inflow -175,247 -768,061 637,739 255,174 -219,854 -487,175

Ending cash balance 740,658 -27,403 837,739 865,509 645,655 158,480

Minimum cash bal 200,000 200,000 200,000 200,000 200,000 200,000

Surplus/deficit 540,658 -227,403 637,739 665,509 445,655 -41,520

Short-term borrowing 0 227,403 0 0 0 41,520

Repayment of ST debt 0 0 227,403 0 0 0

Cumulative ST debt 0 227,403 0 0 0 41,520

ST interest expense 0 2,464 0 0 0 450

Table 4: Sales Forecasts for next 13 months

January 200,000

February 400,000

March 800,000

April 2,200,000

May 220,000

June 440,000

July 720,000

August 2,640,000

September 330,000

October 660,000

November 1,080,000

December 3,960,000

January 220,000

. 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 tasks that must be completed prior to writing her report:

Construct the monthly cash collections table.

Construct the monthly cash disbursements table.

Calculate the monthly net cash inflow.

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 predicted monthly cash deficits and surpluses, and how much short-term financing will the company need in the 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 would happen to the cash budget if we changed the minimum cash reserve to $0? To $5,000? To $50,000? To $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 semi-annually 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 be done to overcome the forecasting risk?

Notes

1.Mr. Jackson has told Ms. Truly that he does not like looking at Excel spreadsheets (he actually said, "these gobbledygooks give me a headache"), and he requested that Ms. Truly not show him any. He would prefer a word-processed document containing the cash budget and analyses.

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. Mr. Jones did that, but unfortunately, he returned to his studies before he could check the accuracy of the spreadsheet program. There appears to be some errors in the program, as the numbers did 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.