* AutoSave off) H ~ ~ ~ Chapter 22 Excel (1) - Protected View . Saved to this PC v Search File Home Insert Page Layout Formulas Data Review View Automate Help Comments Share PROTECTED VIEW Be careful-files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Editing C5 viXV fx Excel formulas must be used. Please be sure to submit your file as an Excel file. Any submissions in .numbers format will receive a 0. Chapter 22 Excel Student Name Data Section Actual and Budgeted Unit Sales: April 1,500 May 1,000 June 1.600 July 1,400 August 1,500 September 1.200 Balance Sheet, May 31, 19X5 Cash $8,000 Accounts receivable 07,800 Merchandise inventory 52,800 Fixed assets (net) 130.000 Total assets $298.600 Accounts payable (merchandise) $74,800 Owner's equity 23.800 Total liabilities & equity $298.600 Average selling price $98 Average purchase cost per unit $55 Desired ending inventory (% of next month's unit sales) 60% Collections from customers: Collected in month of sale 20% Collected in month after sale 60% Collected two months after sale 20% Projected cash payments: Variable expenses 30% of sales Fixed expenses (per month) $10,000 depreciation per month $1,000 Answer Section: Sales Budget June July August Units FORMULA1 oo Dollars FORMULAZ Unit Purchases Budget June July August Desired ending inventory FORMULA3 Current month's unit sales FORMULA Total units needed FORMULAS Beginning inventory FORMULA6 Purchases (units) FORMULA7 Purchases (dollars) FORMULA8 SO Cash Budget June July August Cash balance. beainning $8 000 $42 760 $142. 720 MASTER* AutoSave O off) H ~ ~ ~ Chapter 22 Excel (1) - Protected View . Saved to this PC v Search File Insert Page Layout Formulas Data Review View Automate Help Comments | Share PROTECTED VIEW Be careful-files from the Internet can contain viruses, Unless you need to edit, it's safer to stay in Protected View. Enable Editing C5 viXV fx Cash Budget June July August Cash balance, beginning $8,000 $42,760 $142,720 Cash receipts: Collections from customers: From April sales 29,400 From May sales 58,800 19,600 From June sales 31,360 94,08 31,360 From July sales 27,440 82,320 From August sales 29.400 Total cash available $127,560 $183,880 $285,800 Cash disbursements: Merchandise $74,800 FORMULA8 $0 Variable expenses 0 41.160 44, 100 Fixed expenses 10,000 10.000 Interest paid Total disbursements $84.800 $41,160 $54, 100 Cash balance before financing 42,760 $142,720 $231,700 Less: Desired ending balance 8.000 8,000 8,000 Excess (deficit) of cash over needs $34.760 $134,720 $223,700 Financing Borrowing $0 repayment 0 Total effects of financing SO SO SO Cash balance, ending $42,760 $142,720 $231.700 Forecasted Income Statement For Quarter Ended August 31, 19X5 Sales FORMULA 16 Cost of goods sold FORMULA17 Gross profit FORMULA18 Expenses: Variable expenses FORMULA1 Fixed expenses FORMULA20 Depreciation expense FORMULA21 Interest expense FORMULA22 Total expenses SO Net income $0 Forecasted Balance Sheet August 31, 19X5 Assets Cash FORMULA23 Accounts receivable FORMULA24 Merchandise inventory FORMULA25 Fixed assets (net) FORMULA26 Total assets Liabilities & equity: Accounts payable FORMULA27 Loans payable 0 Owner's equity FORMULA28 Total liabilities & equity MASTER +