Include excel formulas

131 A B C X V fx D E B D E B C D E Actual and Budgeted Unit Sales: June July August 10Z April 1,500 103 Forecasted Income Statement 10 May 1,000 Units FORMULA1 104 For Quarter Ended August 31, 19X5 11 June O 1,600 Dollars FORMULA2 105 12 July 1,400 106 Sales FORMULA16 13 August 1,500 Unit Purchases Budget 107 Cost of goods sold FORMULA17 14 September 1,200 108 15 June July August 109 Gross profit FORMULA18 16 Balance Sheet, May 31, 19X5 110 17 Cash $8,000 Desired ending inventory FORMULA3 111 Expenses: O 18 Accounts receivable 107,800 Current month's unit sales FORMULA4 O 112 Variable expenses FORMULA19 19 Merchandise inventory 113 52,800 Fixed expenses FORMULA20 20 Fixed assets (net) 130,000 Total units needed FORMULAS 0 114 Depreciation expense FORMULA21 21 Beginning inventory FORMULAS 115 Interest expense FORMULA22 22 116 Total assets $298,600 23 Purchases (units) FORMULA7 117 Total expenses $0 24 118 Accounts payable (merchandise) $74,800 25 Owner's equity Purchases (dollars) FORMULAS 119 Net income SO 223,800 $0 SO 120 26 27 121 Total liabilities & equity $298,600 Cash Budget 122 Forecasted Balance Sheet 28 123 August 31, 19X5 29 30 Average selling price June August 124 $98 July 125 Assets: 31 Average purchase cost per unit $55 126 Cash FORMULA23 32 Desired ending inventory Cash balance, beginning $8,000 $42,760 $142,720 127 Accounts receivable FORMULA24 33 (% of next month's unit sales) 60% Cash receipts: Collections from customers: 128 Merchandise inventory FORMULA25 34 Collections from customers: 29 Fixed assets (net FORMULA26 35 From April sales 29,400 Collected in month of sale 20% From May sales 58,800 19,600 130 36 Collected in month after sale 60% From June sales 31,360 94,080 31,360 131 Total assets 37 Collected two months after sale 20% From July sales 27,440 82,320 132 38 Projected cash payments: From August sales 29,400 133 Liabilities & equity: 39 Variable expenses 30% of sales 134 Accounts payable FORMULA27 43 40 Fixed expenses (per month) $10,000 0 Total cash available $127,560 $183,880 $285,800 135 Loans payable 44 41 Depreciation per month $1,000 Cash disbursements: 136 Owner's equity FORMULA28 45 42 $0 137 Merchandise $74,800 FORMULAS 46 43 Variable expenses 41,160 44, 100 138 Total liabilities & equity 44 Answer Section: Fixed expenses 10,000 10,000 139 45 Sales Budget 140 Interest paid 46 47 June July August Total disbursements $84,800 $41,160 $54,100 48 49 Units 1,600 1,400 1,500 Cash balance before financing $42,760 $142,720 $231,700 50 Dollars $156,800 137,200 147,000 Less: Desired ending balance 8,000 8,000 8,000 51 52 Unit Purchases Budget Excess (deficit) of cash over needs $34,760 $134,720 $223,700 53 54 June July August Financing 58 55 Borrowing $0 50 $0 56 Desired ending inventory 840 900 720 59 Repayment 60 57 Current month's unit sales 1.600 1.400 1.600 MASTER +