Question

Chapter 4, Problem 3, parts a-c

Camp and Fevurly Financial Planners have forecasted revenues for the first six months of 2017, as shown in the following table.

Month	Sales
November 2016	$44,160
December	41,400
January	23,000
February	24,840
March	27,600
April	34,960
May	36,800
June	41,400

The firm collects 70% of its sales immediately, 29% one month after the sale, and 1% are written off as bad debts two months after the sale. The firm assumes that wages and benefits paid to clerical personnel will be $8050 per month.

Commissions to sales associates average 25% of collections. Each of the two partners is paid $5,000 per month or 20% of sales, whichever is greater (MAX formula). Commissions and partner salaries are paid one month after the revenue is earned.

Rent expense for their office space is $4,025 per month, and lease expense for office equipment is $920. Utilities average $288 per month, except in May and June when they average only $173 (IF formula, MONTH formula).

The ending cash balance in December 2016 was $12,000.

Hints: do not include bad debts in your spreadsheet. The beginning cash balance is the same as the ending case balance of the prior month. Setup your spreadsheet to be cascading:

Sales

Collections:

Cash

Second Month

Total Collections

Less Disbursements:

Wages

Commission

Partner Salary

Rent Payment

Office Equipment

Utilities

Total Disbursements

Beginning Cash Balance

Collections - Disbursements

Unadjusted Cash Balance

Current Borrowing

Ending Cash Balance

Notes:

Minimum Acceptable Cash

Borrowing Necessary

1. Create a cash budget for January 2017 to June 2017, and determine the firm’s ending cash balance in each month assuming that the partners wish to maintain a minimum cash balance of $10,000. This means your Ending Cash Balance each month should never be less than $10,000, and Borrowing may be necessary (IF formula).
2. Camper and Fevurly are thinking of obtaining a line of credit from their bank. Based on their forecast for the first six months of the year, what is the minimum amount that would be necessary? Round you answer to the next highest $1,000 and ignore interest charges on short-term debt. (Hint: SUM the 6 months first and then use the RoundUp function).
3. Create three scenarios (best case, base case and worst case) assuming the revenues are 10% better than expected, exactly as expected, or 10% worse than expected. What is the maximum that the firm would need to borrow to maintain its minimum cash balance in all three cases?

What is the cost of Utilities in May? $173 $4,025 $920 $288

What is the Ending Cash Balance in February (assuming there was borrowing to maintain a minimum cash balance)?

		-$6,435
		-$6,252
		$10,000
		$3,749

Camper and Fevurly are thinking of obtaining a line of credit from their bank. Based on their forecast for the first six months of the year, what is the minimum amount that would be necessary? Round you answer to the next highest $1,000 and ignore interest charges on short-term debt. (Hint: RoundUp function).

		$20,000
		$10,184
		$10,000
		$17,000

reate three scenarios (best case, base case and worst case) assuming the revenues are 10% better than expected, exactly as expected, or 10% worse than expected. What is the maximum that the firm would need to borrow to maintain its minimum cash balance in all three cases?

		Base: $14,939; Best: $19,395; Worst: $11,194
		Base: $19,209; Best: $16,395; Worst: $23,848
		Base: $20,000; Best: $17,000; Worst: $24,000
		Base: $10,184; Best: $10,074; Worst: $10,294

Answer 1

Camp and Fevurly Financial Planner
Cash Budget
For the Period Jan 2017 to June 2017
		November	December	January	February	March	April	May	June
