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					$18,964

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.

Answer 1

PEEL INC., Statement of Cash Budget For the period Jan 2020 to June 2020 Jan 20 Feb 20 Mar 20 S666,000 $890,432 $1,415,023 Apr 20 $2.188,734 May 20 Jun 20 $3,081,181 $3,493,222 $1,993,070 $2,659,070 $2.619,334 | S2 266,468 $3,509,766 $3,681,491 $3.008,466 $5,197,200 $2,610 872 $5,692,053 S3,180.214 $6,673,436 Beginning Balance Receipts Receipts from Accounts Receivable Total Receipts Payments Payments to Accounts Payables Labour Expenses Other Manufacturing Expenses Selling and Administrative Expenses Payments on Notes Payable Dividends Paid Income Taxes Paid Interest Paid Total Payments Ending Balance 5633 891 S198.000 $360,000 S282,747 $557.746 $198,000 $360,000 $298,997 $680,000 $616,250 $198,000 $360,000 $318,497 $582.772 $198,000 $360,000 $341,247 | $603,335 $198,000 $360,000 $357,496 $680,000 $576 665 $198,000 $350.000 $370.496 $24.000 S270.000 $1,768,638 | $890,432 $24,000 $340,000 $270,000 $2,116,019 $3,081, 181 $2,094,743 $1,415,023 $1,492,757 $2,188,734 $2,198,831 $3,493,222 $1,505,161 $5,168 275 Working Notes Computation of Collections per month Expected Collection Period for Monthly Sales Start Date End Date Period Collected on Collected on Average Daily Sales 60 31-Dec-19 29-Jan-20 6 4,166 60 30-Jan-20 29-Feb-20 66,128 50 20-Feb-20 21-Mar-20 70.161 50 22-Mar-2019-Apr-20 79,310 42 12-Apr-2012-May-20 79,031 42 13-May-2011-Jun-20 87,499 35 5-Jun-205-Jul-2088,709 3 56-Jul-204 -Aug-2094,999 Mar-20 Apr-20 May-20 Jun-20 Nov-19 1-Nov-1930-Nov-19 Dec-19 1-Dec-19 31-Dec-19 Jan-201-Jan-20 31-Jan-20 Feb-20 1-Feb-20 29-Feb-20 Mar-2011-Mar-20 31-Mar-20 Apr-201-Apr-2030-Apr-20 May-2011-May-20 31-May-20 Jun-201-Jun-2030-Jun- 20 Collection Schedule of Monthly Sales ( Days of sales collected) Jan-20 Feb-20 Nov-19 29 Dec- 192 29 Jan-20 10 Feb-201 Mar-20 Apr-20 May-20 Schedule of Monthly Collections Jan-201 F eb-20 Nov-19 1,860,814 Dec-19 132.256 1,917,724 Jan-201 701,610 Feb-201 Mar-201 Apr-20 May-20 1,993,070 2,619,334 211 T 1019 1912 19 11 25 M ar-20 Apr-20 May-20 Jun-20 1,473,358 793,100 1,506,877 1,501,589 9 48,387 1.662,485 962,489 2.217,725 3.180,214 2,266,468 3,008,466 2.610,872 Computation of Payments per month Expected Payment Period for Monthly Purchases Start Date End Date Period 401 Nov-191-Nov-19]. 30-Nov-19 Dec-19 1-Dec-19 31-Dec-19 Jan-20 1-Jan-201 31-Jan-20| Feb-2011-Feb-20 29-Feb-20 Mar-201-Mar-20131-Mar-20 Apr-2011-Apr-2030-Apr-20 May-201-May-20 31-May-20 Jun-2011-Jun-201 30-Jun-20 Payment Schedule of Monthly Purchases Jan-20 Feb-20 Nov-1919 40 40 40 40 40 40 40 Collected on Collected on Average Daily Sales 11-Dec-19 9-Jan-201 22.333 10-Jan-2019-Feb-20 1 9,677 10-Feb-20 11-Mar-20 19,032 12-Mar-2019-Apr-20 20,345 10-Apr-2010-May-20 1 9,032 11-May-209-Jun-20 19,667 10-Jun-20 10-Jul-20 19,032 11-Jul-20 9-Aug-20 19,667 Mar-20 Apr-20 May-20 Jun-20

22 91 Dec-19 Jan-20 Feb-20 Mar-201 Apr-201 May-20 Schedule of Monthly Payments 2011 2019 2110 219 11 11 21 Feb-20 Mar-20 Apr-20 May-20 Jun-20 Nov-19 Dec-19 Jan-20 Jan-20 200 997 432 894 177 106 380,640 209,350 406,900 183,100 399,672 Mar-20 Apr-201 May-20 190,328 413,007 176,993 399,672 576,665 633,891 557,746 616,250 582,772 603,335 PEEL INC., Pro Forma Income Statement For the period ended June 30.2020 Sales Cost of Goods Sold Gross Profit in $ 000) $15,150 $10,605 $4,545 Operating Expenses Labour Expenses Other Manufacturing Expenses Selling and Administrative Expenses Amortization Expenses Interest Expenses Total Expenses Net Income (Loss) Income Tax Net Income after Tax $1,188 S2 160 51,969 $234 $270 $5,821 ($1,276) $0 ($1,276) PEEL INC., Pro Forma Statement of Retained Earnings For the period ended June 30.2020 in $ 000) Beginning Balance $2,498 Add: Net Income for the Period ($1,276) Less: Dividends Ending Balance $1,174 ($48) PEEL INC. Pro Forma Statement of Financial Position For the period ended June 30.2020 (in $ 000) Current Assets Cash $5,168 Accounts Receivable $3,050 Inventory $1,166 Total Current Assets $9,384 Capital Assets Plant and Equipment Less: Accumulated Amortization Total Capital Assets Total Assets $11,273 ($5,018) $6,255 $15.639 Current Liabilities Accounts Payables Notes Payable |Accrued Liabilities $914 $2.340 $1.986

