Case 1: Cash Management Ms. Amanda Truly is the new CFO of Mind and Body, Inc., which produces...

Question

Case 1: Cash Management

Ms. Amanda Truly is the new CFO of Mind and Body, 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 Mind and Body, Inc. The CEO of the company, Mr. Lawrence Jackson, 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

Beginning accounts 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 accounts receivable	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

Beginning accounts 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 accounts receivable	414,400		1,354,200			641,025		366,300	612,100		1,196,000
Cash disbursements:
	July		August			September		October	November		December
Beginning accounts 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
Payment of accounts payable	466,200		1,709,400			213,675		427,350	686,000		1,400,000
Ending accounts payable	1,709,400		213,675			427,350		686,000	1,400,000		140,000

Payment of accounts payable	466,200		1,709,400			213,675		427,350	686,000		1,400,000
Wages and other expenses	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
Beginning 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 balance	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

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 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.

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. 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.

3. Note to students: Since setting up and building a budget spreadsheet is a big undertaking, you may choose to use the incorrect spreadsheet program (Cash Budget Builder.xlsx). If you do, make sure that you find and fix the errors before you do your case study analysis. One way to know that the errors have been fixed is when your cash budget reveals the same numbers as the one provided in the case.

Marking Rubric

Item	Available marks
Cash Budget	40
1	10
2	5
3	25
4	15
5	5
Total	100

assignment1cashmanagementcase2019-1-rvgdrhgg.docx assignment1cashbudgetbuilder-1-lf2ylrdz.xlsx

Akshay Kumar · Accepted Answer

Answer: 
Cash Budget for Next Year
	a) Cash collections:
	
	
	
	
	
	
	
	January
	February
	March
	April
	May
	June
	Sales
	200,000
	400,000
	800,000
	2,200,000
	220,000
	440,000
	Month 0 Collections
	100,000
	200,000
	400,000
	1,100,000
	110,000
	220,000
	Month -1 Collections
	600,000
	60,000
	120,000
	240,000
	660,000
	66,000
	Month -2 Collections
	192,080
	392,000
	39,200
	78,400
	156,800
	431,200
	Total Collections
	892,080
	652,000
	559,200
	1,418,400
	926,800
	717,200
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	Beginning Accounts Receivable
	792,080
	452,000
	159,200
	318,400
	816,800
	497,200
	Sales
	200,000
	400,000
	800,000
	2,200,000
	220,000
	440,000
	Cash Collections
	892,080
	652,000
	559,200
	1,418,400
	926,800
	717,200
	Ending Accounts Receivable
	452,000
	159,200
	318,400
	816,800
	497,200
	175,120
	
	
	
	
	
	
	
	b)
	
	
	
	
	
	
	Cash disbursements:
	
	
	
	
	
	
	
	January
	February
	March
	April
	May
	June
	Beginning Accounts Payable
	140,000
	280,000
	560,000
	1,540,000
	154,000
	308,000
	Purchases
	280,000
	560,000
	1,540,000
	154,000
	308,000
	504,000
	Payment of Accounts Payable
	140,000
	280,000
	560,000
	1,540,000
	154,000
	308,000
	Ending Accounts Payable
	280,000
	560,000
	1,540,000
	154,000
	308,000
	504,000
	
	
	
	
	
	
	
	Payment of Accounts Payable
	140,000
	280,000
	560,000
	1,540,000
	154,000
	308,000
	Wages and other expenses
	40,000
	80,000
	160,000
	440,000
	44,000
	88,000
	Income Taxes
	11,720
	11,720
	11,720
	11,720
	11,720
	11,720
	Capital exp
	-
	-
	-
	-
	-
	500,000
	ST interest expense
	400
	-
	-
	-
	-
	-
	LT interest expense
	50,000
	50,000
	50,000
	50,000
	50,000
	50,000
	Dividends
	-
	-
	30,000
	-
	-
	30,000
	Total Cash disbursements
	242,120
	421,720
	811,720
	2,041,720
	259,720
	987,720
	
