Financial-statement forecasts:
On the basis of the above assumptions, forecast the firm’s future 5 years income statements and balance sheets. You need to u
se the respective tabs of the Business Analysis and Valuation Model (BAV Model).
Overview & Instructions Business Analysis and Valuation Model Overview The purpose of the Business Analysis and Valuation Model (BAV Model) is to assist users in analysing and valuing companies with atleast two years of historical financial statement data. This program may be used in conjunction with Business Analysis and Valuation by Krishna G. Palepu, Paul M. Healy, Sue Wright, Michael Bradbury and Jeff Coulton. Instructions 1. Import financial statement data into the respective tabs for intended company using DatAnalysis premium /Morningstar DatAnalysis or by manually entering a minimum of 5 years of financial data using annual reports. 2.Conduct classification exercise across three sheets, using Classification Lookup tab for reference. Ensure that all INDIVIDUAL line items (excluding totals and sub-totals) are appropriately classified to ensure accuracy of the standardised financial statements and forecasts. Classification Lookup Classification Lookup ERROR:#REF! •Instructions 1. Select the financial statement (Income Statement, Balance Sheet or Statement of Cash Flows from the drop-down menu below. 2. Select the desired line-item classification heading(s) from the yellow shaded drop-down menu for examples of financial statement line-items typically classified under that heading. •Classification Financial Statement to LookupBalance_Sheet Classification Category to LookupSales •Examples Company-operated stores Equipment sold Finance participation income Gain on sale of receivables Insurance commissions Licenses Membership and service fees Net interest margin Net sales Other non-interest income Other revenue Product sales Rentals Revenue Royalties & franchise-related fees Service, rental and maintenance revenues Services Services provided Servicing fee income Lookups Lookups ERROR:#REF! InputInputInputInputInputInputInputInputIncome StatementIncome StatementIncome StatementIncome StatementIncome StatementIncome StatementIncome StatementIncome StatementIncome StatementIncome StatementIncome StatementIncome StatementIncome StatementIncome StatementIncome StatementBalance SheetBalance SheetBalance SheetBalance SheetBalance SheetBalance SheetBalance SheetBalance SheetBalance SheetBalance SheetBalance SheetBalance SheetBalance SheetBalance SheetBalance SheetBalance SheetBalance SheetBalance SheetBalance SheetCash Flow StatementCash Flow StatementCash Flow StatementCash Flow StatementCash Flow StatementCash Flow StatementCash Flow StatementCash Flow StatementCash Flow StatementCash Flow StatementCash Flow StatementCash Flow StatementCash Flow StatementCash Flow StatementCash Flow StatementCash Flow StatementCash Flow StatementCash Flow StatementCash Flow StatementCash Flow StatementCash Flow Statement Ordering of YearsYearsMonthsDaysUnitsGrowthCost of EquityFinancialStatementsSalesCost of Goods SoldSG&AD&AOther Operating ExpensesInterest IncomeInterest ExpenseInvestment IncomeOther IncomeOther ExpensesMinority InterestTax ExpenseOther Comprehensive IncomeNet IncomePreferred DividendsCash and Short Term InvestmentsAccounts ReceivableInventoryOther Current AssetsLong-Term Tangible AssetsLong-Term Intangible AssetsDeferred Tax AssetsOther Long-Term AssetsAccounts PayableShort-Term DebtOther Current LiabilitiesOther Non-WC Current LiabilitiesLong-Term DebtDeferred Tax LiabilitiesOther Long-Term LiabilitiesMinority InterestPreferred StockShareholders' EquityShares OutstandingReceipts from CustomersIncome Tax ReceivedInterest ReceivedPayments to Suppliers and EmployeesIncome Tax PaidInterest PaidOther Operating Cash FlowsProceeds from sale of PPEProceeds from Investment in other financial assetsPurchase of PPEPurchase of IntangiblesAcquisition of subsidiariesSale of subsidiariesInvestments in other financial assetsProceeds of loan advancesProceeds from share issuesDividends PaidRepayment of Loan AdvancesOther Financing Cash FlowsOpening Cash BalanceEffect of Foreign Exchange RatesNet increase/decrease in cash heldEffect of Foreign Exchange RatesClosing Cash and Cash Equivalents Ascending (Oldest First)1990January1$Build-up growthCAPMIncome_StatementCompany-operated storesCost of salesGeneral and adminsitrativeDepreciationPre-opening expensesInterest incomeInterest on long-term debtEquity income from associatesGain on sale of Property, Plant and EquipmentLoss on sale of Property, Plant and EquipmentMinority interestProvision for taxesExtraordinary itemsNet IncomePreferred dividendsCashAccounts receivable, netFinished goodsCompany common shares acquired for employees' stock plans at costNet assets of discontinued operation - non-current portionComputer software development costsDeferred Tax AssetsLong-term investmentsAccounts payableCurrent portion of capital lease obligationsAccrued expensesOther financial liabilitiesCapital lease obligationsDeferred Taxes - Current AssetDeferred franchise revenueMinority interestPreferred StockAccumulated foreign currency translation adjustmentsNumber of common shares outstandingReceipts from CustomersIncome Tax ReceivedInterest ReceivedPayments to Suppliers and EmployeesIncome Tax PaidInterest PaidOther Operating Cash FlowsProceeds from sale of PPEProceeds from Investment in other financial assetsPurchase of PPEPurchase of IntangiblesAcquisition of subsidiariesSale of subsidiariesInvestments in other financial assetsProceeds of loan advancesProceeds from share issuesDividends PaidRepayment of Loan AdvancesOther Financing Cash FlowsOpening Cash BalanceEffect of Foreign Exchange Rates Descending (Newest First)1991February2$'000sSimple growthSimple cost of equityBalance_SheetEquipment soldCost of computer equipmentCosts of building brandsAmortisationProduct developmentInterest on short-term debtDividend incomeGain on sale of investmentsLoss on sale of investmentsNon-recurring chargesAccretion of redeemable preferred stockCash and cash equivalentsDue from securitisation trustMerchandise inventoriesDue from affiliatesProperty and equipment at cost, netContract renewal rightsLong-term receivablesTrade accounts payableCurrent portion of long-term debtAccrued interestConvertible subordinated debenturesDeferred Taxes - Current LiabilityOther LT liabilitiesAdditional paid-in capital 1992March3$mCash_Flow_StatementFinance participation incomeCost of equipment soldMarketing Salaries and benefitsProvision for losses on credit salesFinance costsRental incomeForeign exchange gainsRestructuring chargesEffects of accounting changesCash and temporary investmentsFinance income receivableRaw materials and component partsDue from employeesPlant, property and equipmentCost in excess of the Fair Value of Net Assets AcquiredDeposits and other non-current assetsFloor plan notes payableAccrued liabilitiesDiscounted lease rentalsDeferred Taxes - LT LiabilityCapital in excess of par 1993April4$bnGain on sale of receivablesCost of merchandise soldSales and marketingProvision for relocationGains from accounting changes the firm reports pre-taxMerger expensesContract proceeds receivableFinance participation receivable, netWork in progressNet assets of discontinued operationDeferred financing costsNon-current ntoes receivableNotes payableAccrued personnel costsLong-term obligationsCommon stock 1994May5Insurance commissionsCost of products soldSelling and store operating expensesRepositioning and other special chargesForeign exchange gainsShort-term investmentsOther receivablesPrepaid expensesDeferred subscriber acquisitions cost, netInvestment in Leveraged LeasesCustomer advancesLong-term notes payableCommon stock of predecessor 1995June6LicensesCost of revenuesSelling expenseResearch and developmentLosses from accounting changes the firm reports pre-taxTime depositsTrade account receivableprepaid income taxesExcess of costs over net assets of acquired companies less amortisationInvestment in sales-type and direct financing leasesCustomer depositsPos-retirement benefit costDeferred translation adjustment 1996July7Membership and service feesCost of sales and occupancy costsService, rental and maintenanceAsset impairmentRefundable income taxesFinance participation receivable, non-current portionDeferred franchise revenueSenior term notesNet unrealised investment gains 1997August8Net interest marginCost servicesSubscriber acquisition costsRestricted cash in spread accountsGoodwillDeferred revenueSubordinated notesRetained earnings 1998September9Net salesFinancial services costsUnbilled revenuesLicense rightsTreasury stock, at cost 1999October10Other non-interest incomeOther LT assets and deferred chargesUnrealised losses on marketable securities 2000November11Other revenue 2001December12Product sales 200213Rentals 200314Revenue 200415Royalties & franchise-related fees 200516Service, rental and maintenance revenues 200617Services 200718Services provided 200819Servicing fee income 200920 201021 201122 201223 201324 201425 201526 201627 201728 201829 201930 202031 2021 2022 2023 2024 2025 2026 2027 2028 2029 2030 2031 2032 2033 2034 2035 2036 2037 2038 2039 2040 2041 2042 2043 2044 2045 2046 2047 2048 2049 2050 Legend Style legend StyleDesignComment Header1Header1 Header2Header2 Header3Header3 Assumption100A user driven input Technical_Input100A model input that should not be changed to protect the integrity of the model Empty_CellA cell that is left intentionally blank to avoid the risk of error InSheet100A link within the worksheet or an interim calculation step OffSheet 100A link to another worksheet to minimise the number of inter-worksheet references Line_SubTotal100The sum of elements in the table immediately above Line_Total100The sum of elements above, including sub-totals Unit / InfoUSD millionsExplanatory text showing helpful information and the units/dimensions of the calculations Line_Summary100The SUM() of everything to the right Table_HeaderQtrHeader of a table or of an off-sheet reference Flag1Binary flag - set up as a 'Style' and updated with conditional formatting Line_ClosingBal100The closing balance of a control account Names Names and Constants ItemUnitValue Constants Days in YearNum#365 Months per YearNum#12 Quarters per YearNum#4 Months per QuarterNum#3 ThousandNum#1,000 MillionNum#1,000,000 Very Small NumberNum#0.00000010 ToleranceNum#10.00 Tolerance $101010 $'000s1 $m0.1 $bn0.01 Imported Income Statement Imported Income Statement ERROR:#REF! •Instructions 1. . Import financial statement data into the respective tabs for intended company using DatAnalysis premium /Morningstar DatAnalysis or by manually entering a minimum of 5 years of financial data using annual reports. 2.. Conduct classification exercise across three sheets, using Classification Lookup tab for reference. Ensure that all INDIVIDUAL line items (excluding totals and sub-totals) are appropriately classified to ensure accuracy of the standardised financial statements and forecasts. NB 1: DO NOT classify totals and subtotals as line items. Doing so will result in misstated values throughout the model. NB 2: Ensure that all units are consistent. NB 3: Reference Classification Lookup as necessary to ensure correct classification. •Classification ClassificationLine Items 20172018201920202021 Sales5Trading revenue5,628.006,854.307,095.307,918.908,916.10 Cost of Goods Soldcost of sale-4,397.50-5,384.10-5,568.20-6,224.80-6,938.90 Gross Profit1,230.501,470.201,527.101,694.101,977.20 Other IncomeOther income2.001.102.403.602.90 SG&ASales and marketing expenses-580.10-695.10-731.00-786.40-845.80 SG&AAdministration expenses-36.20-42.20-44.50-41.70-41.30 Cost of Goods SoldOccupancy expense-248.60-305.70-306.40-313.10-293.60 Other Operating ExpensesOther expense-75.30-77.20-74.00-72.10-54.70 Interest Expense6Finance costs-10.70-16.60-14.30-36.40-24.70 Profit before tax259.20334.50359.30448.00720.00 Tax Expense7Income tax expense-86.80-101.30-109.50-145.70-213.90 Net profit172.40233.20429.80302.30506.10 Other Operating ExpensesTotal Operation Expense5,268.006,443.206,669.407,364.407,955.80 D&A12D+impairment-69.70-61.10-56.20-59.90-218.50 Other Comprehensive Incomecurrency exchange-1.200.600.30-0.200.00 Other Comprehensive IncomeAbnormals-38.2-0.70-19.7-7.60 Preferred DividendsDiluted Shares114.42114.88114.88114.88114.88 Preferred DividendsDiluted Weighted Shares112.72115.96116.04115.96115.59 Preferred DividendsOrdinary Dividends-135.00-151.60-163.20-217.100.00 Imported Balance Sheet Imported Balance Sheet ERROR:#REF! •Instructions 1. Ensure "Model" section of Control tab is filled out at a minimum prior to importing financial data to ensure correct timing and classification. 2. Import financial statement data into the respective tabs for intended company using a financial database (i.e. FactSet, Capital IQ, Thompson Reuters) or by manually entering a minimum of 2 years of financial data using annual reports. 3. Conduct classification exercise across three sheets, using Classification Lookup tab for reference. Ensure that all INDIVIDUAL line items (excluding totals and sub-totals) are appropriately classified to ensure accuracy of the standardised financial statements and forecasts. NB 1: DO NOT classify totals and subtotals as line items. Doing so will result in misstated values throughout the model. NB 2: Reference Classification Lookup as necessary to ensure correct classification. •Classification ClassificationLine Items 20172018201920202021 Cash and Short Term Investments544.30453.50354.70251.50263.20 Accounts Receivable366.40411.00344.00220.30211.60 Inventory455.00577.00688.70739.30938.80 Other Current Assets23.2029.4033.0034.7035.70 Long-Term Tangible Assets1254614875178652045719876 Long-Term Intangible Assets765876.56751031.41031.4 Deferred Tax Assets15.417.416.422.230.3 Other Long-Term Assets3,319.004,456.005,049.005,876.007,688.00 Accounts Payable521433756854.1777.4 Short-Term Debt12221955201223342543 Other Current Liabilities54.344.372.3092.2032.30 Other Non-WC Current Liabilities Long-Term Debt12,045.0012,777.0013,098.0013,546.0013,876.00 Deferred Tax Liabilities1,543.001,233.001,879.001,756.001,322.00 Other Long-Term Liabilities2,503.001,766.001,965.001,766.002,654.00 Minority Interest3418-10-1255 Preferred Stock23.0012.000.004.000.00 Shareholders' Equity16,786.0012,355.0015,987.0017,876.0012,098.00 Shares Outstanding Imported Cash Flow Statement Imported Cash Flow Statement ERROR:#REF! •Instructions 1. Ensure "Model" section of Control tab is filled out at a minimum prior to importing financial data to ensure correct timing and classification. 2. Import financial statement data into the respective tabs for intended company using a financial database (i.e. FactSet, Capital IQ, Thompson Reuters) or by manually entering a minimum of 2 years of financial data using annual reports. 3. Conduct classification exercise across three sheets, using Classification Lookup tab for reference. Ensure that all INDIVIDUAL line items (excluding totals and sub-totals) are appropriately classified to ensure accuracy of the standardised financial statements and forecasts. NB 1: DO NOT classify totals and subtotals as line items. Doing so will result in misstated values throughout the model. NB 2: Reference Classification Lookup as necessary to ensure correct classification. •Classification ClassificationLine Items 20172018201920202021 Receipts from Customers6,205.507,551.907,804.908,759.309,819.20 Payments to suppliers and employees-5,908.80-7,130.50-7,373.80-7,624.50-9,078.80 interest received1.70.50.71.11.6 Interest and other finance costs paid on borrowings-9.30-15.00-13.80-11.50-3.30