HI

Week2

PrepareFinancial Forecast Using

Excel Financial Forecasting

(Rev. 071216)

For this assignment, use the companythatyou selected previously forthe four class assignments.The selectionsweremade on a first come, first served basis; students cant use the same companies.

Students may elect to use the eVal Financial Forecasting ModelorProf. Stevens Excel Financial Forecasting model for this assignment.

If the eVal model is selected, make sure that financial information is available for the company that is selected for the assignment(See: http://www.lundholmandandsloan.com). Similarly, if Prof. Stevens model is usedfor the assignmentmake sure that financial information is available (e.g., Yahoo Finance, etc.).(Note: Alternatively, theValuePro.netmodel may be used but the final score for the assignment will be reduced by 20% if that alternative is selected.)

Theactual Excel forecasting model(either eVal or Prof. StevensFinancial Forecasting Model)must be submittedwith the assignment.

Theanalysis needs to include:

1.Explanation of Growth Rates-Include an explanation about growth rate projections for the company.(Note: it is difficult to justifyterminal orlong-term growth rates that exceed the growth rate of the economy, e.g., 3%; in the short-run, higher rates may be appropriate.)

2.Explanation of All Other Assumptions-Include an explanation ofallotherassumptionsrelated to future operating performance including costs,margins,efficiency,capitalization,etc.Note: All assumptions used in the forecast need to be explained in the written analysis.Aworksheet is provided for this analysisand it should be incorporated as atablein the written analysis.

3.Explanation and Interpretation of Projected Financial Statements-Explanation and interpretation of the projectedfinancial statements anddiscounted free cash flows to include pro forma financial statements (income statements, balance sheets, and statements of cash flows).

4.Explanation and Interpretation of Overall Forecast Results-Examination and interpretation of the overall forecast results and the projected financial ratio analysis.

Writing Instructions

The discussion portion of the analysis should be three to five pages in length, double spaced, and should employ APA style and format for reference citations. Supporting data (e.g., figures, tables, etc.) and references should be submitted limited to four separate attachments in an appendix after the written portion of the paper.

The paper should begin with a short introduction(explains the purpose of the paper and provides an overview of the contents that follow)and then proceed to examine the four topics outlined in the previous section.

The subheadings used in the paper should be:

1. Introduction

2. Explanation of Growth Rates

3. Explanation of All Other Assumptions

4. Explanation and Interpretation of Projected Financial Statements.

5. Explanation an Interpretation of Overall Forecast Results & Financial Ratios

Evaluation:12.5%of final course grade.

Completeness of analysis:The analysis must demonstrate a solid understanding of forecastingand analysis.All assumptions used in preparing the projections of the company need to be thoroughly explained.

Organization:The papershould be well-organized and follow a logical pattern of analysis and discussion.

Presentation:Papers should meet professional business standards and meet APA formatting requirements.

Spelling, punctuation, and grammar:There should be few errors in grammar and punctuation. All sentences must be complete and well-structured.

Submission and Format:The completed paper is to be submitted to the Gradebook location designated for the assignment.The paper must be in Word format otherwise no credit is earned for the assignment.TheExcel forecasting modelis to be submitted(either eVal or Prof. Stevens Excel Forecasting Model).

The paper is also to be submitted to the Online Classroom.Thiswill allow students to examine and discuss the various projects.

View as PageDownloadToggle Fullscreen

\fTable 1 Assumptions for Prof. Stevens' Financial Projection Model Company Name Inputs (First Page) Cost of Sales % % of Current Balance OR Forecast If Different 0.00% Selling/General/Administrative Expenses % Other Expenses % 0.00% 0.00% Other Expenses% Cash % 0.00% 0.00% Accounts Receivable % Inventories % 0.00% 0.00% Other Current Assets % Other Current Assets % 0.00% 0.00% Other Assets % Other Assets % Other Assets % 0.00% 0.00% 0.00% Other Assets % Accounts Payable % 0.00% 0.00% Notes Payable % Other Current Liabilities % 0.00% 0.00% Other Current Liabilities % Other Long-Term Liabilities % 0.00% 0.00% Other Long-Term Liabilities % Other Equity % 0.00% 0.00% Inputs & Projections (2nd Page - Bottom) Next Year Forecast Growth Rate for Sales Revenues (%) 0.0% Growth Rate for Other Revenues (%) 0.0% Other/Extraordinary Items $ ( + ) or (-) New Long-Term Debt $ (+) $0.0 $0.0 Common Stock Dividends $ (+) $1.4 (90% to Paid-in Cap. & 10% to Com. Stk. ) New Common Stock Issued $ (+) $0.0 (90% from Paid-in Cap. & 10% from Com. Stk.) Common Stock Repurchased $ (-) Preferred Stock Issued $ (+) Preferred Stock Redeemed $ (-) $0.0 Preferred Stock Dividend Rate (%) Tax Rate % (+) 0.0% 0.0% Depreciation Rate as Percent of Gross Fixed Assets % (+) 0.0% Capital Investments / New Fixed Assets $ (+) $0.0 Interest Expense for Short-Term & Long-Term Debt % (+) Interest Income % (+) 0.0% 0.0% $0.0 $0.0 Explanation of Assumptions Explanation of Assumptions Q. For the selected company, develop and explain a recommended financing strategy. How much additional financing is needed? What should be the sources(s) of that new financing and why? Estimate the costs of the new financing? If the current financing strategy is considered to be the best alternative, carefully and thoroughly discuss and explain why? A retail company such as Target requires a short-term and long-term financial strategy in order operate effectively in the long-run. The company is currently having a strategy of ensuring liquidity and access to capital markets, manage net exposure to floating interest rate volatility, and maintain good credit ratings. Target needs a financing strategy that's going to ensure cash flow management such that there's always short-term funds but not just focusing on the long-run. It should develop a new financing strategy of issuing more stock as the highest cash outflows in financing activities in repurchase of stock. It did not receive long-term debt in fiscal year 2015, I therefore recommend in addition to this strategy to have additional long-term debt particularly through the issue of bonds. The additional financing needed is $2.25 billion. The company can obtain financing for the new financing through issuance of new stock, and through additional of issuance of commercial paper to clear the additional financing which is basically cash outflows. The new financing should cost approximately 108 million at a rate of 4.8%. If the current strategy mentioned above is taken to be the most suitable alternative, it may be due to the fact that issuing more stock would increase the dividend payouts; thus leading to an increase in the cash out flows. The company's dividend rate is increasing annually and the shareholders are expecting it to remain that way. The company can therefore continue with its strategy of issuing commercial paper and increasing its credit rating score so that it can maintain its lenders and obtain new potential lenders. It can obtain this funds and use it to run its day to day operations and clear the long-term debts that are almost due. It however did incur much losses when it called a credit before it was due, I therefore recommend clear the debts that are due in the current year first.