undefined

Excel Lab #1 Prepare purchases and income statement budgets for a merchandiser 1. Sales for May is expected to be $900,000. Sales in June and July are expected to be 10% higher than the preceding month. 2. Cost of Goods Sold is expected to be 75% of sales 3. Company policy is to maintain ending inventory at 10% of following month's cost of goods sold. 4. Operating expenses are estimated to be as follows: Monthly Sales salaries 35,000 Rent Expense per month 5,000 Advertising (% of sales) 6% Depreciation per month 800 Delivery expense (% of sales) 2% Utilities per month 600 Sales Commision (% of sales) 5% Insurance per month 500 Interest expense per month 2,000 Income tax 30% NOTE: "value" - Enter number in cell "?" - Enter either: reference to a cell or a formula in cell Instructions (a) Prepare the merchandise purchases budget for each month in columnar form. (b) Prepare budgeted income statements for each month in columnar form. Show in the statements the details of cost of goods sold. Big Beer Store Merchandise Purchases Budget For the Months of May and June, 2017 May $ 900,000 June $990,000 Budgeted Sales $ 675,000 Budgeted cost of goods sold Add: Desired ending merchandise inventory Total Less: Beginning merchandise inventory Required merchandise purchases ? ? ? ? ? ? June ? ? ? ? ? ? ? Big Beer Store Budgeted Income Statement For the Months of May and June, 2017 May Sales Revenue ? Cost of goods sold Beginning inventory ? Add: Purchases ? Cost of goods available for sale ? Less: Ending inventory ? Cost of goods sold ? Gross profit ? Operating expenses Sales salaries 35,000 Advertising* ? Delivery** ? Sales commissions ? Rent 5,000 Depreciation 800 Utilities 600 Insurance 500 Total ? Income from operations ? Interest expense ? Income before income taxes ? Income tax expense (30%) ? Net income ? *** 35,000 ? ? ? ? ? ? ? ? ? ? ? ? ? *6% of sales **2% of sales. ***5% of sales