Total Current Liabilities $5,240 Long-term Debts Common Stock Retained Earnings $4,725 $4,500 $1,174 $10,399 Total Liabilities and Shareholder's Equity $15,639 Working Notes Computation of Accounts Receivable Beginning Balance Add: Sales Less: Receipts Ending Balance $3,578 $15,150 ($15,679) $3,049 Computation of Inventory Beginning Inventory Add: Purchases Less: Cost of Goods Sold Ending Inventory $8,231 S3,540 ($10,605) $1,166 Computation of Capital Assets Accumulated Amortization. 1/1/2020 Add: Amortization for the period Accumulated Amortization. 30/6/2020 $4,784 $234 $5,018 Capital Assets, 01/01/2020 Less: Accumulated Amortization Capital Assets , 30/06/2020 $11,273 ($5,018) $6,255 Computation of Accounts Payable Beginning Balance Add: Purchases Less: Payments Ending Balance $945 $3,540 $3,571 $8,056 Computation of Notes Payable Beginning Balance Less: Paid in February 2020 Less: Paid in May 2020 Ending Balance $3,700 (9680) ($680) $2,340 Computation of Accrued Liabilities Beginning Balance Less: Payment against accrued interest Payments against accrued income tax liability Ending Balance $2,556 ($270) ($340) $1,986 As evident from the memorandum financial statements and the projected cash flows, it is evident that the if the current policy is implemented, the company although incurs loss during the period, it will have sufficient cash balance after paying off it's expenses. Peel Inc can look into possibilities of making fresh investments in the undertaking in a way that results in reduction of its operating expenses. Statement of Projected Cash Budget if the collection period remains 60 days PEEL INC., Statement of Cash Budget For the period Jan 2020 to June 2020 Jan 20 Feb 20 Mar 20 Apr 20 May 20 Jun 20 Beginning Balance | S666.000 $890,432 $713,401 $1,395,635 $1.658,637 | S1,918,235 Receipts Receipts from Accounts Receivable $1,993,070 $1.917,712 $2.174,991 $2.379.021 $2,458,429 $2,537.471 Total Receipts $2,659,070 $2,808,144 $2,888,392 $3,774,656 $4,117.066 $4,455,706 Payments Payments to Accounts Payables S633 891 $557,746 $616,260 $582,772 $603,335 $576 665 Labour Expenses S198.000 $198,000 $198,000 $198,000 $198,000 $198,000

$360,000 S282,747 $360,000 $298,997 $680,000 $360,000 $318,497 $360,000 $341,247 | $360,000 $357,496 $680,000 $350.000 $370.496 $24.000 Other Manufacturing Expenses Selling and Administrative Expenses Payments on Notes Payable Dividends Paid Income Taxes Paid Interest Paid Total Payments Ending Balance $270.000 $1,768,638 $890,432 $24,000 $340,000 $270,000 $2,116,019 $1,658,637 $2,094,743 $713,401 $1,492,757 $1,395,635 $2,198,831 $1,918,235 $1,505 161 $2,950,545 Working Notes Computation of Collections per month Expected Collection Period for Monthly Sales Start Date End Date Period 60 60 60 Collected on Collected on Average Daily Sales 31-Dec-19 29-Jan-201 64,166 30-Jan-20 29-Feb-20 66,128 1-Mar-20 31-Mar-20 70,161 1-Apr-20 29-Apr-20 79,310 30-Apr-20 30-May-20 79,031 31-May-20 29-Jun-20 87,499 30-Jun-20 30-Jul-20 88,709 31-Jul-20 29-Aug-20 94,999 60 1.2 60 60 60 60 Mar 20 Apr-20 May-20 Jun-20 T 31 Nov-19 1-Nov-19 30-Nov-19| Dec-19 1-Dec-19 31-Dec-19 Jan-201-Jan-20 31-Jan-20 Feb-20 1-Feb-201 29-Feb-20 Mar-2011-Mar-20 31-Mar-20 Apr-20 1 -Apr-201 30-Apr-20 May-201-May-20 31-May-20| Jun-2011-Jun-2030-Jun-20 Collection Schedule of Monthly Sales ( Days of sales collected) Jan 20 Feb-20 Nov-19 29 Dec-19 2. 29 Jan-201 Feb-201 Mar-20 Apr-20 May-201 Schedule of Monthly Collections Jan-20 Feb-20 Nov-19 1,860.814 Dec-19 132256 1,917,712 Jan-201 Feb-20 Mar-20 Apr-201 May-20 1,993,070 1,917,712 29 11 30 1129 Mar-20 Apr-20 May-20 Jun-20 2,174,991 2.299,990 79,031 2.370,930 87,499 2.537,471 2,174,991 2,379,021 2,458,429 2.537,471 Computation of Accounts Receivable Beginning Balance Add: Sales Less: Receipts Ending Balance S3,578 $15,150 ($13,461) $5,267 If the collection period remains 60 days. the ending cash balance would be $ 2.95 million instead of the projected cash balance of $ 5.17 million If the collection period remains 60 days, the accounts receivable would be $ 5.27 million instead of the projected accounts receivable of $ 3.05 million