Prior to building your scenarios---test your model further. Change May's revenues to $700,000. Your new June 30 ending cash balance should change to $68,370. If it doesn't, there are still errors in your model. ***** 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: Use Scenario Manager to evaluate alternatives. Ensure that May's revenues are reset back to $680,000. April Create the following scenarios in Scenario Manager: 1 - Current plan 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 Values as per original assumptions 2 -- Attend the big industry trade show in April *Administrative expenses includes $5,0 in depreciation expense each month. Revenues for the month of May and June would increase by 10% for each month. Selling expenses in April would increase by $40,000. C 3 -- Ramp up Advertising/Promotions Revenues for April, May and June would increase by 5% for each month. b. Sales each month are 20% cash sales (collected at the moment of the sale), and 80% on account 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. February's total sales were $260,000, and March's 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 month's 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%. 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 Prepare a cash budget for the third quarter, by month. Show borrowings from the company's 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. Total Interest paid over the three months Check figures: Also replace all of the Summary Report's cell reference indicators with more meaningful line item descriptions of what each amount is (this doesn't have to be done with any slick, glamorous methods...). Ensure the summary report shows ONLY the three scenarios. April amount borrowed = $7,560 June closing cash balance = $65,584