Question

Active Life Inc., a sports equipment retailer, needs to prepare a cash budget for the first quarter of 2018. The financial staff at Active Life has forecasted the following sales figures: January February March April May $100,000 $150,000 $300,000 $250,000 $150,000 Actual sales in October, November, and December 2017 were $125,000, $146,000, and $125,000, respectively. Cash sales are 40% of the total, and the rest are on credit. Under the current credit policy the firm expects to collect 60% of credit sales the following month, 30% two months after, and the remainder in the third month after the sale. Each month, the firm makes inventory purchases equal to 45% of the of the next month's sales. The firm pays for 40% of its inventory purchases in the same month and 60% in the following month; nevertheless, the firm enjoys a 2% discount if it pays during the same month as the purchase. Estimated disbursements include monthly wages and other expenses representing 25% of the same month's sales; a major capital outlay of $30,000 expected in January; a dividend payment of $25,000 in February; $40,000 of long-term debt maturing in March; and a tax payment of $60,000 in April. The interest rate on its short-term borrowing is 7%. It has a required minimum cash balance of $10,000 every month, and has an ending cash balance of $30,000 for December 2017. a) Using the above information, create a cash budget for January to June 2018. The cash budget should account for short-term borrowing and payback of outstanding loans. b) Using Excel's outline feature, group the worksheet area at the top of the cash budget so that the preliminary calculations can be easily hidden or unhidden.

Answer 1

Active Life Inc
Cash Budget
For the period January to April 2018
		October	November	December	January	February	March	April	May	June
Sales:		125000	146000	125000	100000	150000	300000	250000	150000
Collection
Cash		50000	58400	50000	40000	60000	120000	100000	60000	0
First Month			45000	52560	45000	36000	54000	108000	90000	54000
Second Month				22500	26280	22500	18000	27000	54000	45000
Third Month					7500	8760	7500	6000	9000	18000
Total Collections		50000	103400	125060	118780	127260	199500	241000	213000	117000
Purchases		65700	56250	45000	67500	135000	112500	67500	0	0
Payments
During month		26280	22500	18000	27000	54000	45000	27000	0	0
Month after			39420	33750	27000	40500	81000	67500	40500	0
Total Payments		26280	61920	51750	54000	94500	126000	94500	40500	0
Collections		23720	41480	73310	64780	32760	73500	146500	172500	117000
Less: Disbursements
Inventory
Short term Interest
Wages		31250	36500	31250	25000	37500	75000	62500	37500	0
Capital					30000
Divident						25000
Long term debt							40000
taxes								60000
Total		31250	36500	31250	55000	62500	115000	122500	37500	0
Beginning cash balance			10000	14980	39510	49290	19550	-7530	10000	137050
Net Addition		-7530	4980	42060	9780	-29740	-41500	24000	135000	117000
Addjusted balance		-7530	14980	57040	49290	19550	-21950	16470	145000	254050
Add: Current borrowings		17530	0	-17530			14420	-6470	-7950
Ending balance		10000	14980	39510	49290	19550	-7530	10000	137050	254050
Cumulative borrowings		17530	17530				14420
Interest		102.2583	102.2583				84.11667

Answer 2

how to do in excel

Answer 3

how to do in excel

Answer 4

how to do in excel