Question

Problem #1: Must provide Excel Spreadsheet Answer. Make sure every cell that can be driven by a formula has a formula.

The financial staff at Toledo, Inc. has estimated the following sales figures for the first half of 2019: Month Sales Other Expenses

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 2018 sales were $200,000 and $90,000, respectively. Cash sales are 45% of the total and the rest are on credit. About 70% of total 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 current month’s sales. Commissions to sales associates are estimated to be 15% of sales. A major capital expenditure of $50,000 is expected in April and a quarterly dividend of $22,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. The firm has an ending cash balance of $20,000 for December 2018.

The firm must maintain a minimum cash balance of at least $15,000.

Construct a complete Cash Budget for Toledo, Inc. for the first half of 2019 (January to June).

Answer 1

Relavant workings are attached below:-