Question

MayCee’s Departmental Store: A Case Study MayCee’s Departmental Stores Inc. is currently in troubled waters. Competition from online retailers has resulted in less frequent store visits from customers. A recent survey has revealed that customers on an average are also spending less time on the shopping floor. Accurate budgeting is therefore a key element of business success and survival for MayCee’s. You have just been hired as an intern at MayCee’s headquarters in Cleveland OH and your first assignment is to help the CFO Ms. Cathy Wilkins prepare a cash budget. At your first meeting with the CFO, you are provided with the following information. Past Sales Nov 2017: $650,000 Dec 2017: $870,000 Projected Sales for Six-Months ending June 2018 are as follows: January February March April May June $350,000 $400,000 $475,000 $650,000 $500,000 $675,000 Sales Receivables Cash Sales: 25% of Net Sales; Payment for 45% of Net Sales are received the following month; Receivables for all other sales are collected after 60 days (i.e., two months). Cost of Goods Sold Cost of Goods Sold is currently estimated at 65% of Net Sales. MayCee’s pays 20% in cash upfront, 45% next month and for the remaining, it enjoys a Net 60 credit terms. Wages and Salaries Fixed Cost $81,000 and this is applicable for Winter months January and February only. For all other months, the variable wages and salaries (in addition to the fixed cost) are estimated at 25% of difference between the Net Sales for the month and the average sales for January and February. Miscellaneous Fixed Overhead Expenses Store Lease $34,336; Utilities and Water $2,600; Interest on Long-Term Corporate Bonds $11,000. Taxes $5,585 for January; $8,320 for June. Dividends Estimated quarterly dividends in the tune of $19,257 will be paid to shareholders in January and $32,335 in April. Additional Information (i) Ending cash balance for December 2017 is $21,000 (ii) Short-Term Borrowings (i.e., for 30 days), for December 2017 is $52,000 (iii) MayCee’s borrows money (Short-Term Borrowings, i.e., for 30 days) by issuing Commercial Paper (CP) if the month-end cash balance falls below $14,275. All borrowed funds must be paid off after 30 day along with interest. Interest on MayCee’s CP is currently estimated at 7.25%. (iv) MayCee’s invests cash in excess of $43,000 in 30 days Money Market Instruments. Principal along with interest is received after 30 days. Interest on Money Market Instruments are currently estimated at 5.25%.

The CFO wants you to 1. Prepare an estimate of monthly cash balances for the six months ending June 2018. 2. Do a “What-if Analysis” if projected sales go down by 10% (80) (20)

Show excel functions

Answer 1

Particulars	Jan	Feb	Mar	Apr	May	Jun
Receipts
Opening CB	21,000	41,058	78,322	69,915	21,825	58,451
Sales Received (WN-1)	674,000	518,500	403,750	496,250	560,000	588,750
Interest on MMI-5.25%	-	-	(35,322*5.25%*1/12) 155	(26,915*5.25%*1/12) 118	-	(15,451*5.25%*1/12) 68
ST Borrowings
Total Receipts (A)	695,000	559,558	482,226	566,283	581,825	647,269
Payments
Cost of Goods Sold (WN-2)	447,850	352,300	258,375	314,438	363,188	381,875
Wages & Salaries (WN-3)	81,000	81,000	106,000	149,750	112,250	156,000
Miscellaneous-FOH (given)	36,936	36,936	36,936	36,936	36,936	36,936
Interest-Corporate bonds (given)	11,000	11,000	11,000	11,000	11,000	11,000
Taxes (given)	5,585	-	-	-	-	8,320
Dividend Estimates (given)	19,257	-	-	32,335	-	-
ST borrowing paid	52,000	-	-	-	-	-
Interest on ST borrowings-7.25%	(52,000*7.25%*1/12) 314.17	-	-	-	-	-
Total Payments (B)	653,942	481,236	412,311	544,459	523,374	594,131
Total Cash Available (C) =(A-B)	41,058	78,322	69,915	21,825	58,451	53,138
Minimum CB is Satisfied (requirement)	14,275	14,275	14,275	14,275	14,275	14,275
Excess of $43,000 cash available(c) is Invested in MM Instruments	-	35,322	26,915	-	15,451	10,138

Working Note -1 Sales Receivable
		Past		Projected                                               in ($)
	Particulars	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun
	Sales	650,000	870,000	350,000	400,000	475,000	650,000	500,000	675,000
	Cash Sales
	-25% of Net Sales	162,500	217,500	87,500	100,000	118,750	162,500	125,000	168,750
	Credit Sales(Debtor Collection)
	-45% in 1 months time	-	292,500	391,500	157,500	180,000	213,750	292,500	225,000
	-30% in 2 months time	-	-	195,000	261,000	105,000	120,000	142,500	195,000
	Total	162,500	510,000	674,000	518,500	403,750	496,250	560,000	588,750

