Required: Complete the Excel Spreadsheet- Cash Budget Assumptions for Cash Budge
ID: 2467793 • Letter: R
Question
Required: Complete the Excel Spreadsheet- Cash Budget
Assumptions for Cash Budget:
Sales for each quarter: (On Excel Spreadsheet)
Beginning Cash Balance: 100
Minimum Cash Balance: 75
Beginning Accounts Receivable Balance: 200 (received in the 1st Qtr)
Beginning Payables Balance: 100 (Paid in the 1st Qtr)
Purchases: 50% of next qtr sales
Accounts Receivable Turn: 60 days
Accounts Payable Turn: 30 days
Wages, Taxes and Expenses: 25% of sales
Interest Expense: 20 per quarter
Cash Collections Qtr 1 Qtr 2 Qtr 3 Qtr 4 Next Qtr-1 Beg Receivables Sales 320 400 480 500 560 Total Cash Collections Ending Receivables Cash Disbursements Qtr 1 Qtr 2 Qtr 3 Qtr 4 Beginng Payables Payment of A/P Wages, Tx's & Expenses Interest Payments Total Cash Disbursements Net Cash Flow & Cash Qtr 1 Qtr 2 Qtr 3 Qtr 4 Balance Beginning Cash Balance Total Cash Collections Total Cash Disbursements Net Cash Inflow Ending Cash Balance Minimum Cash Balance Cumulative Surplus (Deficit)Explanation / Answer
60 day collection period complies all recievable outstanding from previous quarter collected in the current quarter and (90-60)/90 = 1/3 of current sales are collected
30 days implies all payable outstnading from previus quarter are collected in the current quarter and (90-30)/90 = 2/3 current purchases are paild
Cash collection QTR 1 QTR 2 Qtr 3 Qtr 4 Qtr 5 Begining receivable 200 Sales 320 400 480 500 560 Total cash collection 307 346 427 487 Ending recievables 213 267 320 333 Cash disbursement Beginning payable 100 Payment of accounts payable 133 227 247 270 Wages taxes & expenses 80 100 120 125 Interest payment 20 20 20 20 Total cash disbursement 333 347 387 415 Net cash flow & cash Balance 74 73 113 Beginning cash bal 100 total cash collection 307 346 427 487 Total cash disbursement 333 347 387 415 Net cash inflow 407 420 500 600 Ending cash balance 74 73 113 185 Surplus ( deficit) -1 -2 38 110Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.