1. (The Cash Budget) The financial staff at Safe-Life Systems, Inc., an alarm systems manufacturer, has estimated the following sales and other expenses figures for the second half of 2016:

	July	August	September	October	November	December	January
Sales	$70,000	$90,000	$120,000	$150,000	$120,000	$100,000	$80,000
Other exp.	$3,500	$4,000	$4,500	$5,000	$4,500	$4,000

The firm sells 60% on cash and collects 39% of the remainder one month after the sale since 1% are written off as bad debts. The estimated beginning receivables and payables are $40,000 and $30,000 respectively. Purchases are estimated to represent 55% of the next months sales. The firm pays 40% of its purchases in cash and the remainder during the following month. Commissions to sale associates represents 15% of collectable sales, but the firm has decided to include a bonus of 5% more if the sales of the current month are higher than the previous one. Each of the two partners is paid 20% of the average total sales of the previous and current month, plus a 5% bonus if the sales of the current month are higher than the previous one. Monthly wages, rent, and lease expenses are $5,000; $3,000, and $1,500 respectively. The firm has an ending cash balance of $25,000 for June 2016.

Note: To simplify the problem, the interest rates for both borrowing and investing are not considered as in the simple cash budget worksheet in Chapter 3.

A. Create a cash budget for July to December 2016, and determine the firms ending cash balance in each month assuming that the firm has a required minimum monthly cash balance of $25,000.

B. The partners are considering getting a line of credit from a commercial bank. Determine the minimum amount that would be necessary for July to December 2016.

C. Consider three scenarios. In the first scenario, monthly revenues are 5% better than expected, but cash sales are 40% of forecasted revenue, while credit sales are 58% and 2% are written off as bad debts. In the second scenario, sales are exactly as expected and the original collection policy is implemented. In the last scenario, sales are 5% worse than expected, but cash sales are 70% of forecasted revenue, while credit sales account for the remaining 30% with no bad debts. Use the Scenario Manager to determine the minimum amount that the firm would need to borrow to maintain its minimum cash balance in all three scenarios.

D. The formulas used in the textbook to calculate current borrowing (page 92) and current investing (page 96) are very complicated. Suggest an alternative solution or two to reduce the complexity of these formulas. Show your work in Excel and the logic in Word.