Question

As part of an investment firm, you have been asked to research potential companies for the firm to invest in. Specifically, you are interested are interested in identifying the factors that influence a company’s earnings before taxes. You hypothesize that a firm’s current assets, current liabilities, and amount the firm pays in interest on its loans all might play a role in predicting a company’s earnings before taxes. Therefore, you collect data on all of the variables from several different companies.

Use this data to estimate the following model and answer each of the questions below.

EBT= β0+β1TotAssets+ β2 TotLiabilities+ β3 InterestExpense +ε

Variable Descriptions:

EBT: A firm’s total reported earnings before taxes measured in millions of dollars

Tot Assets: A firm’s total current assets measured in millions of dollars

Tot Liabilities: A firm’s total current liabilities measured in millions of dollars

Interest Expense: A firm’s total expense on interest paid on loans measured in millions of dollars

For this question, you will need to download the Stock Data and then use the data analysis tool pack in Excel to run a regression. Note, you will need to install the data analysis tool pack on your computer. How to do this differs based on the type of computer you are using, but instructions can be found on google for both Mac and PC. Estimate the sample regression equation above and fill in the estimated coefficients. Round the coefficient estimates to 4 decimals.

https://arizona.grtep.com/core/uploadfiles/components/282919/files/Stock%20Data.xlsx (Stock Data)

EBTˆ=_____+_____TotAssets+____TotLiabilities+____InterestExpense

Predict the earnings before taxes for a company with 1.5 billion dollars in assets, 900 million dollars in liabilities, and 80 million dollars in interest expenses. Make sure to use the rounded coefficients when making the prediction. Round your final answer to 4 decimals. Hint, make sure you pay attention to the units!

Answer 1

Using the regression tool in excel, the following output is obtained:

However, we are concerned only with the cells in bold. These are the coefficients of the regression model which are given as:

Thus, the model can be written as:

Predicted Earnings before taxes (EBT):

Putting TotAssets= 1.5 billion = 1.5*1000 = 1500 million dollars, TotLiabilities = 900 million dollars,

and InterestExpense = 80 million dollars in the above model, the predicted value of earnings before taxes is:

  

Note: To perform the analysis in excel, follow the below steps:

Data Data Analysis Regression Select Input X range & Input Y range specify the Output range OK.