Help creating Pro Forma Balance Sheet Sales Budget October November December Tot

Question

Help creating Pro Forma Balance Sheet

Sales Budget October November December Total-Qtr Cash sales 168,000 210,000 262,500 640,500 Sales on account 112,000 140,000 175,000 175,000 Total budgeted sales 280,000 350,000 437,500 1,067,500 Schedule of Cash Receipts October November December Total-Qtr Current cash sales 168,000 210,000 262,500 640,500 Plus collections from A/R -   112,000 140,000 252,000 Total collections 168,000 322,000 402,500 892,500 (Hint- these do not all just get added across!) Inventory Purchases Budget October November December Total-Qtr Budgeted cost of goods sold 224,000 280,000 350,000 854,000 Plus desired ending inventory 84,000 105,000 60,000 60,000 Inventory needed 308,000 385,000 410,000 914,000 Less beginning inventory -   84,000 105,000 -   Required purchases (on account) 308,000 301,000 305,000 914,000 check math vertically too Schedule of Cash Payments Budget for Inventory Purchases October November December Total-Qtr Payment of current month's A/P 61,600 60,200 61,000 182,800 Payment for prior month's A/P -   246,400 240,800 487,200 Total budgeted payments 61,600 306,600 301,800 670,000 Selling and Administrative Expense Budget October November December Total-Qtr Salary expense 21,500 21,500 21,500 64,500 Sales commissions expense 8,400 10,500 13,125 32,025 Supplies expense   2,800 3,500 4,375 10,675 Utilities Expense 2,300 2,300 2,300 6,900 Depreciation expense on store fixtures 8,375 8,375 8,375 25,125 Rent expense   8,000 8,000 8,000 24,000 Miscellaneous expense 1,250 1,250 1,250 3,750 Total S&A expenses 52,625 55,425 58,925 166,975 Schedule of Cash Payments for S&A Expenses (Some balances may be zero.) October November December Total-Qtr Salaries 21,500 21,500 21,500 64,500 Sales commissions -   8,400 10,500 18,900 Supplies expense 2,800 3,500 4,375 10,675 Utilities -   2,300 2,300 4,600 Depreciation on store fixtures -   -   -   -   Rent 8,000 8,000 8,000 24,000 Miscellaneous 1,250 1,250 1,250 3,750 Total payments for S&A expenses 33,550 44,950 47,925 126,425 (Hint- these do not all just get added across!) Cash Budget October November December Total-Qtr Beginning cash balance -   10,850 10,420 -   Issuance of stock 300,000 -   -   300,000 Collections from customers 168,000 322,000 402,500 892,500 Cash available 468,000 332,850 412,920 1,192,500 Less payments just a heading- no input this line    For inventory purchases 61,600 306,600 301,800 670,000    For S&A expenses 33,550 44,950 47,925 126,425    Purchase of store fixtures 450,000 -   -   450,000     Pay dividend -   -   40,000 40,000    Interest expense -   880 1,180 2,060 Total budgeted payments 545,150 352,430 390,905 1,288,485 Cash balance before borrow/repay (77,150) (19,580) 22,015 (95,985) Financing activity just a heading- no input this line    Borrowing (repayment) 88,000 30,000 (12,000) 106,000 Ending cash balance 10,850 10,420 10,015 10,015 check math vertically too BUDGETS H and I below must be completed in OWL, question 2 for grading. You can do them below, if it helps you. They will not be graded as part of the excel part of the project. Pro Forma Income Statement For the Quarter Ended December 31, Year1 Sales revenue 1,067,500 Cost of goods sold (854,000) Gross margin 213,500 S&A expenses (166,975) Operating income 46,525 Interest expense (2,060) Net income 44,465 Pro Forma Balance Sheet For the Quarter Ended December 31, Year1 Assets no input    Cash    Accounts receivable    Inventory    Store fixtures    Accumulated depreciation Total assets Liabilities no input    Accounts payable    Utilities payable    Sales commissions payable    Line of credit liability     Total liabilities Equity no input    Common stock    Retained earnings     Total equity Total liabilities and equity

Explanation / Answer

Proforma Balance Sheet as on December 31

Amount

Account receivable

450000

Total liability

Assets

Amount

Account receivable

175000 Inventory 60000 Store fixtures

450000

Accumilated depreciation 25125 Cash 10015 Total Assets 720140 Liabilities Account payable 244000 Utilities 2300 Sales commission 13125 Line of crediit 106000

Total liability

365425 Equity Common stock 300000 Retained earnings 44465 Total Equity 344465 Total Liabilities & Equity 709890

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.