Please only use the numbers provided here in the Excel Template and solve for Cash Budget.#5 Chapter 18 Question 5 Input Area: Beginning A/R$ 265 a.Collection period45APP b.Collection period60F35% c.Collection period30 Q1Q2Q3Q4 Sales$ 620$ 670$ 620$ 730 Output Area:Q4Q1Q2Q3Q4 620670620730 a.45-day collection periodBeg A/R310 Q1Q2Q3Q4$ coll from curr qtr sales335 Beginning receivables$ 265.00$ 310.00$ 335.00$ 310.00Total Cash Coll645 Sales620.00670.00620.00730.00 Cash collections(575.00)(645.00)(645.00)(675.00) Ending receivables$ 310.00$ 335.00$ 310.00$ 365.00 b.60-day collection period Beginning receivables$ 265.00$ 413.33$ 446.67$ 413.33 Sales620.00670.00620.00730.00 Cash collections(471.67)(636.67)(653.33)(656.67) Ending receivables$ 413.33$ 446.67$ 413.33$ 486.67 c.30-day collection period Beginning receivables$ 265.00$ 206.67$ 223.33$ 206.67 Sales620.00670.00620.00730.00 Cash collections(678.33)(653.33)(636.67)(693.33) Ending receivables$ 206.67$ 223.33$ 206.67$ 243.33 #8 Chapter 18 Question 8 Input Area: Projected sales increase15% Orders (% of sales)30% b.Payables period90 c.Payables period60 Q1Q2Q3Q4 Sales$ 1,210$ 1,330$ 1,170$ 1,460 Output Area: Q1Q2Q3Q4Q1 $ 1,210$ 1,330$ 1,170$ 1,460$ 1,392 a.The payable period is zero since payment is made immediately. Beg A/P242266234292 Payment in each period = 0.30 times next period salesPmts made og curr qtr orders133117146139.15 Q1Q2Q3Q4Total Pmts375383380431.15 Payment of accounts$ 399.00$ 351.00$ 438.00$ 417.45 b.If the payables period is90days. Payment of accounts$ 363.00$ 399.00$ - 0$ 351.00$ - 0$ 438.00 c.If the payables period is60days. Payment of account$ 375.00$ - 0$ 383.00$ - 0$ 380.00$ - 0$ 431.15 #11 Chapter 18 Question 11 Input Area: Uncollected credit sales5% Collected in the month of the sale35% Collected in the following month60% Previous month credit sales$ 459,000 Previous month credit purchases$ 248,400 Beginning cash$ 342,000 AprilMayJune Credit sales$ 486,000$ 549,000$ 586,800 Credit purchases240,100288,100286,200 Cash disbursements Wages, taxes, and expenses62,70086,80095,100 Interest17,60017,60017,600 Equipment purchases127,800148,400- Output Area: Sales collections = .35 times current month sales + .60 times previous month sales. AprilMayJune Beginning cash balance$ 342,000$ 331,000$ 321,850 Cash receipts Cash collections from credit sales445,500483,750534,780 Total cash available$ 787,500$ 814,750$ 856,630 Cash disbursements Purchases$ 248,400$ 240,100$ 288,100 Wages, taxes, and expenses62,70086,80095,100 Interest17,60017,60017,600 Equipment purchases127,800148,400- Total cash disbursements456,500492,900400,800 Ending cash balance$ 331,000$ 321,850$ 455,830 #19 Chapter 18 Questions 19 Input Area: Sales (1st quarter of next year)$ 175 A/R$ 63 Collection period45 % of purchases for next Q sales45% Suppliers paid36 % of sales for expenses30% Interest and dividends$ 15 Outlay in second Q$ 45 Beginning cash balance$ 36 Minimum balance (in millions)$ 20 Borrowing rate3% Invested securities2% Beginning short-term borrowing$ - 0 Q1Q2Q3Q4 Sales (in millions)$ 160$ 145$ 190$ 220 Output Area: 45-day collection period means sales collections = 1/2 current sales + 1/2 old sales 36-day payables period means payables = 3/5 current orders + 2/5 old orders Q1: Cash flow$ 12.05 Q2: Cash flow$ (28.40) Q3: Cash flow$ 1.90 Q4: Cash flow$ 37.15 Cash Balance Q1Q2Q3Q4 Beginning cash balance$ 36.00$ 48.05$ 19.65$ 21.55 Net cash inflow12.05(28.40)1.9037.15 Ending cash balance$ 48.05$ 19.65$ 21.55$ 58.70 Minimum cash balance(20.00)(20.00)(20.00)(20.00) Cumulative surplus (deficit)$ 28.05$ (0.35)$ 1.55$ 38.70 KWC_Casestudy1 Chapter 18NAME:____________________________________________ Keafer Manufacturing Working Capital Management Inputa Data (Do not change) (These are different from Textbook intentionally) Input Area: Compute results here Beginning cash balance$ 105,800 Outlay in third Q$ 305,000 Target cash balance$ 95,000 Q1Q2Q3Q4 Last years' sales$ 795,000$ 844,000$ 914,000$ 783,000 Growth rate in sales7.90% Collection period50Days Beg A/R$ 348,000.00 Percent uncollectible % of purchases for next Q sales44% Suppliers paid39Days % of sales for expenses23% Interest and dividends$ 32,000 Output Area: Sales next year Sales following year Quarterly cash flow Q1Q2Q3Q4 Collections from previous quarter Collections from current quarter sales Payments to suppliers for previous quarter Payments to suppliers for current quarter Expenses Dividends and interest Outlay Net cash flow Cash Balance Q1Q2Q3Q4 Beginning cash balance Net cash inflow Ending cash balance Minimum cash balance Cumulative surplus (deficit)