Question

PROBLEM 1:
Felix Company wants to forecast its cash budget for the next 3 months.
* Estimated sales revenues are:
Month	Revenue
January	$        200,000
February	$        150,000
March	$        300,000
April	$        400,000
All sales are on credit and are estimated to be paid as follows:
Month of Sale	60%
Month after sale	40%
	100%
* Cost of goods sold as a percentage of sales is				75%
* Payments for merchandise sold are made in the month following the month sale.
* Operating expenses total $50,000 per month and are to be paid in the month they are incurred.
* The cash balance estimated on February 1 is $100,000.
QUESTION:   Prepare monthly cash budgets for February, March and April in Excel.

Answer 1

Cash Budget
	Feb	Mar	April
Cash Balance Beginning	100000	70000	147500
Total Cash receipts	170000	240000	360000
Cash Available	270000	310000	507500
Total Disbursements	200000	162500	275000
Cash Balance Ending	70000	147500	232500
Workings:
Cash Receipts	Feb	Mar	April
Jan (200000*40%)	80000
Feb (150000*60%,40%)	90000	60000
Mar (300000*60%,40%)		180000	120000
April (400000*60%)			240000
Total	170000	240000	360000
Cash Disbursements	Feb	Mar	April
COGS	150000 (200000*75%)	112500 (150000*75%)	225000 (300000*75%)
Operating Expenses	50000	50000	50000
Total	200000	162500	275000

** Cost of goods sold (COGS) payment for the month of jan is made in Feb and payment for the month of Feb is made in March

Above Cash budget with Formulas in excel

	A	B	C	D
1	Cash Budget
2		Feb	Mar	April
3	Cash Balance Beginning	100000	=B7	=C7
4	Total Cash receipts	=B14	=C14	=D14
5	Cash Available	=B3+B4	=C3+C4	=D3+D4
6	Total Disbursements	=B19	=C19	=D19
7	Cash Balance Ending	=B5-B6	=C5-C6	=D5-D6
8
9	Cash Receipts	Feb	Mar	April
10	Jan (200000*40%)	=200000*40%
11	Feb (150000*60%,40%)	=150000*60%	=150000*40%
12	Mar (300000*60%,40%)		=300000*60%	=300000*40%
13	April (400000*60%)			=400000*60%
14		=SUM(B10:B13)	=SUM(C10:C13)	=SUM(D10:D13)
15
16	Cash Disbursements	Feb	Mar	April
17	COGS	=200000*75%	=150000*75%	= 300000*75%
18	Operating Expenses	50000	50000	50000
19	Total	=B17+B18	=C17+C18	=D17+D18