THE CASH BUDGET WITH EXPLANATION Millennium Corporation Cash Budget Information - December 2017 - March 2018 Budget Items Total Sales Credit Sales December 825.000 770,000 January 730,000 690,000 February 840,000 780.000 March 920,000 855.000 290,000 27.000 12,000 290,000 27,000 14,000 Wages & Salaries Rentals Other Expenses Taxes Paid Dividends Equipment Purchase Cash Balance 250,000 27,000 10.000 105,000 40,000 declared 75,000 ordered 100,000 40,000 paid 75,000 paid Assumptions: The company must maintain a $100,000 cash balance at the end of each month 2. 25% of credit sales are collected in the month of sale. 3. The credit sales balance is collected one month after the month of sale for each month 4. The company buys 60% of next month sales and these purchases are paid for in the following month. 5. The April's sale is expected to be $900,000. Required: Use the template below to show the company receipts and disbursement of cash as per the simulation above And answer the questions that follow. Millennium Corporation Cash Budget Worksheet for First Quarter 2018 December January February March $1,500,000 1.350.000 Estimated Sales Estimated Credit Sales Estimated Receipts: Cash sales Collections of Accounts Receivable 25% of last month's credit sales 75% of current month credit sales Total Accounts Receivable collections Estimated purchases Estimated payments of accounts payable $438.000 March - $ Cash Budget - First Quarter, 2018 December January February Sales Projected cash balance beginning of month Receipts: Cash sales Collection of accounts receivable Total cash available Disbursements: Payment of accounts payable Wages and salaries Rent Other expenses Taxes Dividends on common stock Purchase of new equipment Total disbursements Excess of available cash over disbursements Cash loans needed to maintain balance of $100,000 Projected cash balance, end of month Cumulative Net borrowings Gale mon Cash Receipts Receivables Credit Financing Month Sales ($10 12 15 14 14 Assumptions - 10 % Collected at invoicing - 50% one month after - Remainder 2 months after 1. What is the cash receipt for June 2. What is the receivables in July 3. Would you do a loan of $0.5M in July assuming a factoring of 80%? Why? Millennium Corporation Cash Budget Worksheet for First Duarter 2018 December January February March 5825,000 770.000 $730,000 690,000 40,000 Estimated Sales Estimated Credit Sales Estimated Receipts: Cash sales Collections of Accounts Receivable 25% of credit sales in the month 75% of credit sales one month after Total Accounts Receivable collections Estimated purchases Estimated payments of accounts payable 172,500 577.500 750,000 504,000 438,000 $438,000 March $ Cash Budget - First Quarter 2018 December January February Sales $825,000 $730,000 $ Projected cash balance beginning of month $100,000 Receipts: Cash sales 40.000 Collection of accounts receivable 750,000 Total cash available $890,000 Disbursements: Payment of accounts payable $438,000 $ Wages and salaries 250.000 Rent 27,000 Other expenses 10,000 Taxes 105,000 Dividends on common stock Purchase of new equipment Total disbursements $830,000 $ 60,000 Excess of available cash over disbursements Cash loans needed to maintain balance of $100,000 40,000 Projected cash balance, end of month Cumulative Net borrowings $100,000 $ 40,000 AMT (5) AGING OF THE ACCOUNT Days Outstanding 1 10 30 31 to 60 61 to 90 91 to 180 TOTAL 20.000 10.000 9.00 100,000 100 Financing Gap Goods Ordered & Inventoried Account Receivable Payment to Suppliers FINANCING GAP Cost of Foregoing the Payable Discount Disc/(100 - Disc)/ 365 /Contract days - discount days) Let % = whole Numbers For Ex. 2/10 net 30 payment term 12/100-2]/[365/30 - 10)] 2/98 x 365/20 -37% Recommendation: borrow at less than 37% and pay within the 10-day period. Therefore, 37% is the opportunity cost alternative to the 2% over 20-day period Try this one - 3/15 net 60 What is the highest % could the business borrow at to take the discount? SOLN: Discussion Questions: 1. A company must synchronize their receipts of cash to synchronize with their disbursements. Correctly summarize your findings with respect to the synchronization of inflows and outflows of cash in the cash budget tabulated above. From your analysis would you be willing to grant a notes pavable loan to this firm in April based on a 75% Factor? a. Yes b. No c. Rationalize you answer for a or b above 5 Part B: Cash Receipts, Receivables % Credit woment 5 p Month J F M A M J Sales (SM 5.0 4.5 5.0 6.0 6.0 5.8 5.6 Assumptions: - 10% Collected at invoicing - 30% one month after - Remainder 2 months after 1. What is the cash receipt for June 2. What is the receivables in July 3. Would you do a loan of $4.0 M in August assuming a factoring of 75%? Why? THE CASH BUDGET WITH EXPLANATION Millennium Corporation Cash Budget Information - December 2017 - March 2018 Budget Items Total Sales Credit Sales December 825.000 770,000 January 730,000 690,000 February 840,000 780.000 March 920,000 855.000 290,000 27.000 12,000 290,000 27,000 14,000 Wages & Salaries Rentals Other Expenses Taxes Paid Dividends Equipment Purchase Cash Balance 250,000 27,000 10.000 105,000 40,000 declared 75,000 ordered 100,000 40,000 paid 75,000 paid Assumptions: The company must maintain a $100,000 cash balance at the end of each month 2. 25% of credit sales are collected in the month of sale. 3. The credit sales balance is collected one month after the month of sale for each month 4. The company buys 60% of next month sales and these purchases are paid for in the following month. 5. The April's sale is expected to be $900,000. Required: Use the template below to show the company receipts and disbursement of cash as per the simulation above And answer the questions that follow. Millennium Corporation Cash Budget Worksheet for First Quarter 2018 December January February March $1,500,000 1.350.000 Estimated Sales Estimated Credit Sales Estimated Receipts: Cash sales Collections of Accounts Receivable 25% of last month's credit sales 75% of current month credit sales Total Accounts Receivable collections Estimated purchases Estimated payments of accounts payable $438.000 March - $ Cash Budget - First Quarter, 2018 December January February Sales Projected cash balance beginning of month Receipts: Cash sales Collection of accounts receivable Total cash available Disbursements: Payment of accounts payable Wages and salaries Rent Other expenses Taxes Dividends on common stock Purchase of new equipment Total disbursements Excess of available cash over disbursements Cash loans needed to maintain balance of $100,000 Projected cash balance, end of month Cumulative Net borrowings Gale mon Cash Receipts Receivables Credit Financing Month Sales ($10 12 15 14 14 Assumptions - 10 % Collected at invoicing - 50% one month after - Remainder 2 months after 1. What is the cash receipt for June 2. What is the receivables in July 3. Would you do a loan of $0.5M in July assuming a factoring of 80%? Why? Millennium Corporation Cash Budget Worksheet for First Duarter 2018 December January February March 5825,000 770.000 $730,000 690,000 40,000 Estimated Sales Estimated Credit Sales Estimated Receipts: Cash sales Collections of Accounts Receivable 25% of credit sales in the month 75% of credit sales one month after Total Accounts Receivable collections Estimated purchases Estimated payments of accounts payable 172,500 577.500 750,000 504,000 438,000 $438,000 March $ Cash Budget - First Quarter 2018 December January February Sales $825,000 $730,000 $ Projected cash balance beginning of month $100,000 Receipts: Cash sales 40.000 Collection of accounts receivable 750,000 Total cash available $890,000 Disbursements: Payment of accounts payable $438,000 $ Wages and salaries 250.000 Rent 27,000 Other expenses 10,000 Taxes 105,000 Dividends on common stock Purchase of new equipment Total disbursements $830,000 $ 60,000 Excess of available cash over disbursements Cash loans needed to maintain balance of $100,000 40,000 Projected cash balance, end of month Cumulative Net borrowings $100,000 $ 40,000 AMT (5) AGING OF THE ACCOUNT Days Outstanding 1 10 30 31 to 60 61 to 90 91 to 180 TOTAL 20.000 10.000 9.00 100,000 100 Financing Gap Goods Ordered & Inventoried Account Receivable Payment to Suppliers FINANCING GAP Cost of Foregoing the Payable Discount Disc/(100 - Disc)/ 365 /Contract days - discount days) Let % = whole Numbers For Ex. 2/10 net 30 payment term 12/100-2]/[365/30 - 10)] 2/98 x 365/20 -37% Recommendation: borrow at less than 37% and pay within the 10-day period. Therefore, 37% is the opportunity cost alternative to the 2% over 20-day period Try this one - 3/15 net 60 What is the highest % could the business borrow at to take the discount? SOLN: Discussion Questions: 1. A company must synchronize their receipts of cash to synchronize with their disbursements. Correctly summarize your findings with respect to the synchronization of inflows and outflows of cash in the cash budget tabulated above. From your analysis would you be willing to grant a notes pavable loan to this firm in April based on a 75% Factor? a. Yes b. No c. Rationalize you answer for a or b above 5 Part B: Cash Receipts, Receivables % Credit woment 5 p Month J F M A M J Sales (SM 5.0 4.5 5.0 6.0 6.0 5.8 5.6 Assumptions: - 10% Collected at invoicing - 30% one month after - Remainder 2 months after 1. What is the cash receipt for June 2. What is the receivables in July 3. Would you do a loan of $4.0 M in August assuming a factoring of 75%? Why