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:

Question

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

b) Consider three scenarios where inventory purchases constitute 40%, 50%, and 60% of the next month’s sales. For each one of these scenarios assume that sales will be 5% better than expected, exactly as expected, or 5% worse than expected. The CEO has asked you to use the Scenario Manager to evaluate the each scenario based on the firm’s maximum borrowing needs and cumulative net interest income.

Answer 1