A B C D E F G H J 20% 80% Accounts receivable 30 80% of January sales get paid in February, etc 60 Accounts payable, 2 month lag between purchases and payments Actual Forecasted October November December January February Sales $ 360 $ 420 $ $ 1,200 $ 600 $ 240 $ Purchases $ 510 $ 540 $ 1,200 $ 300 $ 120 $ March 240 120 =240 Hint: All three methods should yield the same external funding needs. 1 % cash sales 2 % credit sales 3 Credit terms (in days) 4 Purchase credit terms (in days) 5 6 Wages payable monthly 7 Principal payment (due in March) 8 Interest (due in March) 9 Dividend (payable in March) 10 Taxes (payable in February) 11 Addition to accumulated depreciation (in March) 12 Cash balance on Jan 1 13 Minimum desired cash balance 14 15 16 17 $ $ $ $ $ $ $ $ 180 210 Just in March 90 Just in March 300 Just in March 180 Just in February 30 Just in March 300 Beginning balance 150 Don't want to drop below $150 Finally, use a data table to show how the cash surplus/deficit in January would vary if sales ranged from $300 to $900, by increments on $100. Hint: You must build the data table in the same sheet as the input (January sales). A B D E January February March 1 Cash receipts 2 Cash sales 3 Collections from credit sales 4 Total cash receipts 5 6 Cash disbursements 7 Payment for purchases 8 Wages 9 Interest 10 Principal payments 11 Dividends 12 Taxes 13 Total cash disbursements 14 15 Net cash receipts (disbursements) 16 17 Determination of cash needs 18 Beginning cash 19 Net receipts (disbursements) 20 Ending cash 21 Minimum cash desired 22 Cash surplus (deficit) C D E F G H I J K 3 COGS (1) 6,000 3,900 2,100 1,620 90 90 300 99 201 2021