The XYZ Manufacturing Company was very satisfied with their xxx1 performance. The company had only begun operations the prior year, and as a result, sales had been slow. However, things had picked up considerably in xxx1 with sales more than doubling in volume. XYZ had changed none of its business practices during the year and had managed to hold its cost of goods sold to 78% of sales. All sales were made on credit, and payment was required within 30 days of the sale. The company paid for its purchases in 30 days and maintained a cash balance of 20% in sales. The only thing that had changed during the year was the companys inventory turnover, which had jumped from 3 to 6. While XYZs management was enthusiastic about the companys increased level of sales, it was concerned about the possible necessity of increasing its short-term debt. In order to assess the situation, the company was in the process of completing the financial statements shown below. (Inv. Turn = C.O.G.S/Inv.) Complete the forms given. For xxx1, use short-term Notes Payable as the balancing account (a plug). xxxo xxx1 Income Statement: Sales 1,500,000 3,060,000 Cost of goods sold 1,170,000 Gross margin 330,000 Other expenses 240,000 240,000 Profit before tax 90,000 Taxes @ 40% 36,000 Profit after tax 54,000 Balance sheet: Cash 300,000 Accounts receivable 123,288 Inventory 390,000 Total current assets 813,288 Net property, plant and equipment 480,000 456,000 Total assets 1,293,288 Accounts payable 96,162 Short-term Notes Payable 183,126 Total current liabilities 279,288 Long-term debt 360,000 360,000 Common stock 600,000 600,000 Retained earnings 54,000 Total long-term debt and equity 1,014,000 Total liabilities and equity 1293,288 Net working capital: Current assets Current liabilities Net working capital 1. Compare ROE, ROA, EM, PM, A/R turnover, inventory turnover, fixed asset turnover for the years xxxo and xxx1. 2. Build the common size income statements for years xxxo and xxx1. 3. What is your assessment of XYZ Manufacturing Company based on the figures obtained in questions 1 and 2? Will the company need to raise external funds (EFN or AFN ) in xxx1, and how much? 4. Comptele the statements for xxx1 assuming that cost of goods sold is 83%, that the company maintains cash balance of 15% of sales, everything else remains as stated in the problem. And redo questions 1, 2 and 3 for this new scenario. Accounts Receivable and Accounts Payable 1. The problem specifies that it takes the firm 30 days to collect its sales. This means that the days of sales outstanding (DSO), sometimes also called average collection period (ACP), equal 30 days. The ratio that measures DSO or ACP = A.R./(credit Sales/360). You use this ratio to find A.R. (accounts receivable). This problem assumes that all sales are made on credit. 2. The problem also says that the firm takes 30 days to pay their purchases. Thus, the Average Payment Period (APP) for the firm is 30 days. The ratio that measures APP is: APP = A.P./(credit Purchases/360). You use this ratio to find A.P. (accounts payable). Assume that all the firms purchases are made on credit. However, the problem does not say how much purchases the firm plans to make in the year XXX1. You need to calculate the expected purchases first. You can follow exactly the same steps that we followed in class when we worked out the Jordan Stores example to illustrate the Financial Planning and Forecasting approach that we labeled the Composite Method. This example is posted on Canvas in the Module on Financial Planning and Forecasting. The illustration example (Jordan Stores that is in the section titled Composite Method) provides all the steps to follow in order to calculate the forecasted purchases. In any case, this is briefly how you would calculate the forecasted purchases. We know that for Inventories, we can generally write the following: Beginning Inventories + Purchases Ending Inventories = Cost of Goods Sold (1) From (1) you can estimate Purchases for the forecast period as: Purchases = Cost of Goods Sold + Ending Inventories Beginning Inventories (2) Plug Account 3. Furthermore the problem says that the additional funds needed (AFN) will be raised as Short term Debt. Thus, you should use the account Other S.T. Liabilities as a plug account. It means that after completing your proforma Balance Sheet, use the Other ST Liab. account to make it balance by entering the appropriate dollar amount that will make the firms Total Liabilities and Net Worth equal its Total Assets. Do just as you did with the Cash and Equivalents account in spreadsheet project #1. As a result, an AFN (or EFN) account does not have to show on your proforma balance sheet as a separate item even though you have to provide the $amount of the EFN/AFN. Dividend 4. Finally, the problem does not mention any dividend payout rate. Thus, assume no dividend payment. That is, the company retains all of its Net Profit. Do not hesitate to ask if you have more questions on the spreadsheet assignment Excel 5. Spreadsheet projects must be completed with excel. Thus this project must be completed with excel. Projects that are not done with excel will be downgraded. 6. No handwritten submission is accepted. Accounts Receivable and Accounts Payable 1. The problem specifies that it takes the firm 30 days to collect its sales. This means that the days of sales outstanding (DSO), sometimes also called average collection period (ACP), equal 30 days. The ratio that measures DSO or ACP = A.R./(credit Sales/360). You use this ratio to find A.R. (accounts receivable). This problem assumes that all sales are made on credit. 2. The problem also says that the firm takes 30 days to pay their purchases. Thus, the Average Payment Period (APP) for the firm is 30 days. The ratio that measures APP is: APP = A.P./(credit Purchases/360). You use this ratio to find A.P. (accounts payable). Assume that all the firms purchases are made on credit. However, the problem does not say how much purchases the firm plans to make in the year XXX1. You need to calculate the expected purchases first. You can follow exactly the same steps that we followed in class when we worked out the Jordan Stores example to illustrate the Financial Planning and Forecasting approach that we labeled the Composite Method. This example is posted on Canvas in the Module on Financial Planning and Forecasting. The illustration example (Jordan Stores that is in the section titled Composite Method) provides all the steps to follow in order to calculate the forecasted purchases. In any case, this is briefly how you would calculate the forecasted purchases. We know that for Inventories, we can generally write the following: Beginning Inventories + Purchases Ending Inventories = Cost of Goods Sold (1) From (1) you can estimate Purchases for the forecast period as: Purchases = Cost of Goods Sold + Ending Inventories Beginning Inventories (2) Plug Account 3. Furthermore the problem says that the additional funds needed (AFN) will be raised as Short term Debt. Thus, you should use the account Other S.T. Liabilities as a plug account. It means that after completing your proforma Balance Sheet, use the Other ST Liab. account to make it balance by entering the appropriate dollar amount that will make the firms Total Liabilities and Net Worth equal its Total Assets. Do just as you did with the Cash and Equivalents account in spreadsheet project #1. As a result, an AFN (or EFN) account does not have to show on your proforma balance sheet as a separate item even though you have to provide the $amount of the EFN/AFN. Dividend 4. Finally, the problem does not mention any dividend payout rate. Thus, assume no dividend payment. That is, the company retains all of its Net Profit. Do not hesitate to ask if you have more questions on the spreadsheet assignment Excel 5. Spreadsheet projects must be completed with excel. Thus this project must be completed with excel. Projects that are not done with excel will be downgraded. 6. No handwritten submission is accepted.

