Build the Budget model: Garden Sales, Inc. sells garden supplies. Management is planning its cash needs for the upcoming second quarter (April, May, June). The following information has been assembled to assist in preparing a cash flow budget for the quarter: a. Budgeted monthly income statements for April to July are as follows: April May June July Sales $ 480,000 $ 680,000 $ 400,000 $ 320,000 Cost of goods sold 336,000 476,000 280,000 224,000 Gross margin 144,000 204,000 120,000 96,000 Less: Operating expenses: Selling expense 63,200 96,000 49,600 40,800 Administrative expense* 36,000 41,600 32,800 30,400 Total operating expenses 99,200 137,600 82,400 71,200 Net income $ 44,800 $ 66,400 $ 37,600 $ 24,800 ________________________________________ *Administrative expenses includes $5,000 in depreciation expense each month. b. Sales each month are 20% cash sales (collected at the moment of the sale), and 80% on account. c. Sales on account are collected over a three-month period in the following ratio: 10% collected in the month of sale, 70% collected in the month following the month of sale, and the remaining 20% collected two months after the sale. Februarys total sales were $260,000, and Marchs sales totalled $340,000. d. Cost of Goods Sold (which are 70% of each months Sales) are paid for within 15 days. Therefore, 50% of a months COGS purchases are paid for in the month incurred. The remaining 50% are paid in the following month. March COGS were $238,000. e. Eighty percent (80%) of Selling Expenses are paid for in the month incurred, and the remainder in the following month. March Selling Expense was $75,000. f. Administrative expenses (net of Depreciation) are paid in the month incurred. g. Equipment costing $45,000 will be purchased for cash in June. h. The cash balance at March 31 is $41,600; the company must maintain a cash balance of at least $32,000 at all times. i. The company can borrow from its bank, as needed, to bolster the cash account. Interest is paid monthly on any balance owing from a previous month. The annual interest rate is 12%. Prepare a cash budget for the second quarter, by month. Show borrowings from the companys bank and repayments to the bank, as needed, to maintain the minimum cash balance. Ensure that your COGS amounts are based on being 70% of revenues. Your budget needs to automatically calculate amounts borrowed, repaid and interest repaid each month as applicable, based on assumptions changing. Check figures: April amount borrowed = $7,560 June closing cash balance = $65,584 ****** Prior to building your scenarios---test your model further. Change Mays revenues to $700,000. Your new June 30 ending cash balance should change to $68,370. IF it doesnt, there are still errors in your model. ****** Use Scenario Manager to evaluate alternatives. Ensure that Mays revenues are reset back to $680,000. Create the following scenarios in Scenario Manager: 1 Current plan Values as per original assumptions 2 -- Attend the big industry trade show in April Revenues for the month of May and June would increase by 10% for each month. Selling expenses in April would increase by $40,000. 3 -- Ramp up Advertising/Promotions Revenues for April, May and June would increase by 5% for each month. Selling Expenses in April, May and June would increase by $1,500 for each month. Create a Scenario Summary Report. Have that report provide the following information as result cells: Ending Cash Balance, June 30 Amount of bank loan at June 30 Total Interest paid over the three months Also replace all of the Summary Reports cell reference indicators with more meaningful line item descriptions of what each amount is (this doesnt have to be done with any slick, glamorous methods). Ensure the summary report shows ONLY the three scenarios. In excel sheet