Financial Forecasting Problem
In this part, you are required to perform the following analysis for Lowe’s Company (Ticker: LOW).
(1) Find the historical financial statements of Home Depot in the most recent five years from other finance website.
(2) Use TREND function in Excel to perform linear trend extrapolation for the sales of the firm in years 2018~2025.
(3) Perform regression analysis to determine the relationship between sales and inventory of the company. Interpret the regression results: coefficients, t-statistics for coefficients, R square, R square adjusted, and F statistics.
(4) Use percent of sales method to forecast 2018 financial statements (income statement, balance sheet) for the firm.
(5) (Iteration calculations) Use iteration calculations in Excel to eliminate DFN in the pro forma balance sheet if DFN is not equal to 0. Assumption: If DFN is a deficit, we assume that 50% of the deficit amount will be raised using short-term debt and the other 50% will be raised using long-term debt. If DFN is a surplus, we assume that 50% of the surplus will be used to reduce the outstanding short-term debt and the other 50% will be used to reduce the outstanding long-term debt. You should set a dummy variable (0, 1) in Excel to control (disable/enable) the iterative calculations.
(6) Summarize/interpret your findings and results.
Can you please also tell me which formulas you used for each calculation?