Excel Project: Part III Instructions

Excel Project: Part III Instructions

Common Sized BalanceSheets (Tabs 12), Common Sized Income Statements (Tab 13), Horizontal AnalysisBalance Sheets (Tab 14), Horizontal Analysis Income Statements (Tab 15), andProjected Financial Statements (Tabs 16-18)

        Continuein the same Excel workbook prepared in Week 4, adding the following tabs:

o  The12^th tab in yourFinancial Statement Analysis Template must be labeled Common Sized BalanceSheets.

  Set-up like theHistorical Balance Sheets (Tab 3), this worksheet should use formulas tocalculate the common sized percentages, linking to the Historical Balance Sheetsfor the calculations.  Total Assetsshould be used as the base for all common sized calculations on theBalance Sheets.  Common sized percentagesshould be calculated for all three historical years presented.

  See Exhibit A fora partial example of this tab.

o  The13^th tab in yourFinancial Statement Analysis Template must be labeled Common Sized IncomeStatements.

  Set-up like theHistorical Income Statements (Tab 2), this worksheet should use formulas tocalculate the common sized percentages, linking to the Historical IncomeStatements for the calculations. Revenue or Total Revenue (depending on your company) should be usedas the base for all common sized calculations on the Income Statements.Common sized percentages should be calculated for all three historical yearspresented.

o  The14^th tab in yourFinancial Statement Analysis Template must be labeled Horizontal Analysis ofBalance Sheets.

  Start by copyingand pasting the Historical Balance Sheets from Tab 3 to this worksheet.  Then, add dollar and percentage changecolumns between each of the set of historical years.

  See Exhibit B foran example of this tab.

o  The15^th tab in yourFinancial Statement Analysis Template must be labeled Horizontal Analysis ofIncome Statements.

  Start by copyingand pasting the Historical Income Statements from Tab 2 to this worksheet.  Then, add dollar and percentage changecolumns between each of the set of historical years.

o  The16^th tab in yourFinancial Statement Analysis Template must be labeled Projected IncomeStatements.  Three years of projectedstatements should be included, starting with the most recent year after thecompanys last issued report. 

o  The17^th tab in yourFinancial Statement Analysis Template must be labeled Projected Balance Sheets.  Three years of projected statements should beincluded.  Three years of projectedstatements should be included, starting with the most recent year after thecompanys last issued report. 

o  The18^th tab in yourFinancial Statement Analysis Template must be labeled Projected Statement ofCash Flows.  Three years of projectedstatements should be included, starting with the most recent year after thecompanys last issued report. 

  Other informationrelating to projected tabs:

  Projections shouldbe based on research performed on your company and its industry.  Use historical, trend, ratio data and currentand future economic projections as well as information from the companysMD&A to aid in preparing projections.

  Clearly state yourassumptions in a text box at the bottom of each tab.  Cite sources for assumptions based onresearch. 

  The statementsshould be consistent, reasonable, and accurate. Areas that will be checked for accuracy include:

        TheBalance Sheets should balance for all years presented.

        Retainedearnings should be properly calculated using the prior years balance, netincome, and dividends.

        TheEnding Cash Balance on the Statement of Cash Flows should agree to the CashBalance shown on the Balance Sheet for the same year.

        NetIncome on the Income Statements should match what is reported on the top of theStatement of Cash Flows (if indirect method is used).

o  Otherinstructions:

  The Excel workbookshould be professionally formatted. Professional formatting includes:

        All numbers are formattedsimilarly.  The use of decimals, commasfor thousands, and other formatting is consistent.

        Dollar signs are included inthe first and last numbers of each column.

        Subtotals and totals are precededby a single underline.  Totals arefollowed by a double underline.

        Line descriptions areindented/bolded in a manner that makes the spreadsheet easy to read. 

  All common sized, horizontal analysis, and projection items shouldbe calculated using formulas.  Totals and subtotalsshould also be calculated using formulas.  For an example of whichcells may need formulas, please see Exhibits A & B.

Thisassignment is due by 11:59 p.m. (ET) on Monday of Module/Week 6.