Question

FORECASTING ASSIGNMENT- Peel Inc.

Assume you are the Financial Analyst for Peel Inc., and it is the end of December 2019.

Peel Inc., a manufacturing company, has been growing quickly, but it has found that its financial situation is continually under pressure. Production has fluctuated to meet demand in an attempt to provide first-class service, resulting in larger inventory positions. Also, the collection of accounts has worsened to approximately 60 days, which is well above the terms of 30 days. To address the financial concerns, Peel Inc. is forecasting level production and the impact of plans by the credit department to bring the average collection period down to 35 days.

Forecasted sales for the upcoming months are as follows:

January	$2,174,978
February	2,299,977
March	2,449,976
April	2,624,974
May	2,749,973
June	2,849,972

Sales for November were $1,924,981 and will be approximately $2,049,980 for the current month of December. It is projected that the current collection period of 60 days will be reduced to 50 days for January and February and to 42 days for March and April, and will meet the target of 35 days in May and June.

Purchases are forecast to be $590,000 a month beginning in January. In November they were $670,000, and in December they are expected to be $610,000. The purchases are paid in 40 days. Labour expense will be paid as incurred and will be $198,000 a month. Other expenses of manufacturing will also be paid as incurred and are expected to be $360,000 a month. Cost of goods sold has regularly been 70% of sales.

Amortization is $39,000 per month. Selling and administrative expenses are expected to be 13 percent of sales. The tax rate is 42 percent.

There will be payments on Notes Payable of $680,000 in each of February and May. Income taxes of $340,000 are due and paid in April. Dividends of $24,000 and interest of $270,000 (3 months of interest) are paid in January and April.

PEEL INC.

Balance Sheet (Estimated)

December 31, 2019

($ thousands)

ASSETS
Current Assets:
	Cash				$666
	Accounts Receivable				3,578
	Inventory				8,231
			Total Current Assets		$12,475
Capital Assets:
	Plant and Equipment			$11,273
	Less: Accumulated Amortization			4,784	6,489
Total Assets					$18,964
LIABILITIES AND SHAREHOLDERS’ EQUITY
Current Liabilities:
	Accounts Payable				$945
	Notes Payable				3,700
	Accrued Liabilities				2,596
		Total Current Liabilities			$7,241
	Long-term Debt				4,725
	Common Stock				4,500
	Retained Earnings				2,498
Total Liabilities and Shareholders’ Equity					$56,892

Required:

Using the information above, prepare pro forma statements for Peel Inc. for the six months ending January to June 2020. Also construct a cash budget for the six-month period and identify any need for short-term financing. There are no changes in accounts not mentioned above. Comment on the policy changes and examine the consequences if the collection period remains at 60 days. Assume capital assets are sufficient for increased sales.

Actual Expected Forecasted Nov Dec $1,924,981 $2,049,980 Jan Feb Mar Apr May Jun $2,174,978 $2,299,977 $2,449,976 $2,624,974 $2,749,973 $2,849,972 Income Statement - Jan. to Jun. $8,224,919 Sales COGS (70%) $1,347,487 $1,434,986 $1,522,485 $1,609,984 $1,714,983 $1,837,482 $1,924,981 $1,994,980 Gross Profit $577,494 $614,994 $652,493 $689,993 $734,993 $787,492 $824,992 $854,992 $39,000 $250,248 $288,247 $39,000 $266,497 $309,497 $39,000 $298,997 $351,996 $39,000 $318,497 $377,496 $39,000 $357,496 $428,495 $39,000 $370,496 $445,495 Amortization Selling and Admin (13%) Operating Profit Interest ($) Profit Before Taxes Tax (42%) Net Income Dividends To Retained Earnings $288,247 $121,064 $167,183 $309,497 $129,989 $179,508 $39,000 $282,747 $330,746 $270,000 $60,746 $25,513 $35,233 $24,000 $11,233 $351,996 $147,838 $204,158 $377,496 $158,548 $218,948 $39,000 $341,247 $407,246 $270,000 $137,246 $57,643 $79,602 $24,000 $55,602 $428,495 $179,968 $248,527 $445,495 $187,108 $258,387 $167,183 $179,508 $204,158 $218,948 $248,527 $258,387 $590,000 $198,000 Purchases $670,000 Labour (paid as incurred) $198,000 Other Manufacturing $360,000 Cost of Goods Producer $112,000 $610,000 $198,000 $360,000 $52,000 $590,000 $198,000 $360,000 $32,000 $590,000 $198,000 $360,000 $32,000 $590,000 $198,000 $360,000 $32,000 $590,000 $198,000 $360,000 $32,000 $590,000 $198,000 $360,000 $32,000 $32,000 $680,000 $680,000 $270,000 Principal Payment - Notes Interest Payments Income Tax Payments Dividend Payments $270,000 $340,000 $24,000 $24,000 Purchases From Nov Purchases From Dec Purchases From Jan Purchases
s

