Description

FIN 4873, Homework #4

DUE March 31 (before class begins)

Instructions

This homework picks up where we left off with Part II of Homework #3 when we used regression analysis to project the income statements for Local Yokels, Inc.  The figures used for this Income Statement are not the solution for Homework #3, so work with the Income Statements on the accompanying spreadsheet for this assignment.

Also, Note that all references to “the Comparables” is referring to the reduced set beginning on the Dataset tab on Row 250.

Balance Sheet

The cash figure for years 2015 and 2016 will be our plug figure.

For the years 2015 – 2016, calculate the Accounts Receivable amount by using the weighted average of the Receivables-to-Sales for the comparables on the Dataset tab beginning in row 250 (sum of column Q divided by sum of column C) and applying the average to the projected sales of Local Yokels.

For Inventory projections, use a regression of the inventory levels for the comparables versus the Cost of Goods Sold (COGS).  You will need to place zeroes for the Inventory values (column R) for those companies with blanks. Then, run a regression with Inventory as the dependent variable (Y) to establish the Base Inventory (intercept) and the Variable Inventory (X Variable 1) as a percentage of COGS.  Use the regression equation to forecast inventory levels for 2015 and 2016.  (Don’t worry about the fact that the equation is not very significant and has a negative coefficient for the percentage of sales.)

Assume that Other Current Assets (column S) are a weighted average of sales in Column C and apply the ratio to Local Yokels’ projected sales for years 2015 – 2016.

Now write an equation to sum the Total Current Assets.

Calculate the Fixed Assets using a regression between the Fixed Assets and Sales for the comparables.  Again, put zeroes in for the companies with blanks in the Net Fixed Assets (column U).  Apply the results for the Fixed Assets of Local Yokels for 2015 and 2016.

Assume that Real Estate is just the average value of that for the comparables (column V) in both 2015 and 2016.  Leave the blanks of the comparables as blanks.

Write an equation that sums the Total Assets for both years.

To avoid circularity, we need to first do the Equity portion of the Liabs. & Equity side of the balance sheet.  Keep the Common Stock amount the same for 2015 and 2016 as it is for the Beginning amount in 2014 since this is a financing decision based upon whether or not the company needs additional funding.

Retained earnings increases as a result of Net Income and no dividends are paid.

Calculate the Total Liabilities for 2015 and 2016 as the average percentage of Total Liabilities (column AA of the DataSet) to Stockholders Equity (column AB) for the comparables by applying the ratio to the sum of the Common Stock and Retained Earnings (rows 23 and 24) projected for Local Yokels.

To make your balance sheet balance, plug the cash account (with an equation!) so that Total Assets equals Total Liabilities & Equity.

Now let’s finish off the income statements.  On the Income worksheet tab, make the equation for Non-cash Expense (row 16) equal to the weighted average of Non-cash Charges-to-Fixed Assets for the comparables (sum of Non-cash Charges in column I divided by sum of Fixed Assets in column U) and apply it to the projected Fixed Assets of Local Yokels.

Now we need to tie the interest expense of Local Yokels on the income statements to the amount of debt from the preceding year (i.e., Beg. 2014).  The equation for 2015 would be  

“  =Balance!B22*Balance!$B$4  “  and then you would copy this to the next column for year 2016.  We need to tie interest expense to the preceding year to avoid circularity (since our total liabilities is a function of the total equity, including retained earnings, but retained earnings is a function of our net income!)

Statements of Cash Flows

Create Statements of Cash Flows for Local Yokels for the years 2015 and 2016.  Remember that we are assuming NO dividends for years 2015 – 2016.

Use Non-Cash Expenses in the Income Statement for the Depreciation Expenses accounts.

Lastly, format the cells in both the income statements, balance sheets and statements of cash flows so that the three will be ready to print out as exhibits.