60.1K

Verified Solution

Link Copied!

Cash Flow Budgeting / Scenario Manager

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:

1. 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.

2. Sales each month are 20% cash sales (collected at the moment of the sale), and 80% on account.
3. 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.
4. 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.
5. 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.
6. Administrative expenses (net of Depreciation) are paid in the month incurred.
7. Equipment costing $45,000 will be purchased for cash in June.
8. The cash balance at March 31 is $41,600; the company must maintain a cash balance of at least $32,000 at all times.
9. 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 third 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.

Answer & Explanation Solved by verified expert

See Answer