Answer 1

Solution:

Peel Inc. Cash Budget For the Month ended January to June January February March April May June 15,28,020 20,49,980 Particulars Cash Sales Cash Receipts Nov Cash Receipts Dec Cash Receipts Jan Cash Receipts Feb Cash Receipts Mar Cash Receipts April 21,74,978 22,99,977 24,49,976 26,24,974 Total Cash Receipts (A) 15,28,020 20,49,980 21,74,978 22,99,977 24,49,976 26,24,974 3,35,000 6,10,000 5,90,000 5,90,000 5,90,000 Cash Paid to Supplier For Nov For Dec For Jan For Feb For Mar For April Labour Expense Other Manufacturing Exp. Selling & Administrative Payments of Note Payable Income tax paid Dividend Paid Interest Paid Total Cash Payments (B) 1,98,000 3,60,000 2,82,747 1,98,000 3,60,000 2,98,997 6,80,000 1,98,000 3,60,000 3,18,497 1,98,000 3,60,000 3,41,247 1,98,000 3,60,000 3,57,496 6,80,000 5,90,000 1,98,000 3,60,000 3,70,496 3,40,000 24,000 2,70,000 20,99,247 11,99,747 21,46,997 14,66,497 21,85,496 15,18,496 Net Cash Flows (A-B) Beginning Balance of Cash 3,28,273 6,66,000 -97,017 9,94,273 7,08,481 8,97,256 2,00,730 16,05,737 2,64,480 18,06,467 11,06,478 20,70,947 Ending Cash Balance 9,94,273 8,97,256 16,05,737 18,06,467 20,70,947 31,77,425

Particulars Sales Income Statement For the month ended January to June 2020 January February March April May June 21,74,978 22,99,977 24,49,976 26,24,974 27,49,973 28,49,972 COGS (70% of Sales) 15,22,485 16,09,984 17,14,983 18,37,482 19,24,981 19,94,980 6,52,493 6,89,993 7,34,993 7,87,492 8,24,992 8,54,992 Gross Profit (A) Operating Expenses: Amortization Selling & Administrative Total Operating Expense (B) Operating Profit (A-B) Interest Expense Profit Before tax Tax @42% Net Income Dividends Retained Earnings 39,000 2,82,747 3,21,747 3,30,746 90,000 2,40,746 1,01,113' 1,39,633 24,000 1,15,633 39,000 2,98,997 3,37,997 3,51,996 90,000 2,61,996 1,10,038' 1,51,958 39,000 39,000 39,000 3,18,497 3,41,247 3,57,496 3,57,497 3,80,2473,96,496 3,77,496 4,07,246 4,28,495 90,000 90,000 90,000 2,87,496 3,17,246 3,38,495 1,20,748 1,33,243 1,42,168 1,66,748 1,84,002 1,96,327 00 0 1,66,748 1,84,002 1,96,327 39,000 3,70,496 4,09,496 4,45,495 90,000 3,55,495 1,49,308 2,06,187 1,51,958 2,06,187

Balance Sheet as at 30 June 2020 ASSETS Cash Accounts Receivable Inventory Plant & Equipment 1,12,73,000 Less: Acc. Amortization 50,18,000 31,77,425 55,99,9451 45,14,105 62,55,000 Total Assets 1,95,46,475 LIABILITIES & SHAREHOLDERS' EQUITY Acc. Payable Notes Payable Accrued Liability Accrued Taxes 11,80,000 23,40,000 28,66,000 4,16,620 Long Term Debt Common Stock Retained Earnings 47,25,000 45,00,000 35,18,855 Total Liabilities & Shareholders' Equity 1,95,46,475