Question

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

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

Answer:

Cash Budget
For the period from January to June ,2019
Description	January	Feburary	March	April	May	June
Receipts:
Cash Sales	45,000	54,000	67,500	81,000	67,500	54,000
Collection From Customers	45,000	54,000	67,500	81,000	67,500	54,000
Total	90,000	108,000	135,000	162,000	135,000	108,000
Payments:
Cash Purchase	24,000	30,000	36,000	30,000	24,000	30,000
Payment to suppliers	30,000	36,000	45,000	54,000	45,000	36,000
Administrative Wages	10,000	12,000	15,000	18,000	15,000	12,000
Other Expenses	3,000	3,200	3,500	3,800	3,500	3,200
Sales Commision	15,000	18,000	22,500	27,000	22,500	18,000
Interest Expenses	4,000	4,000	4,000	4,000	4,000	4,000
Maintanance Expenses	1,500	1,500	1,500	1,500	1,500	1,500
Capital Expenditures	0	0	0	50,000	0	0
Payment of Dividend	0	0	22,000	0	0	22,000
Total	87,500	104,700	149,500	188,300	115,500	126,700
Net Cash Flow	2,500	3,300	-14,500	-26,300	19,500	-18,700
Opening Cash Balance	20,000	15,000	0	0	15,000	0
Total Cash Balance	22,500	3,300	-14,500	-26,300	34,500	-18,700
Cash Withdrawal For Maintaining Minimum Balance	7,500	0	0	0	19,500	0
Cash Bought From Temporary Borrowing	0	11,700	29,500	41,300	0	33,700
Closing Cash Balance	15,000	15,000	15,000	15,000	15,000	15,000

Working Notes:

Calculation of Cash Sales and Collection From Customers
Description	November	December	January	Feburary	March	April	May	June
Total Sales	200,000	90,000	100,000	120,000	150,000	180,000	150,000	120,000
Cash Sales (45% of Current Month Sales)	90,000	40,500	45,000	54,000	67,500	81,000	67,500	54,000
Credit Sales	110,000	49,500	55,000	66,000	82,500	99,000	82,500	66,000
Collection From Customers:
In the Next month (70% of previous month sales)			34,650	38,500	46,200	57,750	69,300	57,750
In the 2nd next month (30% of preceeding month of above sales)			33,000	14,850	16,500	19,800	24,750	29,700
Total Collection			67,650	53,350	62,700	77,550	94,050	87,450

Calculation of Cash Purchase and Payment to Suppliers
Description	December	January	Feburary	March	April	May	June	July
Total Sales	90,000	100,000	120,000	150,000	180,000	150,000	120,000	150,000
Total Purchase (50% of Following Months Sales)	50,000	60,000	75,000	90,000	75,000	60,000	75,000	75,000
Cash Purchases (40% of the total purchase)	20,000	24,000	30,000	36,000	30,000	24,000	30,000	30,000
Payment to Suppliers (60 % in the following)		30,000	36,000	45,000	54,000	45,000	36,000	45,000

Payment of Administrative Wages
Description	January	Feburary	March	April	May	June
Total Sales	100,000	120,000	150,000	180,000	150,000	120,000
Payment of Administrative Wages (10% of Current Month sales)	10,000	12,000	15,000	18,000	15,000	12,000

Payment of Sales Commission
Description	January	Feburary	March	April	May	June
Total Sales	100,000	120,000	150,000	180,000	150,000	120,000
Payment of Sales Commission (10% of sales)	15,000	18,000	22,500	27,000	22,500	18,000