So from sales made in Jan'18 are received in 3 installments which is 25% in Jan(cash sales), 45% in Feb(credit sale) and rest 30%(credit sale) in Mar'18.

Jan'18 Sales are 350,000 received as follows,

• 25% Cash sales in Jan- $87,500
• 45% in Next 1month (feb) - $157,500 &
• 30% in 2^nd month March - $ 105,000.

However, actual cash received in Jan'18 is as follows.

• Cash received from sales of the same month (i.e 25% from Jan'18 sales)
• Cash received from Sales of Dec month (i.e 45% of Dec'17 Sales)
• Cash received from Sales of Nov month (i.e 30% from Nov'17 Sales)

Working Note -2 Cost of Goods Sold
		Past		Projected                                               in ($)
	Particulars	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun
	Sales	650,000	870,000	350,000	400,000	475,000	650,000	500,000	675,000
	Cost of Goods Sold -65%	422,500	565,500	227,500	260,000	308,750	422,500	325,000	438,750
	Payments
	-20% upfront	84,500	113,100	45,500	52,000	61,750	84,500	65,000	87,750
	-45% in next month	0	190,125	254,475	102,375	117,000	138,938	190,125	146,250
	-35% in 2 month time	0	0	147,875	197,925	79,625	91,000	108,063	147,875
	Total	84,500	303,225	447,850	352,300	258,375	314,438	363,188	381,875

Purchase value of Jan'18 are 65% of Net sales of that month. i.e for Jan'18 the purchase value will be 65% of $350,000 = $227,500.

Purchases for Jan'18 are made in 3 installments(similar to sales)

• 20% in same month i.e Jan'18 - $227,500*20% = $45,500.
• 45% in next 1 month i.e in Feb - $227,500*45% = $102,375.
• 35% in 2^nd month i.e in Mar - $227,500*35% = $ 79,625.

But actual payment made for Purchase bills in Jan'18 = $447,850 will be as follows,

• 20% of Jan'18 purchase - $45,500.
• 45% of Dec'17 purchase payment made in jan'18 - $ 254,475. ($565,500*45%)
• 35% of Nov'17 purchase payment made in jan'18 - $ 147,875. ($422,500*35%)

Working Note -3 Variable wages & salaries from Mar to Jun
		Projected                                               in ($)
	Particulars	Jan	Feb	Mar	Apr	May	Jun
	Net Sales of the month	350,000	400,000	475,000	650,000	500,000	675,000
	Average of Jan & Feb	375,000	375,000	375,000	375,000	375,000	375,000
	Difference	(25,000)	25,000	100,000	275,000	125,000	300,000
	Variable -25% of Difference	-	6,250	25,000	68,750	31,250	75,000
	Wages and Salary - Fixed	81,000	81,000	81,000	81,000	81,000	81,000
	Wages and Salary - Variable	-	-	25,000	68,750	31,250	75,000
	Total	81,000	81,000	106,000	149,750	112,250	156,000

If Projected Sales go down by 10%

Cash Budget

WN -1 Sales Receivable

WN -2 Cost of Goods Sold

WN -3 Variable wages & salaries from Mar to Jun

Particulars Feb Mar Apr Jun Receipts Opening CB Sales Received 21,00036,858 63,809 52,67754,579 31,761 665,250 492,750363,375446,625504,000 529,875 Interest on MMI-5.25% | - 91 42 51 ST Borrowings 52,000 Total Receipts (A) Payments 686,250529,608427,275551,344558,630561,636 Cost of Goods Sold 443,300 336,863 232,538282,994326,869 343,688 81,000 81,000 94,125 133,500 99,750 139,125 36,936 36,93636,93636,93636,93636,936 11,000 11,000 11,000 11,000 11,000 11,000 5,585 19,257 52,000 314.17 649,392465,799374,599496,765526,869539,069 Wages & Salaries Miscellaneous-FOH Interest-Corporate bonds Taxes 8,320 Dividend Estimates 32,335 52,000 Sl borrowing paid Interest on ST borrowings-7.25% 314.17 Total Payments (B) Total Cash Available (C)36,85863,809 52,677 22,568 Minimum CB is Satisfied 14,27514,275 14,2751 Excess of $43,000 Invested in MMI 54,579 31,761 14,275 14,27514,275 11,579 20,8099,6717 in (S) May Jun Past Projected Particulars Nov Dec Jan Mar Apr 650,000 870,000 315,000 360,000 427,500585,000 450,000 607,500 Cash Sales -25% of Net Sales 162,500 217,500 78,750 90,000 106,875 146,250 112,500 151,875 Credit Sales(Debtor Collection) -45% in 1 months time 292,500391,500 141,750 162,000 192,375 263,250 202,500 195,000 261,000 94,500 108,000 128,250 175,500 162,500 510,000 665,250 492,750 363,375 446,625 504,000 529,875 -30% in 2 months time !: - | Total in ($) May Past Projected Particulars Mar Apr Sales 650,000 870,000 315,000 360,000 427,500 585,000 450,000 607,500 Cost of Goods Sold-65% Payments | 422,500| 565,500| 204,750| 234,000| 277,875| 380,250| 292,500| 394,875 -20% upfront -45% in next month -35% in 2 month time 84,500113,100 40,950 46,800 55,575 76,050 58,500 78,975 0 190,125254,475 92,138105,300 125,044 171,113131,625 0 147,875 197,925 71,663 81,900 97,256133,088 84,500 303,225 443,300 336,863 232,538 282,994326,869 343,688 0 Total Projected in (S) Particulars Jan Feb Mar AprMayJun 315,000 360,000 427,500 585,000 450,000607,500 375,000| 375,000| 375,000| 375,000 375,000 375,000 (60,000)(15,000) 52,500 210,000 75,000 232,500 Net Sales of the month Average of Jan & Feb Difference variable-25% of Difference (3,75013,12552,500 18,750 58,125 Wages and Salary - Fixed 81,000 81,000 81,000 81,000 81,000 81,000 Wages and Salary-Variable- 13,125 52,50018,75058,125 Total 81,00081,00094,125133,500 99,750139,125

Particulars Feb Mar Apr Jun Receipts Opening CB Sales Received 21,00036,858 63,809 52,67754,579 31,761 665,250 492,750363,375446,625504,000 529,875 Interest on MMI-5.25% | - 91 42 51 ST Borrowings 52,000 Total Receipts (A) Payments 686,250529,608427,275551,344558,630561,636 Cost of Goods Sold 443,300 336,863 232,538282,994326,869 343,688 81,000 81,000 94,125 133,500 99,750 139,125 36,936 36,93636,93636,93636,93636,936 11,000 11,000 11,000 11,000 11,000 11,000 5,585 19,257 52,000 314.17 649,392465,799374,599496,765526,869539,069 Wages & Salaries Miscellaneous-FOH Interest-Corporate bonds Taxes 8,320 Dividend Estimates 32,335 52,000 Sl borrowing paid Interest on ST borrowings-7.25% 314.17 Total Payments (B) Total Cash Available (C)36,85863,809 52,677 22,568 Minimum CB is Satisfied 14,27514,275 14,2751 Excess of $43,000 Invested in MMI 54,579 31,761 14,275 14,27514,275 11,579 20,8099,6717 in (S) May Jun Past Projected Particulars Nov Dec Jan Mar Apr 650,000 870,000 315,000 360,000 427,500585,000 450,000 607,500 Cash Sales -25% of Net Sales 162,500 217,500 78,750 90,000 106,875 146,250 112,500 151,875 Credit Sales(Debtor Collection) -45% in 1 months time 292,500391,500 141,750 162,000 192,375 263,250 202,500 195,000 261,000 94,500 108,000 128,250 175,500 162,500 510,000 665,250 492,750 363,375 446,625 504,000 529,875 -30% in 2 months time !: - | Total in ($) May Past Projected Particulars Mar Apr Sales 650,000 870,000 315,000 360,000 427,500 585,000 450,000 607,500 Cost of Goods Sold-65% Payments | 422,500| 565,500| 204,750| 234,000| 277,875| 380,250| 292,500| 394,875 -20% upfront -45% in next month -35% in 2 month time 84,500113,100 40,950 46,800 55,575 76,050 58,500 78,975 0 190,125254,475 92,138105,300 125,044 171,113131,625 0 147,875 197,925 71,663 81,900 97,256133,088 84,500 303,225 443,300 336,863 232,538 282,994326,869 343,688 0 Total Projected in (S) Particulars Jan Feb Mar AprMayJun 315,000 360,000 427,500 585,000 450,000607,500 375,000| 375,000| 375,000| 375,000 375,000 375,000 (60,000)(15,000) 52,500 210,000 75,000 232,500 Net Sales of the month Average of Jan & Feb Difference variable-25% of Difference (3,75013,12552,500 18,750 58,125 Wages and Salary - Fixed 81,000 81,000 81,000 81,000 81,000 81,000 Wages and Salary-Variable- 13,125 52,50018,75058,125 Total 81,00081,00094,125133,500 99,750139,125