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 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 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 Februarys total sales were $260,000, and Marchs sales totalled $340,000 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 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 Administrative expenses (net of Depreciation) are paid in the month incurred Equipment costing $45,000 will be purchased for cash in June The cash balance at March 31 is $41,600 the company must maintain a cash balance of at least $32,000 at all times 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

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Aug 10, 2024

Cash Flow Budgeting / Scenario Manager Build the Budget model: Garden Sales, Inc. sells garden supplies. Management is planning its cash needs for the upcoming

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:

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.

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. Februarys total sales were $260,000, and Marchs sales totalled $340,000.
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.
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.
Administrative expenses (net of Depreciation) are paid in the month incurred.
Equipment costing $45,000 will be purchased for cash in June.
The cash balance at March 31 is $41,600; the company must maintain a cash balance of at least $32,000 at all times.
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.