Sales	100%	$      44,160	$      41,400	$      23,000	$      24,840	$      27,600	$      34,960	$      36,800	$      41,400
Collections:
Cash	70%	$      30,912	$      28,980	$      16,100	$      17,388	$      19,320	$      24,472	$      25,760	$      28,980
Second Month	29%	$               0	$      12,806	$      12,006	$        6,670	$        7,204	$        8,004	$      10,138	$      10,672
Total Collections			$      41,786	$      28,106	$      24,058	$      26,524	$      32,476	$      35,898	$      39,652
Less Disbursements:
Wages				$        8,050	$        8,050	$        8,050	$        8,050	$        8,050	$        8,050
Commission	25%			$      10,247	$        5,693	$        6,148	$        6,831	$        8,653	$        9,108
Partner Salary	20%			$      16,394	$      10,000	$      10,000	$      10,930	$      13,844	$      14,573
Rent Payment				$        4,025	$        4,025	$        4,025	$        4,025	$        4,025	$        4,025
Office Equipment				$           920	$           920	$           920	$           920	$           920	$           920
Utilities				$           288	$           288	$           288	$           288	$           173	$           173
Total Disbursements				$      39,924	$      28,976	$      29,431	$      31,044	$      35,665	$      36,849
Beginning Cash Balance				$      12,000	$      10,000	$      10,000	$      10,000	$      11,432	$      11,666
Collections - Disbursements				$      28,106	$      24,058	$      26,524	$      32,476	$      35,898	$      39,652
Less: Disbursements				$      39,924	$      28,976	$      29,431	$      31,044	$      35,665	$      36,849
Unadjusted Cash Balance				$           182	$        5,083	$        7,093	$      11,432	$      11,666	$      14,469
Current Borrowing				$        9,818	$        4,918	$        2,907	$              -	$              -
Ending Cash Balance			$      12,000	$      10,000	$      10,000	$      10,000	$      11,432	$      11,666	$      14,469

A B с D E F H March November 44160 December 41400 January 23000 February 24840 April 34960 May 36800 June 41400 27600 =C21*$B$23 =B21"$B$24 =D21$B$23 =C21$B$24 =SUM(D23:D24) =E21 $B$23 =D21*$B$24 =SUM(E23:E24) =F21"$B$23 =E21 $B$24 =SUM(F23:F24) =G21"$B$23 =F21*$B$24 =SUM(G23:624) =H21.$B$23 =G21"$B$24 =SUM(H23:H24) =121*$B$23 =H21"$B$24 =SUM(123:124) =J21$B$23 =121 $B$24 =SUM(123:124) 16 Hints: do not include bad debts in your spre 17 Camp and Fevurly Financial Planner 18 Cash Budget 19 For the Period Jan 2017 to June 2017 20 20 21 Sales 1 22 Collections: 23 Cash 0.7 22 24 Second Month 0.29 25 Total Collections 26 Less Disbursements: 27 Wages 20 28 Commission 0.25 20 29 Partner Salary 0.2 20 30 30 31 Office Equipment 2 32 Utilities 33 Total Disbursements 2 34 35 Beginning Cash Balance 36 Collections - Disbursements 37 l Less: Disbursements 38 Unadjusted Cash Balance 39 Current Borrowing 40 Ending Cash Balance Rent Payment 8050 8050 8050 8050 8050 8050 =D21*99% $B$28 =E21*99%*$B$28 =F21*99%*$B$28 =G21.99%$B$28 =H21*99%*$B$28 =121*99%*$B$28 =MAX(5000,021*99%*$B$2 =MAX(5000, E21*99%*$B$2 =MAX(5000,F21*99%*$B$2=MAX(5000, G21*99%*$B$: =MAX(5000, H21*99%*$B$2 =MAX(5000,121*99%*$B$2 4025 4025 4025 4025 4025 4025 920 920 920 920 920 920 288 288 288 288 173 173 =SUM(E27:E32) =SUM(F27:532) =SUM(G27:32) -SUM(H27:H32) =SUM(127:132) =SUM(J27:32) 12000 =E25 =E33 =E35+E36-E37 =10000-E38 =E38+E39 =E40 =F25 =F33 =F35+F36-F37 =10000-F38 =F38+F39 =F40 =G25 =G33 =G35+G36-G37 =10000-G38 =G38+G39 =G40 =H25 =H33 =H35+H36-H37 0 =H38+H39 =H40 =125 =133 =135+136-137 0 =138+139 =140 =J25 =J33 =J35+)36-J37 12000 =J38+)39