PLEASE CHEKC THIS NOTE:

"value" - Enter number in cell

"?" - Enter either: reference to a cell or a formula in cell

M 9 A B C D E G H J L 1 Excel Lab #1 2 Prepare purchases and income statement budgets for a merchandiser 3 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. 4 2. Cost of Goods Sold is expected to be 75% of sales 5 3. Company policy is to maintain ending inventory at 10% of following month's cost of goods sold. 6 4. Operating expenses are estimated to be as follows: 7 Monthly Sales salaries 35,000 Rent Expense per month 5,000 8 Advertising (% of sales) 6% Depreciation per month 800 Delivery expense (% of sales) 2% Utilities per month 600 10 Sales Commision (% of sales) 5% Insurance per month 500 11 Interest expense per month 2,000 Income tax 30% 12 13 NOTE: "value" - Enter number in cell 14 "?" - Enter either: reference to a cell or a formula in cell 15 16 Instructions 17 (a) Prepare the merchandise purchases budget for each month in columnar form. 18 (b) Prepare budgeted income statements for each month form. Show in the 19 statements the details of cost of goods sold. 20 21 Submission: once completed 22 - save file under Lab 1: your name 23 - upload file in eLearn 24 A B D E F G . 29 30 31 32 33 34 Budgeted Sales Big Beer Store Merchandise Purchases Budget For the Months of May and June, 2017 May Value June ? 35 . 36 Budgeted cost of goods sold 37 Add: Desired ending merchandise inventory 38 Total 89 Less: Beginning merchandise inventory 40 Required merchandise purchases 41 42 ? ? ? ? ? ? ? ? ? ? ? ? A B E G H 44 45 46 June ? 50 51 52 ? ? ? ? ? ? 53 54 F Big Beer Store Budgeted Income Statement For the Months of May and June, 2017 47 May 48 Sales Revenue ? 49 Cost of goods sold Beginning inventory ? Add: Purchases ? Cost of goods available for sale ? Less: Ending inventory ? Cost of goods sold ? 55 Gross profit ? 56 Operating expenses Sales salaries ? 58 Advertising* ? Delivery** ? 60 Sales commissions*** ? 61 Rent ? Depreciation ? Utilities ? 64 Insurance ? 65 Total ? 66 Income from operations ? 67 Interest expense ? 68 Income before income taxes ? 69 Income tax expense (30%) ? 70 Net income ? 57 59 62 63 ? ? ? ? ? ? ? ? ? ? ? ? ? ? 71 72 73 *6% of sales **2% of sales. ***5% of sales. 74