Question

On the Cash Budget tab, enter 47200 as the beginning cash balance for the first quarter. Enter the data for the four quarters: SES 29 Quarter 14 $118,400 Quarter #3 $156,000 SES $33,200 Quarter #2 $126,400 Quarter #1 5172,000 Cash Receipts from In-Store Customer Sales Cash Receipts from Online $19,600 $21,200 $24,600 Sales 18871 ESKOL Cash Receipts from $8,000 $4,230 $3,800 Services Rendered Reduction in Notes $960 $1,550 $1,100 si 200 Receivable WWW IS Interest Received $730 $850 S420 $650 Sale of Automobile $2,350 $0 SO Cash Disbursements for 598,300 5 127 300895,300 S107,400 Merchandise Purchases Cash Disbursements for $73,600 $71,400 $69,100 $81,300 Operating Expenses Loan Payments (Principal) $2,600 $3,900 $3,900 $3,900 Create formulas in the appropriate locations to summarize cash receipts, cash available, and cash disbursements. Create formulas for the ending cash balances for Quarters 1-4 and for the beginning cash balances for Quarters 2-4.

Answer 1

Following is the completed cash balance

SL. No.	Inflows	Remark	Q1	Q2	Q3	Q4
1	Opening Balance	Given for Q1	$     47,200.00	$     62,950.00	$     47,570.00	$     43,150.00
2	Cash received from in-store sales	Given	$ 1,72,000.00	$ 1,26,400.00	$ 1,56,000.00	$ 1,18,400.00
3	Cash received from online sales	Given	$     24,600.00	$     21,200.00	$     33,200.00	$     19,600.00
4	Cash received from services	Given	$       9,700.00	$       3,800.00	$       4,230.00	$       8,000.00
5	Reduction in notes receivable	Given	$       1,400.00	$       1,100.00	$       1,550.00	$           960.00
6	Interest Received	Given	$           650.00	$           420.00	$           850.00	$           730.00
7	Sale of automobile	Given			$       2,350.00
8	Total Cash receipts	Sum of Sl. No. 2-7	$ 2,08,350.00	$ 1,52,920.00	$ 1,98,180.00	$ 1,47,690.00
9	Total Cash Available	Sum of Sl. No. 1-8	$ 2,55,550.00	$ 2,15,870.00	$ 2,45,750.00	$ 1,90,840.00
10	Out flows
11	Cash disbursements for merchandise purchase	Given	$ 1,07,400.00	$     95,300.00	$ 1,27,300.00	$     98,300.00
12	Cash disbursements for operating expense	Given	$     81,300.00	$     69,100.00	$     71,400.00	$     73,600.00
13	Loan payments (Principal	Given	$       3,900.00	$       3,900.00	$       3,900.00	$       2,600.00
14	Total cash disbursements	Sum of Sl. No. 11-13	$ 1,92,600.00	$ 1,68,300.00	$ 2,02,600.00	$ 1,74,500.00
15	Closing Balance	Sl no. 9-14	$     62,950.00	$     47,570.00	$     43,150.00	$     16,340.00

• Opening balance for quarters 2-4 = previous year's closing balance
• Closing balance = Total Cash available - total cash disbursements