Instructions: - In the Excel spreadtheet keep the problem data (budget assumptions) separate in one section of the spreadnheet. This is to be able to link the budget formulas so that the master budgets do not have to be recreated when the data is manipulatod. - Then link the data using Excel formulas to dovelop the following budgets and schedules for the three months (Apni, May, June) and in total for Ouarter 2 (O2) You may have to keep an extra column for March and July as I did in class. 1. Schedule 1: Sales budget for (April, May, June) and Total for 02 . Also show how much is cash sales and how much are credit sales. 2 2. Schedule 2: Merchandise Purchase Budget (April, May, June) and Total for Q2 3. Schedule 3: Selling and Administration Budget (April, May, June) and Total for Q2 4. Budgeted Income Statement in total for the quarter (Total for 02) 5. Sensitivity Analysis 5(a) Change the Gross Margin from 25% to 30%. Print the Budgeted Income Statement in total for the quarter (Total for Q2) 5(b) Change the gross margin from 25% to 35% and increase sales commission from 12% to 15% and other expenses from 6% to 10%. Print the Budgeted Income Statement in total for the quarter (Total for Q2) Printouts to be submitted for grading 1. For questions 1 through 4, printout Schedule 1, Schedule 2 Schedule 3 and the quarterly income statement. 2. For 5(a) Print the Budgeted Income Statement in total for the quarter (Total for Q2) 3. For 5(b) Print the Budgeted Income Statement in total for the quarter (Total for 02) a. The gross margin is 25% of sales 1. Actual and budgeted sales data are as follonw? 6. Sules are 60% for eash and 40% on credit. Credir sales are collected in the month followise wie The accounts receivable at March 31 are the result of Mtutch crefis wiex. 2 Each month's ending inventory should equal sorf of the following noebri bidineteit ent of goods sold. c. One-half of a month's inventory purchases is paid for in the moeit of purchase; the other voe-taif chaces of inventory. per month and includes depreciation on new arsets: 1. Management would like to maintail a minimum cas bahace of $4.000 at the end of each inonith. Tie coupany has an aprecenent with a local hank that allows the company to boenus as aceled if the beginting of exch month, up to a total loan balance of $39,000. The inferest nate on these loam is 18 per month and for simplicity we will assume that interes is noc compounded. The coovpany Requint: Saig the pereding dath, complele the following: I Scholule of expected cash collections: -Merchandise purchases: 2 Comnlete the following cash budget: 4. Prepare an absorption costing income statement, similar to the one shown in Schedule 9, is quarter ended June 30. 5. Prepare a bulance shoct as of tune 30
