Question

1. The financial staff at Lehman Inc., a wholesale distributor has estimated the following sales figures for the first half of 2019:

Month	Sales
January	$100,000
February	$120,000
March	$150,000
April	$180,000
May	$150,000
June	$120,000
July	$150,000
August	$180,000

Actual November and December 2018 sales were $200,000 and $90,000, respectively. Cash sales are 45% of the total and the rest are on credit. About 70% of credit sales are typically collected one month after the sale and 30% the second month. Monthly inventory purchases represent 50% of the following month’s sales. The firm pays 40% of its inventory purchases in cash and the remainder in the following month. Wages are expected to be 25% of the month’s sales, plus commissions to sales associates estimated to be 10% of collectable sales. A major capital expenditure of $22,000 is expected in April and a quarterly dividend of $10,000 will be paid to shareholders in March and June. Monthly rent is $2,500 and other maintenance expenses are estimated at 15% of sales. The firm has an ending cash balance of $20,000 for December 2018.

Required:

1. Help the financial staff of Lehman Inc. to prepare a cash budget based on the information above.

2. Consider three scenarios where inventory purchases constitute 30%, 40%, and 50% of the next month’s sales. For each one of these scenarios assume that sales will be 5% better than expected, exactly as expected, or 5% worse than expected. The CEO has asked you to use the Scenario Manager to evaluate each scenario.

Answer 1

Statement Showing Expected Cash Collections for Each month
Given in the problem that
1. 45% of the current month sales value collected in the same month
2. 70% of the last month credit sales value collected in current month
3. 30% of the Last Month Before Last Month Credit Sales value collected in current month
By Considering the above all data,
											Amount in $
			Nov'18	Dec'18	Jan'19	Feb'19	Mar'19	Apr'19	May'19	June'19
Sales			$ 2,00,000	$ 90,000	$ 1,00,000	$ 1,20,000	$ 1,50,000	$ 1,80,000	$ 1,50,000	$ 1,20,000	$    8,20,000
Cash Sales			$    90,000	$ 40,500	$    45,000	$    54,000	$    67,500	$    81,000	$    67,500	$    54,000	$    3,69,000
Credit Sales			$ 1,10,000	$ 49,500	$    55,000	$    66,000	$    82,500	$    99,000	$    82,500	$    66,000	$    4,51,000
Expected Cash Collections from Credit Sales :
Nov'18					$    33,000						$       33,000
Dec'18					$    34,650	$    14,850					$       49,500
Jan'19						$    38,500	$    16,500				$       55,000
Feb'19							$    46,200	$    19,800			$       66,000
Mar'19								$    57,750	$    24,750		$       82,500
Apr'19									$    69,300	$    29,700	$       99,000
May'19										$    57,750	$       57,750
June'19											$                -
					$ 1,12,650	$ 1,07,350	$ 1,30,200	$ 1,58,550	$ 1,61,550	$ 1,41,450	$   8,11,750

Purchases Budget for each month
Given that Purchases of Materials will = 50% of Next Month Sales
											Amount in $
			Nov'18	Dec'18	Jan'19	Feb'19	Mar'19	Apr'19	May'19	June'19	July'19	Total Purchases till June'19
Sales			$ 2,00,000	$ 90,000	$ 1,00,000	$ 1,20,000	$ 1,50,000	$ 1,80,000	$ 1,50,000	$ 1,20,000	$    1,50,000
Purchase of Materials			45000	50000	60000	75000	90000	75000	60000	75000		435000
Statement Showing the Cash Paymets for Purchase of Materials for each month
Given that
1. 40% of Current month purchases value will be paid in same month
2. 60% of the last month [urchases value will be paid in current month
				Dec'18	Jan'19	Feb'19	Mar'19	Apr'19	May'19	June'19	Total From Jan'19 to June'19
Purchase of Materials				$ 50,000	$    60,000	$    75,000	$    90,000	$    75,000	$    60,000	$    75,000	$   4,35,000
Cash Payments for Material Purchases
Dec'18					$    30,000						$       30,000
Jan'19					$    24,000	$    36,000					$       60,000
Feb'19						$    30,000	$    45,000				$       75,000
Mar'19							$    36,000	$    54,000			$       90,000
Apr'19								$    30,000	$    45,000		$       75,000
May'19									$    24,000	$    36,000	$       60,000
June'19										$    30,000	$       30,000
Total Cash Payments for Each Month					$    54,000	$    66,000	$    81,000	$    84,000	$    69,000	$    66,000	$   4,20,000
Cash Budget for the Half Year of 2019
					Jan'19	Feb'19	Mar'19	Apr'19	May'19	June'19	Total
Opening Balance					20000	30650	14900	-16650	-48500	-26700	-26300
Cash Collected From Sales (Note :1)					112650	107350	130200	158550	161550	141450	811750
Total Cash Available (1)					132650	138000	145100	141900	113050	114750	785450
Cash Payments :
Cash Payments for Purchases (Note : 2)					54000	66000	81000	84000	69000	66000	420000
Sales Commision Sales *25% + Credit Sales *10%			30500	36600	45750	54900	45750	36600	250100
Rent					2500	2500	2500	2500	2500	2500	15000
Other Maintanace Expesnes					15000	18000	22500	27000	22500	18000	123000
Dividend					0	0	10000	0	0	10000	20000
Capital Expenditure					0	0	0	22000	0	0	22000
Total Cash Payments (2)					102000	123100	161750	190400	139750	133100	850100
Excess/ Deficiency of Cash [ (1) - (2) ]					30650	14900	-16650	-48500	-26700	-18350	-64650

:) Hope You Like the answer