Accounts Receivable and Accounts Payable

The problem specifies that it takes the firm 30 days to collect its sales. This means that the days of sales outstanding (DSO), sometimes also called average collection period (ACP), equal 30 days. The ratio that measures DSO or ACP = A.R./(credit Sales/360). You use this ratio to find A.R. (accounts receivable). This problem assumes that all sales are made on credit.

The problem also says that the firm takes 30 days to pay their purchases. Thus, the Average Payment Period (APP) for the firm is 30 days. The ratio that measures APP is:

APP = A.P./(credit Purchases/360). You use this ratio to find A.P. (accounts payable). Assume that all the firms purchases are made on credit.

However, the problem does not say how much purchases the firm plans to make in the year XXX1. You need to calculate the expected purchases first.

You can follow exactly the same steps that we followed in class when we worked out the Jordan Stores example to illustrate the Financial Planning and Forecasting approach that we labeled the Composite Method. This example is posted on Canvas in the Module on Financial Planning and Forecasting. The illustration example (Jordan Stores that is in the section titled Composite Method) provides all the steps to follow in order to calculate the forecasted purchases.

In any case, this is briefly how you would calculate the forecasted purchases.

We know that for Inventories, we can generally write the following:

Beginning Inventories + Purchases Ending Inventories = Cost of Goods Sold (1)

From (1) you can estimate Purchases for the forecast period as:

Purchases = Cost of Goods Sold + Ending Inventories Beginning Inventories (2)

Plug Account

Furthermore the problem says that the additional funds needed (AFN) will be raised as Short term Debt. Thus, you should use the account Other S.T. Liabilities as a plug account.

It means that after completing your proforma Balance Sheet, use the Other ST Liab. account to make it balance by entering the appropriate dollar amount that will make the firms Total Liabilities and Net Worth equal its Total Assets. Do just as you did with the Cash and Equivalents account in spreadsheet project #1. As a result, an AFN (or EFN) account does not have to show on your proforma balance sheet as a separate item even though you have to provide the $amount of the EFN/AFN.

Dividend

Finally, the problem does not mention any dividend payout rate. Thus, assume no dividend payment. That is, the company retains all of its Net Profit.

Do not hesitate to ask if you have more questions on the spreadsheet assignment

Excel

Spreadsheet projects must be completed with excel. Thus this project must be completed with excel. Projects that are not done with excel will be downgraded.