Question

The Cash Budget– Extra Credit

* Would like to have answer in excel with equations shwoing

1.    The financial staff at Exotic Foods Inc., a food importer, wholesaler, and distributor, has estimated the following sales figures for the first half of 2018:

Month	Sales	Other Expenses
January	$100,000	$3,000
February	$120,000	$3,200
March	$150,000	$3,500
April	$180,000	$3,800
May	$150,000	$3,500
June	$120,000	$3,200
July	$150,000	$3,500
August	$180,000	$3,800

Actual November and December 2017 sales were $200,000 and $90,000, respectively. Cash sales are 45% of the total and the rest are on credit. About 70% of credit sales are typically collected one month after the sale and 30% the second month. Monthly inventory purchases represent 50% of the following month’s sales. The firm pays 40% of its inventory purchases in cash and the remainder in the following month. Administrative wages are expected to be 10% of the month’s sales. Commissions to sales associates are estimated to be 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. A major capital expenditure of $40,000 is expected in April and a quarterly dividend of $20,000 will be paid to shareholders in March and June. Monthly long-term debt interest expenses and maintenance expenses are $4,000 and $1,500, respectively. Sales taxes are 5% of quarterly sales and must be paid in January, April, and July, starting in January with $19,000. The firm has an ending cash balance of $20,000 for December 2017.

a)    The firm wants to maintain a minimum cash balance of at least $15,000 with a maximum of $30,000, and will pay interest on its short-term borrowings of 5%. If the firm can earn an average rate of return of 3% on short-term investments, help the financial staff of Exotic Foods Inc. to prepare a cash budget that shows interest payments on borrowed/invested funds. Note that the firm must pay off any short-term loan outstanding before any cash surplus can be invested, and invested funds should be used instead of borrowing when needed.

Answer 1