Question

1. A startup company has 750,000 dollars in equity and borrowed 250,000 dollars from a bank. It’s just started to search for customer order of next year sales and forecast that the estimated sales is 100,000 dollar on January 2020 and keep growing at 10% rate every month till last month of the next year. Sales will be 60% in cash and 40% in net30 credit, and expect 5% bad debt from total monthly sales. The average gross profit is 30%. Raw material will be purchased one month in advance and pay cash in order to get cash discount. The office rent cost 20,000 per month, labor cost 30,000 per month, and other administration cost 10,000 per month will be paid at the end of the month. The minimum cash on-hand policy is 50,000. On 1 December2019, the cash-on-hand is 200,000. If in any month that additional cash is needed, it will borrow from bank in multiple of 10,000 with interest at 12%. And if in any month that extra cash is available, it will invest in marketable securities in multiple of 10,000 with return of 12%. Please prepare the cash budget.

Answer 1

CASH BUDGET                                                                                                                                                                                                                                                                                                                          (IN $)
PARTICULARS	Dec-19	Jan-20	Feb-20	Mar-20	Apr-20	May-20	Jun-20	Jul-20	Aug-20	Sep-20	Oct-20	Nov-20	Dec-20
A. OPENING CASH	200,000	50,000	54,100	52,900	54,030	57,283	52,871	51,128	52,211	56,402	54,013	52,328	51,726
RECEIPTS:
CASH SALES (60%) (10% INCREASE EVERY YEAR)		60,000	66,000	72,600	79,860	87,846	96,631	106,294	116,923	128,615	141,477	155,625	171,187
DEBTORS REALIZATION( 40% IN NEXT 30 DAYS - 5% BAD DEBTS)(10% INCREASE EVERY YEAR)		-	38,000	41,800	45,980	50,578	55,636	61,199	67,319	74,051	81,456	89,602	98,562
INTEREST ON SECURITIES (12%) (=E*12%/12)		1,100	-	-	-	-	-	-	-	-	6,944	13,889	20,833
B. TOTAL RECEIPTS		61,100	104,000	114,400	125,840	138,424	152,266	167,493	184,242	202,667	229,878	259,115	290,583
EXPENSES:
RAW MATERIAL (IN ADVANCE)	40,000	47,000	54,700	63,170	72,487	82,736	94,009	106,410	120,051	135,056	151,562	169,718	-
LABOR		30,000	30,000	30,000	30,000	30,000	30,000	30,000	30,000	30,000	30,000	30,000	30,000
OFFICE RENT		20,000	20,000	20,000	20,000	20,000	20,000	20,000	20,000	20,000	20,000	20,000	20,000
ADMINISTRATION COST		10,000	10,000	10,000	10,000	10,000	10,000	10,000	10,000	10,000	10,000	10,000	10,000
INTEREST EXPENSE ON BORROWING(12%) (F*12%/12)		-	500	100	100	100	-	-	-	-	-	-	-
C. TOTAL EXPENSES	40,000	107,000	115,200	123,270	132,587	142,836	154,009	166,410	180,051	195,056	211,562	229,718	60,000
D. CLOSING CASH
A+B-C	160,000	4,100	42,900	44,030	47,283	52,871	51,128	52,211	56,402	64,013	72,328	81,726	282,308
E. INVESTMENT OF EXCESS CASH	110,000									10,000	20,000	30,000	230,000
F. BORROWING OF ADDITIONAL CASH		50,000	10,000	10,000	10,000	-	-	-	-
CLOSING CASH (D-E+F)	50,000	54,100	52,900	54,030	57,283	52,871	51,128	52,211	56,402	54,013	52,328	51,726	52,308

WORKING NOTES:

PARTICULARS	Dec-19	Jan-20	Feb-20	Mar-20	Apr-20	May-20	Jun-20	Jul-20	Aug-20	Sep-20	Oct-20	Nov-20	Dec-20
TOTAL SALES (A)		100,000.00	110,000.00	121,000.00	133,100.00	146,410.00	161,051.00	177,156.10	194,871.71	214,358.88	235,794.77	259,374.25	285,311.67
GROSS PROFIT (B)		30,000.00	33,000.00	36,300.00	39,930.00	43,923.00	48,315.30	53,146.83	58,461.51	64,307.66	70,738.43	77,812.27	85,593.50
LABOR EXPENSES (C)		30,000.00	30,000.00	30,000.00	30,000.00	30,000.00	30,000.00	30,000.00	30,000.00	30,000.00	30,000.00	30,000.00	30,000.00
RAW MATERIAL REQUIREMENT (A-B-C)		40,000.00	47,000.00	54,700.00	63,170.00	72,487.00	82,735.70	94,009.27	106,410.20	120,051.22	135,056.34	151,561.97	169,718.17
RAW MATERIAL PAYMENTS MADE IN ADVANCE	40,000.00	47,000.00	54,700.00	63,170.00	72,487.00	82,735.70	94,009.27	106,410.20	120,051.22	135,056.34	151,561.97	169,718.17	-