| Microbiz Inc. |
| Cash budget ($ thousands) |
| July 2017 through March 2018 | (Actual) | (Actual) |
| | May | June | July | August | September | October | November | December | Jan. 2018 | Feb. 2018 | Mar. 2018 | Apr. 2018 |
| Projected sales revenues | $410,000.00 | $410,000.00 | $420,000.00 | $504,000.00 | $504,000.00 | $504,000.00 | $462,000.00 | $462,000.00 | $462,000.00 | $420,000.00 | $420,000.00 | $425,000.00 |
| Revenue collections1 |
| Cash sales | $20,500.00 | $20,500.00 | $21,000.00 | $25,200.00 | $25,200.00 | $25,200.00 | $23,100.00 | $23,100.00 | $23,100.00 | $21,000.00 | $21,000.00 |
| 30-day lagged collections | | | $123,000.00 | $126,000.00 | $151,200.00 | $151,200.00 | $151,200.00 | $138,600.00 | $138,600.00 | $138,600.00 | $126,000.00 |
| 60-day lagged collections | | | $262,400.00 | $262,400.00 | $268,800.00 | $322,560.00 | $322,560.00 | $322,560.00 | $295,680.00 | $295,680.00 | $295,680.00 |
| Interest income2 | | | $0.00 | $0.00 | $0.00 | $0.00 | $22.90 | $55.46 | $0.00 | $0.00 | $0.00 |
| Total cash collections | | | $406,400.00 | $413,600.00 | $445,200.00 | $498,960.00 | $496,882.90 | $484,315.46 | $457,380.00 | $455,280.00 | $442,680.00 |
| Purchases3 | $266,500.00 | $273,000.00 | $327,600.00 | $327,600.00 | $327,600.00 | $300,300.00 | $300,300.00 | $300,300.00 | $273,000.00 | $273,000.00 | $276,250.00 |
| Payment on purchases4 | | | $266,500.00 | $273,000.00 | $327,600.00 | $327,600.00 | $327,600.00 | $300,300.00 | $300,300.00 | $300,300.00 | $273,000.00 |
| Utilities payments5 | | | $8,400.00 | $10,080.00 | $10,080.00 | $10,080.00 | $9,240.00 | $9,240.00 | $9,240.00 | $8,400.00 | $8,400.00 |
| Wage and salary payments6 | | | $54,200.00 | $55,040.00 | $55,040.00 | $55,040.00 | $54,620.00 | $54,620.00 | $54,620.00 | $54,200.00 | $54,200.00 |
| Other fixed expense payments | | | $12,000.00 | $12,000.00 | $12,000.00 | $12,000.00 | $12,000.00 | $12,000.00 | $12,000.00 | $12,000.00 | $12,000.00 |
| Interest on notes payable | | | $4,333.00 | $4,333.00 | $4,333.00 | $4,333.00 | $4,333.00 | $4,333.00 | $4,333.00 | $4,333.00 | $4,333.00 |
| Interest on line-of-credit7 | | | $240.00 | $236.37 | $191.81 | $12.04 | $0.00 | $0.00 | $1,648.00 | $1,571.80 | $1,449.43 |
| Debt principal repayment | | | | | | | | | | | $58,000.00 |
| Dividends | | | | $50,000.00 | | | $50,000.00 | | | $50,000.00 |
| Tax payments | | | $60,000.00 | | | $60,000.00 | | | $60,000.00 |
| Capital expenditures | | | | | | | | $500,000.00 |
| Total disbursements | | | $405,673.00 | $404,689.37 | $409,244.81 | $469,065.04 | $457,793.00 | $880,493.00 | $442,141.00 | $430,804.80 | $411,382.43 |
| Net increase (decrease) in cash | | | $727.00 | $8,910.64 | $35,955.19 | $29,894.96 | $39,089.90 | ($396,177.54) | $15,239.00 | $24,475.20 | $31,297.57 |
| Beginning cash balance | | | $40,000.00 | $40,000.00 | $40,000.00 | $40,000.00 | $40,000.00 | $40,000.00 | $40,000.00 | $40,000.00 | $40,000.00 |
| Change in cash balalnce | | | $727.00 | $8,910.64 | $35,955.19 | $29,894.96 | $39,089.90 | ($396,177.54) | $15,239.00 | $24,475.20 | $31,297.57 |
| Cash balance before LOC/MS change | | | $40,727.00 | $48,910.64 | $75,955.19 | $69,894.96 | $79,089.90 | ($356,177.54) | $55,239.00 | $64,475.20 | $71,297.57 |
| Line-of-credit payment (borrowing) | | | $727.00 | $8,910.64 | $35,955.19 | $29,894.96 | $39,089.90 | ($396,177.54) | $15,239.00 | $24,475.20 | $31,297.57 |
| Line-of-credit (money market) balance | | $48,000.00 | $47,273.00 | $38,362.37 | $2,407.18 | ($27,487.79) | ($66,577.68) | $329,599.86 | $314,360.86 | $289,885.66 | $258,588.09 |
| Cash balance after LOC/MS adjustment | | $40,000.00 | $40,000.00 | $40,000.00 | $40,000.00 | $40,000.00 | $40,000.00 | $40,000.00 | $40,000.00 | $40,000.00 | $40,000.00 |
| Assumptions: |
| 1. Collections on sales equal 5% cash sales, 30% 30-day lagged, & 64% 60-day lagged (remaining 1% is uncollected) |
| 2. Interest on funds swept to money market is earned at 1% per annum |
| 3. Purchases equal 65% of expected sales of following month |
| 4. Payment on purchases lag purchases by 60 days |
| 5. Utilities payments equal 2% of current month sales revenues |
| 6. Wages and salaries equal $50,000 plus 1% of current month sales revenues |
| 7. Interest on line-of-credit is paid at 6% per annum |
khalednass
CashBudgetingExample.xlsx