c)
	
	
	
	
	
	
	Cash Collections
	892,080
	652,000
	559,200
	1,418,400
	926,800
	717,200
	Cash Disbursements
	242,120
	421,720
	811,720
	2,041,720
	259,720
	987,720
	Net cash inflow
	649,960
	230,280
	-            252,520
	-            623,320
	667,080
	-          270,520
	d)
	
	
	
	
	
	
	Cash Budget:
	
	
	
	
	
	
	Beginning cash balance
	200,000
	849,960
	1,080,240
	827,720
	204,400
	871,480
	Net cash inflow
	649,960
	230,280
	-            252,520
	-            623,320
	667,080
	-          270,520
	Ending cash balance
	849,960
	1,080,240
	827,720
	204,400
	871,480
	600,960
	Minimum cash balance
	200,000
	200,000
	200,000
	200,000
	200,000
	200,000
	Surplus/deficit
	649,960
	880,240
	627,720
	4,400
	671,480
	400,960
	
	
	
	
	
	
	
	Short-term borrowing
	-
	-
	-
	-
	-
	-
	Repayment of ST debt
	-
	-
	-
	-
	-
	-
	Cumulative ST debt
	-
	-
	-
	-
	-
	-
	ST interest expense
	-
	-
	-
	-
	-
	-
	Cash Budget (Cont.)
	
	
	
	
	
	
	
Cash collections:
	
	
	
	
	
	
	
	July
	August
	September
	October
	November
	December
	Sales
	720,000
	2,640,000
	300,000
	660,000
	1,080,000
	3,960,000
	Month 0 Collections
	360,000
	1,320,000
	150,000
	330,000
	540,000
	1,980,000
	Month -1 Collections
	132,000
	216,000
	792,000
	90,000
	198,000
	324,000
	Month -2 Collections
	43,120
	86,240
	141,120
	517,440
	58,800
	129,360
	Total Collections
	535,120
	1,622,240
	1,083,120
	937,440
	796,800
	2,433,360
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	Beginning Accounts Receivable
	175,120
	302,240
	933,120
	607,440
	256,800
	453,360
	Sales
	720,000
	2,640,000
	300,000
	660,000
	1,080,000
	3,960,000
	Cash Collections
	535,120
	1,622,240
	1,083,120
	937,440
	796,800
	2,433,360
	Ending Accounts Receivable
	302,240
	933,120
	607,440
	256,800
	453,360
	1,399,680
	
	
	
	
	
	
	
	b)
	
	
	
	
	
	
	Cash disbursements:
	
	
	
	
	
	
	
	July
	August
	September
	October
	November
	December
	Beginning Accounts Payable
	504,000
	1,848,000
	210,000
	462,000
	756,000
	2,772,000
	Purchases
	1,848,000
	210,000
	462,000
	756,000
	2,772,000
	154,000
	Payment of Accounts Payable
	504,000
	1,848,000
	210,000
	462,000
	756,000
	2,772,000
	Ending Accounts Payable
	1,848,000
	210,000
	462,000
	756,000
	2,772,000
	154,000
	
	
	
	
	
	
	
	Payment of Accounts Payable
	504,000
	1,848,000
	210,000
	462,000
	756,000
	2,772,000
	Wages and other expenses
	144,000
	528,000
	60,000
	132,000
	216,000
	792,000
	Income Taxes
	11,720
	11,720
	11,720
	11,720
	11,720
	11,720
	Capital exp
	
	-
	-
	-
	-
	-
	ST interest expense
	-
	-
	6,382
	-
	-
	-
	LT interest expense
	50,000
	50,000
	50,000
	50,000
	50,000
	50,000
	Dividends
	-
	-
	30,000
	-
	-
	30,000
	Total Cash disbursements
	709,720
	2,437,720
	368,102
	655,720
	1,033,720
	3,655,720
	c)
	
	
	
	
	
	
	Cash Collections
	535,120
	1,622,240
	1,083,120
	937,440
	796,800
	2,433,360
	Cash Disbursements
	709,720
	2,437,720
	368,102
	655,720
	1,033,720
	3,655,720
	Net cash inflow
	-            174,600
	-            815,480
	715,018
	281,720
	-            236,920
	-         1,222,

Case 1: Cash Management Ms. Amanda Truly is the new CFO of Mind and Body, Inc., which produces popular yoga and Pilates videos. Ms. Truly is concerned about the company’s cash flow management, and...

Case 1: Cash Management

Notes

Marking Rubric

Solution

Answer To This Question Is Available To Download

Related Questions & Answers

Submit New Assignment