{"text_format":"1","workbook":"xlsx","n_sheets":3,"sheets":["Instructions","A","Models"],"not_rendered":{"charts":0,"images":0,"macros":0,"external_links":0,"defined_names":34,"comments":2,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Instructions","index":0,"n_rows":124,"n_cols":3,"rows_kept":124,"cut":false,"hidden":false,"merged":[]}
["ASSIGNMENT 4.  Pro Forma Financial Statements and Valuation"]
[null,"This assignment is intended to give you a better understanding of how to use"]
[null,"Excel to forecast balance sheet and income statement items along with free"]
[null,"cash flows to value a business."]
[null,"All work is to be completed on your own as with the other assignments in this course."]
[null,"Follow the instructions below to complete the assignment starting on sheet A in this"]
[null,"workbook. Be sure you understand how the model works before proceeding. Modify only"]
[null,"the formulas in the workbook necessary to complete the required tasks. Do not move"]
[null,"cells or insert or delete rows or columns unless specifically requested (doing so"]
[null,"will make your workbook calculations ungradable!)."]
[null,"After completing the Certification (you may copy that sheet from another workbook),"]
[null,"name the workbook with your class number and last name (ex. 004James.xlsm). Email"]
[null,"the completed workbook to your instructor no sooner than 24 hours before it is due."]
[null,"Overview:"]
[null,"Your boss has asked you to complete an Excel model (see Models sheet) to"]
[null,"be used this year and in future years to help determine the optimal advertising budget."]
[null,"The textbook model has been revised to consider the allocation of advertising in years"]
[null,"1-5. Historically the company, through year 0 just ended, has been using $20 annual"]
[null,"advertising. They are considering changing the amount in future years with the"]
[null,"understanding that the amount used in years 4 and 5 will be the average of that used in"]
[null,"years 0-3. The growth rate in sales increases with increasing advertising as given by"]
[null,"the formulas in row 13 on sheet A. The company is unable to spend more than $100 in"]
[null,"any given year on advertising."]
[null,"You may assume that the company has prospects of continuing their business in"]
[null,"perpetuity and that the financial ratio values in C3:C11, WACC and sales growth rate"]
[null,"formulas will remain constant. The firm's long-term free cash flow growth rate (LTG) is"]
[null,"uncertain and must be estimated from the pro forma statements. The initial estimate of"]
[null,"LTG (prior to analysis of any pro forma statement) is five percent, exactly three percent"]
[null,"above the current and projected rate of inflation."]
[1,"On sheet A, format ALL input cells in bolded blue, as in this cell."]
[null,"See the Models sheet for a better explanation of the blue font convention."]
[2,"Use Name manager to give cells C2:C11 names similar to those in cells A2:A11."]
[null,"Document (explain) formulas in rows 16-39 with text in column I. See examples in I14"]
[null,"and I15. Use the names in Column A in your documentation. Enter your class number"]
[null,"and name in D1:E1."]
[3,"Insert sparkline graphs in column J for balance sheet and income statement items."]
[null,"The line graphs located in J14:J39 should graph years 0-5."]
[4,"Build a data table and graph to show what happens to the equity value (ALL shares) for"]
[null,"different advertising budgets in years 1-3 if the same amount is spent on advertising in"]
[null,"each of those three years. Use the shaded regions starting in A72 with advertising"]
[null,"amounts from $0 to $30 in $1 increments. You will need formulas in cells D2:F2 to"]
[null,"complete the analysis. Scale the y-axis to have a minimum value of 1235."]
[null,"For the graph, the first column of the data table is plotted on the x-axis. Label the graph"]
[null,"axes and supply a title. Graph the values in the data table as points."]
[5,"Use Solver to determine how much advertising the company should use assuming the"]
[null,"same amount in each year 1-3. Enter the resulting advertising amount as a number (not"]
[null,"formula) into C68."]
[null,"Add a solid line to the graph that runs vertically from the optimal advertising amount to"]
[null,"the optimal (highest achievable) equity value. You may need to do additional calculations"]
[null,"for the vertical line in the panel to the right of the graph."]
[6,"Now assume that the amounts spent on advertising in years 1-3 can each be different."]
[null,"Calculate the optimal advertising budget and place the amounts in C69:E69 as numbers"]
[null,"(not formulas). You will likely need to alter a couple of cell formulas to do this step."]
[null,"When finished with this step reset any cell formulas so that the data table continues"]
[null,"to reflect the answer to step 4 above."]
[7,"The formula in C62 uses the NPV function multiplied by a special term. For the valuation"]
[null,"modeled, that cell formula is appropriate! Very carefully explain why in the tan region"]
[null,"below. Place your text entries in column B, set View/Zoom/100%, then use"]
[null,"Home/Fill/Justify (not a text box) to produce a nice looking paragraph."]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[8,"Make an Exact copy of Sheet A (use the Sheet Copy command) and name it"]
[null,"\"Projection\". (Double check spelling!). Now work on the Projection sheet. Delete rows"]
[null,"68-104 and columns I and J. Delete all range names for the Projection sheet only."]
[null,"Rework the model to project free cash flows out to year 20. Advertising expenses in"]
[null,"years 19 and 20 should be the average of the new optimal advertising amounts in years"]
[null,"1-18. The Long-term free cash flow growth rate should equal the average FCF growth"]
[null,"rate of years 5-20. Below, carefully define the term Terminal Value (TV). Below that,"]
[null,"explain why we use the formula on sheet A to calculate terminal value. Use Fill/Justify"]
[null,"and not a text box."]
[null,"Terminal Value (TV) Definition:"]
[]
[]
[]
[null,"Explanation of why the formula in A!H59 is used to calculate TV:"]
[]
[]
[]
[]
[]
[]
[9,"Assume that you have found the best advertising budget using both 5- and 20-year"]
[null,"proforma models, each with a different estimate of the optimal amount of advertising."]
[null,"Your boss needs a quick forecast of the optimal amount of advertising needed for next"]
[null,"year so she can negotiate with the Ad agency this coming week. She is relying on your"]
[null,"analytic abilities and good judgement to tell her the amount of advertising needed next"]
[null,"year (just year 1) determind by your models. To make this estimate, how many years"]
[null,"would you project the proformas -- would you use the 5-year model, the 20-year model,"]
[null,"or something else? How much should be spent next year (year 1) on advertising? What"]
[null,"is your estimate of the firm's Long Term FCF Growth rate for years six and beyond?"]
[null,"Replace the \"???????\" below and explain your reasoning. (Use Fill/Justify as above.)"]
[null,"???????","<-- Number of years you would project free cash flows"]
[null,"???????","<-- Your estimate of the optimal amount of advertising for year one"]
[null,"???????","<-- Your estimate of the firm's Long Term FCF Growth rate"]
[null,"Explain:"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[10,"Copy the Certification sheet from a previous assignment as the last sheet in this"]
[null,"workbook. Then complete the Certification sheet with your answers to the questions."]
[null,"Be sure that both pictures are saved as low resolution using Compress Pictures. Use"]
[null,"E-mail 96 ppi for size or the lowest resolution available. Before saving your final"]
[null,"version, set View/Zoom/100% to each sheet. In each sheet select cell A1. End in sheet"]
[null,"A then save the workbook. Email it to your instructor by 8:00 AM the day it is due."]
[null,"r.ritchey@ttu.edu"]

{"sheet":"A","index":1,"n_rows":102,"n_cols":11,"rows_kept":102,"cut":false,"hidden":false,"merged":[]}
[null,"PRO FORMA FINANCIAL MODEL",null,"# Here!","Your Name Goes Here!"]
["ADDVERT","Advertising Expense",20,20,20,20,{"$f":"AVERAGE(C2:F2)","v":20},{"$f":"G2","v":20},{"$f":"\"6-Year Sum = \"&FIXED(SUM(C2:H2),2)","v":"6-Year Sum = 120.00"}]
["CA/S","Current assets/Sales",0.15]
["CL/S","Current liabilities/Sales",0.08]
["NFA/S","Net fixed assets/Sales",0.77]
["CGS/S","Costs of goods sold/Sales",0.5]
["Dg","Depreciation rate",0.1]
["rDEBT","Interest rate on debt",0.1]
["rCASH","Interest paid on cash and marketable securities",0.08]
["T","Tax rate",0.4]
["PAYOUT","Dividend payout ratio",0.4]
[null,"Year End",0,1,2,3,4,5]
[null,"Income statement",null,{"$f":"(-0.17+0.00619*C2-0.0000451*C2^2)*0.6","v":-3.8544000000000002E-2},{"$f":"(-0.17+0.00619*D2-0.0000451*D2^2)*0.6","v":-3.8544000000000002E-2},-3.8544000000000002E-2,-3.8544000000000002E-2,-3.8544000000000002E-2,"<-- Sales growth rates"]
["S","Sales",{"$f":"1000","v":1000},{"$f":"C14*(1+D13)","v":961.45600000000002},{"$f":"D14*(1+E13)","v":924.39763993600002},888.76765730230682,854.51099671924669,821.57472486170002,"Grows at rate Sg"]
["CGS","Costs of goods sold before advertising expense",{"$f":"-C14*$C$6","v":-500},-480.72800000000001,-462.19881996800001,-444.38382865115341,-427.25549835962335,-410.78736243085001,"CGS/S * S"]
[{"$f":"A2","v":"ADDVERT"},"Advertising Expense",{"$f":"-C2","v":-20},-20,-20,-20,-20,-20]
["IntDebt","Interest payments on debt",{"$f":"-$C$8*C36","v":-32},{"$f":"-$C$8*(C36+D36)/2","v":-32},{"$f":"-$C$8*(D36+E36)/2","v":-32},{"$f":"-$C$8*(E36+F36)/2","v":-32},{"$f":"-$C$8*(F36+G36)/2","v":-32},{"$f":"-$C$8*(G36+H36)/2","v":-32}]
["IntCash","Interest earned on cash and marketable securities",{"$f":"$C$9*C27","v":6.4},{"$f":"$C$9*C27","v":6.4},{"$f":"$C$9*D27","v":19.304243200000002},32.032128389939196,44.599698579384757,57.022644685576253]
["D","Depreciation",-30,{"$f":"-$C$7*C32","v":-77},{"$f":"-$C$7*D32","v":-74.032112000000012},-71.178618275072012,-68.435109612277628,-65.797346747381994]
["EBT","Profit before tax",{"$f":"SUM(C14:C19)","v":424.4},{"$f":"SUM(D14:D19)","v":358.12799999999999},355.47095116800006,353.23733876602057,351.42008732673048,350.01266036904428]
["Taxes","Taxes",{"$f":"-C20*$C$10","v":-169.76},{"$f":"-D20*$C$10","v":-143.25120000000001},-142.18838046720003,-141.29493550640822,-140.56803493069219,-140.00506414761773]
["EAT","Profit after tax",{"$f":"C21+C20","v":254.64},214.87679999999997,213.28257070080002,211.94240325961235,210.85205239603829,210.00759622142655]
["Div","Dividends",{"$f":"-C22*$C$11","v":-101.85599999999999},{"$f":"-$C$11*D22","v":-85.95071999999999},{"$f":"-$C$11*E22","v":-85.313028280320012},{"$f":"-$C$11*F22","v":-84.776961303844942},{"$f":"-$C$11*G22","v":-84.340820958415321},{"$f":"-$C$11*H22","v":-84.003038488570624}]
["REinc","Retained earnings",{"$f":"C23+C22","v":152.78399999999999},128.92607999999998,127.96954242048001,127.16544195576741,126.51123143762297,126.00455773285593]
[]
[null,"Balance sheet"]
["CASH","Cash and marketable securities",80,{"$f":"D39-D28-D32","v":241.30304000000001},{"$f":"E39-E28-E32","v":400.40160487423998},{"$f":"F39-F28-F32","v":557.49623224230947},{"$f":"G39-G28-G32","v":712.78305856970314},{"$f":"H39-H28-H32","v":866.45408466289837}]
["CA","Current assets (Not Cash & MS)",{"$f":"$C$3*C14","v":150},{"$f":"$C$3*D14","v":144.2184},138.65964599040001,133.31514859534602,128.17664950788699,123.236208729255]
[null,"Fixed assets"]
[null,"     At cost",{"$f":"C32-C31","v":1070},{"$f":"D32-D31","v":1117.3211200000001},1162.81829475072,1206.5618263978483,1248.6193073611696,1289.0557247782408]
["D","     Depreciation",-300,{"$f":"C31+D19","v":-377},{"$f":"D31+E19","v":-451.03211199999998},{"$f":"E31+F19","v":-522.21073027507202},{"$f":"F31+G19","v":-590.64583988734967},{"$f":"G31+H19","v":-656.44318663473166}]
["NFA","     Net fixed assets",{"$f":"C14*$C$5","v":770},740.32112000000006,711.78618275072006,684.35109612277631,657.97346747381994,632.61253814350903]
["TA","Total assets",{"$f":"C27+C28+C32","v":1000},{"$f":"D32+D28+D27","v":1125.84256},{"$f":"E32+E28+E27","v":1250.8474336153599},{"$f":"F32+F28+F27","v":1375.1624769604318},{"$f":"G32+G28+G27","v":1498.9331755514099},{"$f":"H32+H28+H27","v":1622.3028315356623}]
[]
["CL","Current liabilities",{"$f":"C14*$C$4","v":80},76.916480000000007,73.951811194880008,71.101412584184544,68.360879737539733,65.725977988936009]
["DEBT","Debt",{"$f":"C33-C35-C37-C38","v":320},{"$f":"C36","v":320},320,320,320,320]
["STK","Stock (Par + Paid In)",450,450,450,450,450,450]
["REbal","Accumulated retained earnings",150,{"$f":"C38+D24","v":278.92607999999996},{"$f":"D38+E24","v":406.89562242047998},{"$f":"E38+F24","v":534.06106437624737},{"$f":"F38+G24","v":660.57229581387037},{"$f":"G38+H24","v":786.57685354672628}]
["TLE","Total liabilities and equity",{"$f":"SUM(C35:C38)","v":1000},{"$f":"SUM(D35:D38)","v":1125.84256},1250.8474336153599,1375.1624769604318,1498.9331755514102,1622.3028315356623]
[]
[null,"Year",0,1,2,3,4,5]
[null,"Free cash flow calculation"]
[null,"Profit after tax",null,{"$f":"D22","v":214.87679999999997},{"$f":"E22","v":213.28257070080002},{"$f":"F22","v":211.94240325961235},{"$f":"G22","v":210.85205239603829},{"$f":"H22","v":210.00759622142655}]
[null,"Add back depreciation",null,{"$f":"-D19","v":77},{"$f":"-E19","v":74.032112000000012},{"$f":"-F19","v":71.178618275072012},{"$f":"-G19","v":68.435109612277628},{"$f":"-H19","v":65.797346747381994}]
[null,"Subtract increase in current assets",null,{"$f":"-(D28-C28)","v":5.7815999999999974},{"$f":"-(E28-D28)","v":5.5587540095999941},{"$f":"-(F28-E28)","v":5.3444973950539918},{"$f":"-(G28-F28)","v":5.1384990874590244},{"$f":"-(H28-G28)","v":4.940440778631995}]
[null,"Add back increase in current liabilities",null,{"$f":"D35-C35","v":-3.0835199999999929},{"$f":"E35-D35","v":-2.9646688051199988},{"$f":"F35-E35","v":-2.8503986106954642},{"$f":"G35-F35","v":-2.7405328466448111},{"$f":"H35-G35","v":-2.6349017486037241}]
[null,"Subtract increase in fixed assets at cost",null,{"$f":"-(D30-C30)","v":-47.321120000000064},{"$f":"-(E30-D30)","v":-45.497174750719978},{"$f":"-(F30-E30)","v":-43.743531647128293},{"$f":"-(G30-F30)","v":-42.057480963321268},{"$f":"-(H30-G30)","v":-40.436417417071198}]
[null,"Add back after-tax interest on debt",null,{"$f":"-(1-$C$10)*D17","v":19.2},19.2,19.2,19.2,19.2]
[null,"Subtract after-tax interest on cash and mkt. securities",null,-3.84,-11.582545920000001,-19.219277033963518,-26.759819147630854,-34.213586811345749]
["FCF","Free cash flow",null,{"$f":"SUM(D43:D49)","v":262.61375999999996},{"$f":"SUM(E43:E49)","v":252.02904723456001},{"$f":"SUM(F43:F49)","v":241.852311637951},{"$f":"SUM(G43:G49)","v":232.06782813817799},{"$f":"SUM(H43:H49)","v":222.66047777041985}]
[null,null,"FCF Growth Rates ---->",null,{"$f":"E50/D50-1","v":-4.0305248153942652E-2},{"$f":"F50/E50-1","v":-4.0379217031827563E-2},-4.0456439855825033E-2,-4.0537072472435987E-2]
[]
[null,"Valuing the firm"]
[null,"Weighted average cost of capital",0.2]
[null,"Long-term free cash flow growth rate (LTG)",0.05]
[]
[null,"Year",0,1,2,3,4,5]
[null,"FCF",null,{"$f":"D50","v":262.61375999999996},{"$f":"E50","v":252.02904723456001},{"$f":"F50","v":241.852311637951},{"$f":"G50","v":232.06782813817799},{"$f":"H50","v":222.66047777041985}]
[null,"Terminal value",null,null,null,null,null,{"$f":"H58*(1+C55)/(C54-C55)","v":1558.6233443929386}]
[null,"Total",null,{"$f":"D59+D58","v":262.61375999999996},{"$f":"E59+E58","v":252.02904723456001},{"$f":"F59+F58","v":241.852311637951},{"$f":"G59+G58","v":232.06782813817799},{"$f":"H59+H58","v":1781.2838221633585}]
[]
[null,{"$f":"\"Enterprise value, present value of row \"&ROW(B60)","v":"Enterprise value, present value of row 60"},{"$f":"NPV(C54,D60:H60)*(1+C54)^0.5","v":1491.5572405362389}]
[null,"Add in initial (year 0) cash and mkt. securities",{"$f":"C27","v":80}]
[null,"Asset value in year 0",{"$f":"C63+C62","v":1571.5572405362389}]
[null,"Subtract out value of firm's debt today",{"$f":"-C36","v":-320}]
[null,"Equity value",{"$f":"C64+C65","v":1251.5572405362389}]
[null,"Share value (100 shares)",{"$f":"C66/100","v":12.515572405362388},null,null,null,null,null,"Equity value / #Shares"]
[null,"Optimal advertising for years 1,2 &3 -->"]
[null,"Optimal advertising for each year 1,2 &3 -->"]
["Data Table Goes Here",null,null,"Graph Goes Here",null,null,null,null,null,null,"Optional:"]
[null,null,null,null,null,null,null,null,null,null,"Vertical Line Calculations for Chart Here"]
[0]
[1]
[2]
[3]
[4]
[5]
[6]
[7]
[8]
[9]
[10]
[11]
[12]
[13]
[14]
[15]
[16]
[17]
[18]
[19]
[20]
[21]
[22]
[23]
[24]
[25]
[26]
[27]
[28]
[29]
[30]

{"sheet":"Models","index":2,"n_rows":28,"n_cols":2,"rows_kept":28,"cut":false,"hidden":false,"merged":[]}
["What is an \"Excel Model\"?"]
["In this course an Excel Model consists of three parts:"]
[1,"One or more inputs."]
[null,"These are starting values of important variables. An input cell contains"]
[null,"either text, number or formula that drives the results. The model is"]
[null,"built so that all input cell values may be changed so that the elements"]
[null,"of the model that depend on these inputs automatically reflect the"]
[null,"changes. For example, a tax rate might be used in a model in many"]
[null,"different calculations. It is best to enter this rate in a single cell then"]
[null,"refer to that cell in all formulas that use the tax rate. The tax rate input"]
[null,"cell should be formatted with a blue font to indicate that the model is"]
[null,"built so that the tax rate only needs to be changed in one cell and that"]
[null,"all formulas in the workbook using the tax rate will use the rate from"]
[null,"that cell. The blue font convention reminds the user that all formulas"]
[null,"using the tax rate will be dependent on the rate in the single tax input"]
[null,"cell and that one need not modify equations that need this rate if it"]
[null,"changes."]
[null,"When building a model it is normally best to set as inputs those cells"]
[null,"containing constants that might change in the future. That way when"]
[null,"the model is opened in the future the user can easily see which"]
[null,"elements of the model must be revised."]
[2,"Intermediate calculations."]
[null,"These are calculations that are done on the spreadsheet that would not"]
[null,"typically be the focus of the analysis but rather computational steps"]
[null,"leading up to key model outputs. In a simple model there may be no"]
[null,"intermediate calculations."]
[3,"Outputs."]
[null,"These are key calculations, graphs or tables for which the model is built."]