Question

Homework for Chapter 4: Problem #3 in the text (Chapter 4) NOTE: PLEASE USE THE ATTACHED EXCEL FILE TITLED "Homework for Chapter 4 Excel TO SOLVE THE FOLLOWING PROBLEM. 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 STO SI S 000 250.000 $150,000 Actual sales in October, November, and December 2017 were $125,000, S146,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 eredit 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 cqual 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: S40,000 of long-term debt maturing in March, and a tax payment of 560,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 530,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 pay back 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

please show all work in excel

Answer 1

okau
	Oct$	Nov$	Dec$	Jan$	Feb$	Mar$	April$	May$	Jun'$
Sales-A	1,25,000	1,46,000	1,25,000	1,00,000	1,50,000	3,00,000	2,50,000	1,50,000
Condition - cash sales are 40%
of the total sales
and rest are on Credit
As per credit policy ,
collect 60% of credit sales
in the following month
30%- in Two months
Remainder - thrid month of sales
cREDIT Sales - 60% * A= B	75,000	87,600	75,000	60,000	90,000	1,80,000	1,50,000	90,000
Collection on Credit Sales (60%)- as derived in B
collect 60% of credit sales -B( following Month)		45,000	52,560	45,000	36,000	54,000	1,08,000	90,000	54,000
collect 30% of credit sales -B( in 2 Months)			22,500	26,280	22,500	18,000	27,000	54,000	45,000	27,000
collect 10% of credit sales -B( in 3rd Months)				7,500	8,760	7,500	6,000	9,000	18,000
Cash Sales - 40% * A	50,000	58,400	50,000	40,000	60,000	1,20,000	1,00,000	60,000
Total collection ( cash + Stage wise Credit)=A	50000	103400	125060	118780	127260	199500	241000	213000	117000
Each month - Invenory purchase Equal to 45% of the next month Sales	65,700	56,250	45,000	67,500	1,35,000	1,12,500	67,500	-
So in Jan Invetory Purchase - 45% of Nov Month Sales )
Payment Stage
40%- Inventory purchase in Same month	26,280	22,500	18,000	27,000	54,000	45,000	27,000	-	-
40%- Inventory purchase in following month		39,420	33,750	27,000	40,500	81,000	67,500	40,500	-
Total Payments =B	26,280	61,920	51,750	54,000	94,500	1,26,000	94,500	40,500
Closing Cash Balance(A-B)=C	23,720	41,480	73,310	64,780	32,760	73,500	1,46,500	1,72,500	1,17,000
Others
Wages
25% on Same month Sales @25% * A	31,250	36,500	31,250	25,000	37,500	75,000	62,500	37,500	-
Major capital Outlay				30,000
Divided Payment					25,000
Long term debt						40,000
Tax payment							60,000
Cash Outlay=D	31,250	36,500	31,250	55,000	62,500	1,15,000	1,22,500	37,500
Net( Deficit )/ Surplus(C-D)	-7,530	4,980	42,060	9,780	-29,740	-41,500	24,000	1,35,000	1,17,000
Opening Balance		10,000	14,980	39,510	49,290	19,550	10,000	34,000	1,37,050
Surplus as above		4,980	42,060	9,780	-29,740	-41,500	24,000	1,35,000	1,17,000
Repayment			-17530					-31,950
As per Question , We need to maintain closing cash Balance $ 10000
Need Current Borrowings ( Inject fund )	17530					31,950
$ 10000+$7530)
Closing Cash Balance	10,000	14,980	39,510	49,290	19,550	10,000	34,000	1,37,050	2,54,050
Borrowing in oct - Repayment in Dec Borrowing in March - repay in May
Borrowing above	17,530	17,530				31,950	31,950
Interest 7%
Monthly Interest	102.26	102.26				186.38	186.38
$ 17530*7%/12= $ 102.26						$ 31950*7%/12	$ 31950*7%/12