Assignment #3: Preparing Forecasted Financial Statements

(Points: 80)

Carefully read and complete the following questions. In addition to the marks indicated, you will also be graded on format and presentation (5 marks), grammar and spelling (5 marks). This assignment is worth a total of 80 marks and is 10% of your overall grade.

1. (Marks: 50) Ellis Electronics Companys actual sales and purchases for April and May are shown here, along with forecasted sales and purchases for June through September.

	Sales	Purchases
April (actual).................................	$320,000	$130,000
May (actual)..................................	300,000	120,000
June (forecast)............................	275,000	120,000
July (forecast)..............................	275,000	180,000
August (forecast)........................	290,000	200,000
September (forecast).................	330,000	170,000

The company makes 10 percent of its sales for cash and 90 percent on credit. Of the credit sales, 20 percent are collected in the month after the sale and 80 percent are collected two months after. Ellis pays for 40 percent of its purchases in the month after purchase and 60 percent two months after.

Labour expense equals 10 percent of the current months sales. Overhead expense equals $12,000 per month. Interest payments of $30,000 are due in June and September. A cash dividend of $50,000 is scheduled to be paid in June. Tax payments of $25,000 are due in June and September. There is a scheduled capital outlay of $300,000 in September.

Ellis Electronics ending cash balance in May is $20,000. The minimum desired cash balance is $15,000. Prepare a schedule of monthly cash receipts, monthly cash payments, and a complete monthly cash budget with borrowing and repayments for June through September. The maximum desired cash balance is $50,000. Excess cash (above $50,000) is used to buy marketable securities. Marketable securities are sold before borrowing funds in case of a cash shortfall (less than $15,000).

(Hint: You will have to create separate line items in your cash budget for Marketable Securities Purchased (Sold) and Cumulative Marketable Securities).

2. (Marks: 20) The Longbranch Western Wear Company has the following financial statements, which are representative of the companys historical average.

Income Statement
Sales............................................				$200,000
Expenses....................................				158,000
Earnings before interest and taxes				42,000
Interest......................................				2,000
Earnings before taxes............				40,000
Taxes..........................................				20,000
Earnings after taxes................				$ 20,000
Dividends..................................				$ 10,000
Balance Sheet
Assets			Liabilities and Shareholders Equity
Cash..............................	$ 10,000		Accounts payable...............		$ 5,000
Accounts receivable.....	10,000		Accrued wages...................		1,000
Inventory......................	15,000		Accrued taxes.....................		2,000
Current assets.............	35,000		Current liabilities................		8,000
Capital assets................	70,000		Notes payable......................		7,000
			Long-term debt....................		15,000
			Common stock (at Par) 20,000		20,00
			Paid In Capital 5,000 Retained earnings 50,000		50,000
Total assets...................	$105,000		Total Common Equity 75,000 Total liabilities and equity....		$105,000

Longbranch is expecting a 20 percent increase in sales next year, and management is concerned about the companys need for external funds. The increase in sales is expected to be carried out without any expansion of capital assets; instead, it will be done through more efficient asset utilization in the existing stores. The Dividends Payout Ratio remains unchanged and forecasted taxes are $24,400.

a). Using a percentofsales method, determine whether Longbranch Western Wear has external financing needs.

b). Prepare a pro forma income statement and balance sheet with any financing adjustment made to notes payable, i.e including the external financing needs (the plug). If external financing is not required, excess funds are first used to reduce notes payable with the difference going towards reducing long-term debt.

c). Calculate the current ratio and total debt to assets ratio for each year.