Cash Budgeting Exercise
PDC Company March April May June July Aug. Sept. Oct. Nov. Dec. Jan. Change Cash Flow
[Spreadsheet Projections for Instructors]
Schedule 1: Sales Forecast 92,000 115,000 184,000 138,000 115,000 92,000 80,000 100,000 130,000 160,000 100,000
Credit sales, 40% 36,800 46,000 73,600 55,200 46,000 36,800 32,000 40,000 52,000 64,000
Cash sales, 60% 55,200 69,000 110,400 82,800 69,000 55,200 48,000 60,000 78,000 96,000
Schedule 2: Cash Collections
Cash sales this month 69,000 110,400 82,800 69,000 55,200 48,000 60,000 78,000 96,000
Schedule 4: Disbursements for Purchases
50% of last month’s purchases 38,640 59,570 51,520 41,860 33,810 28,840 33,600 43,400 53,900
50% of this month’s purchases 59,570 51,520 41,860 33,810 28,840 33,600 43,400 53,900 39,200
Disbursements for merchandise 98,210 111,090 93,380 75,670 62,650 62,440 77,000 97,300 93,100
Schedule 5: Wages and Commissions
Wages, all fixed 5,750 5,750 5,750 5,750 5,750 5,750 5,750 5,750 5,750 5,750
Commissions (15% of current sales) 13,800 17,250 27,600 20,700 17,250 13,800 12,000 15,000 19,500 24,000
Total 19,550 23,000 33,350 26,450 23,000 19,550 17,750 20,750 25,250 29,750
Merchandise 98,210 111,090 93,380 75,670 62,650 62,440 77,000 97,300 93,100
Total effects of financing 30,935 -3,335 -21,620 -6,968 0 0 0 0 0
Cash balance 23,000 23,000 23,000 29,487 37,562 32,672 18,822 5,422 20,222
Budgeted Income Statements
Net income -2,760 -460 5,976 1,419 -563 -2,760 -3,960 -1,960 1,040 4,040
Balance Sheets
Current Assets:
Cash 23,000 23,000 23,000 23,000 29,487 37,562 32,672 18,822 5,422 20,222
Accounts receivable 36,800 46,000 73,600 55,200 46,000 36,800 32,000 40,000 52,000 64,000 -9,200 Use of Cash
Cash Budgeting Exercise
Net PPE 55,660 61,410 60,260 59,110 57,960 56,810 55,660 54,510 53,360 52,210
Total Assets 230,000 283,130 283,360 250,470 233,267 223,812 223,712 233,052 246,842 238,432
Current Liabilities
Accounts payable 38,640 59,570 51,520 41,860 33,810 28,840 33,600 43,400 53,900 39,200 -4,830 Use of Cash
Accrued wages and commissions payable 9,775 11,500 16,675 13,225 11,500 9,775 8,875 10,375 12,625 14,875 1,725 Source of Cash
Loan 0 30,935 28,064 6,865 0 0 0 0 0 0 No 4 Month Effect
Total Current Liabilities 48,415 102,005 96,259 61,950 45,310 38,615 42,475 53,775 66,525 54,075 115 Total Balance Sheet Cash Impact
PDC Company March April May June July Aug. Sept.
Schedule 1: Sales Forecast 92,000 115,000 184,000 138,000 115,000 92,000 160,000
Credit sales, 40% 36,800 46,000 73,600 55,200 46,000 36,800
Cash sales, 60% 55,200 69,000 110,400 82,800 69,000 55,200
Schedule 2: Cash Collections
Cash sales this month 69,000 110,400 82,800 69,000 55,200
100% of last month’s credit sales 36,800 46,000 73,600 55,200 46,000
Total Collections 105,800 156,400 156,400 124,200 101,200 S
50% of this month’s purchases 59,570 51,520 41,860 33,810 51,240
Disbursements for merchandise 98,210 111,090 93,380 75,670 85,050
Schedule 5: Wages and Commissions
Wages, all fixed 5,750 5,750 5,750 5,750 5,750 5,750
Commissions (15% of current sales) 13,800 17,250 27,600 20,700 17,250 13,800
Total 19,550 23,000 33,350 26,450 23,000 19,550
Schedule 6: Disbursements-Wages/Comm
50% of last month’s expenses 9,775 11,500 16,675 13,225 11,500
50% of this month’s expenses 11,500 16,675 13,225 11,500 9,775
Cash disbursements: 0 0 0 0
Merchandise 98,210 111,090 93,380 75,670 85,050
Wages and commissions 21,275 28,175 29,900 24,725 21,275
Miscellaneous expenses 5,750 9,200 6,900 5,750 4,600
Rent 4,600 4,600 4,600 4,600 4,600
Truck purchase 6,900 0 0 0 0
Total disbursements 136,735 153,065 134,780 110,745 115,525
Minimum cash balance desired 23,000 23,000 23,000 23,000 23,000
Total cash needed 159,735 176,065 157,780 133,745 138,525
Excess of total cash -30,935 3,335 21,620 13,455 -7,838
Financing 0 0 0 0
Sales 92,000 115,000 184,000 138,000 115,000 92,000
COGS -64,400 -80,500 -128,800 -96,600 -80,500 -64,400
Gross Margin 27,600 34,500 55,200 41,400 34,500 27,600
Operating Expenses 0 0 0 0 0
Wages and commissions -19,550 -23,000 -33,350 -26,450 -23,000 -19,550
Balance Sheets
Total Current Assets 174,340 221,720 223,100 191,360 175,307 197,240
Plant
Equipment, fixtures and other 85,100 92,000 92,000 92,000 92,000 92,000
Accumulated depreciation -29,440 -30,590 -31,740 -32,890 -34,040 -35,190
Net PPE 55,660 61,410 60,260 59,110 57,960 56,810
Total Assets 230,000 283,130 283,360 250,470 233,267 254,050
Current Liabilities
Accounts payable 38,640 59,570 51,520 41,860 33,810 51,240
Accrued wages and commissions payable 9,775 11,500 16,675 13,225 11,500 9,775
Loan 0 30,935 28,064 6,865 0 7,838
Total Current Liabilities 48,415 102,005 96,259 61,950 45,310 68,853
Owner’s equity 181,585 181,125 187,101 188,520 187,957 185,197
Total equities 230,000 283,130 283,360 250,470 233,267 254,050
Accounting Statement of Cash Flows
Cash Flows From Activities
Net Income -460 5,976 1,419 –563 -2,760
Adjustments to Net Inc for CF
Depreciation Expense 1,150 1,150 1,150 1,150 1,150
Decrease in Prepaids 460 460 460 460 460
Increase in Accrued Liab. 1,725 5,175 -3,450 -1,725 -1,725
Decrease in Inventory -38,640 25,760 12,880 12,880 -38,080
Decrease in A/P 20,930 -8,050 -9,660 -8,050 17,430
Increase in A/R -9,200 -27,600 18,400 9,200 9,200
Decrease in Def. Inc. Tax 0 0 0 0 0
Total Adjustments -23,575 -3,105 19,780 13,915 -11,565
Net Cash Flow From Opps (Acct.) -24,035 2,871 21,199 13,352 -14,325
Cash Flows From Investing
Capex -6,900 0 0 0 0
Net Cash Used by Ops/Investments -30,935 2,871 21,199 13,352 -14,325
Cash Flows from Financing
Equity Issues 0 0 0 0 0
Dividends 0 0 0 0 0
Debt Issues 30,935 -2,871 -21,199 -6,865 7,838
Net Cash Flows from Financing 30,935 -2,871 -21,199 -6,865 7,838
Net Decrease in Cash and Cash Equiv. 0 0 0 6,487 -6,487
Beginning Cash Balance 23,000 23,000 23,000 23,000 29,487
Ending Cash Balance 23,000 23,000 23,000 29,487 23,000