{"text_format":"1","workbook":"xlsx","n_sheets":4,"sheets":["Instructions","A","Models","Certification"],"not_rendered":{"charts":0,"images":1,"macros":0,"external_links":0,"defined_names":34,"comments":3,"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":122,"n_cols":3,"rows_kept":122,"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 1240."]
[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, compare in words the differences between the 5-year and"]
[null,"20-year models' long-term free cash flow growth rates and the optimal amount of"]
[null,"advertising. (Use Fill/Justify and not a text box.)"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[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 best estimate of the firm's Long Term 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 Growth rate"]
[null,"Explain:"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[10,"Complete the Certification sheet. Be sure that both pictures are saved as low resolution"]
[null,"using Compress Pictures. Use E-mail 96 ppi for size or the lowest resolution available."]
[null,"Certification pictures and/or sheet may be copied from another workbook. Before saving"]
[null,"your final version, set View/Zoom/100% to each sheet. In each sheet select cell A1."]
[null,"End in sheet A."]

{"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.0066*C2-0.0000521*C2^2)*0.6","v":-3.5304000000000002E-2},{"$f":"(-0.17+0.0066*D2-0.0000521*D2^2)*0.6","v":-3.5304000000000002E-2},-3.5304000000000002E-2,-3.5304000000000002E-2,-3.5304000000000002E-2,"<-- Sales growth rates"]
["S","Sales",{"$f":"1000","v":1000},{"$f":"C14*(1+D13)","v":964.69600000000003},{"$f":"D14*(1+E13)","v":930.63837241600004},897.78311531622558,866.0877802131015,835.51141722045816,"Grows at rate Sg"]
["CGS","Costs of goods sold before advertising expense",{"$f":"-C14*$C$6","v":-500},-482.34800000000001,-465.31918620800002,-448.89155765811279,-433.04389010655075,-417.75570861022908,"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.133171200000007},31.737161817395201,44.225758560724948,56.612527124747174]
["D","Depreciation",-30,{"$f":"-$C$7*C32","v":-77},{"$f":"-$C$7*D32","v":-74.281592000000003},-71.659154676032003,-69.129299879349375,-66.688759076408815]
["EBT","Profit before tax",{"$f":"SUM(C14:C19)","v":424.4},{"$f":"SUM(D14:D19)","v":359.74799999999999},358.17076540800002,356.96956479947596,356.14034878792631,355.67947665856741]
["Taxes","Taxes",{"$f":"-C20*$C$10","v":-169.76},{"$f":"-D20*$C$10","v":-143.89920000000001},-143.26830616320001,-142.78782591979038,-142.45613951517052,-142.27179066342697]
["EAT","Profit after tax",{"$f":"C21+C20","v":254.64},215.84879999999998,214.90245924480001,214.18173887968558,213.68420927275579,213.40768599514044]
["Div","Dividends",{"$f":"-C22*$C$11","v":-101.85599999999999},{"$f":"-$C$11*D22","v":-86.339519999999993},{"$f":"-$C$11*E22","v":-85.960983697920014},{"$f":"-$C$11*F22","v":-85.672695551874241},{"$f":"-$C$11*G22","v":-85.473683709102318},{"$f":"-$C$11*H22","v":-85.363074398056185}]
["REinc","Retained earnings",{"$f":"C23+C22","v":152.78399999999999},129.50927999999999,128.94147554687999,128.50904332781136,128.21052556365348,128.04461159708427]
[]
[null,"Balance sheet"]
["CASH","Cash and marketable securities",80,{"$f":"D39-D28-D32","v":239.16464000000008},{"$f":"E39-E28-E32","v":396.71452271743999},{"$f":"F39-F28-F32","v":552.8219820090618},{"$f":"G39-G28-G32","v":707.65658905933969},{"$f":"H39-H28-H32","v":861.38534557024445}]
["CA","Current assets (Not Cash & MS)",{"$f":"$C$3*C14","v":150},{"$f":"$C$3*D14","v":144.70439999999999},139.59575586240001,134.66746729743383,129.91316703196523,125.32671258306871]
[null,"Fixed assets"]
[null,"     At cost",{"$f":"C32-C31","v":1070},{"$f":"D32-D31","v":1119.81592},1167.87313876032,1214.2337454695257,1258.9576373194695,1302.1025968915428]
["D","     Depreciation",-300,{"$f":"C31+D19","v":-377},{"$f":"D31+E19","v":-451.28159199999999},{"$f":"E31+F19","v":-522.94074667603195},{"$f":"F31+G19","v":-592.07004655538128},{"$f":"G31+H19","v":-658.7588056317901}]
["NFA","     Net fixed assets",{"$f":"C14*$C$5","v":770},742.81592000000001,716.59154676032006,691.29299879349367,666.88759076408815,643.34379125975283]
["TA","Total assets",{"$f":"C27+C28+C32","v":1000},{"$f":"D32+D28+D27","v":1126.68496},{"$f":"E32+E28+E27","v":1252.90182534016},{"$f":"F32+F28+F27","v":1378.7824480999893},{"$f":"G32+G28+G27","v":1504.4573468553931},{"$f":"H32+H28+H27","v":1630.055849413066}]
[]
["CL","Current liabilities",{"$f":"C14*$C$4","v":80},77.17568,74.451069793279999,71.822649225298051,69.287022417048121,66.840913377636653]
["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":279.50927999999999},{"$f":"D38+E24","v":408.45075554687998},{"$f":"E38+F24","v":536.95979887469139},{"$f":"F38+G24","v":665.17032443834489},{"$f":"G38+H24","v":793.21493603542922}]
["TLE","Total liabilities and equity",{"$f":"SUM(C35:C38)","v":1000},{"$f":"SUM(D35:D38)","v":1126.68496},1252.90182534016,1378.7824480999893,1504.4573468553931,1630.055849413066]
[]
[null,"Year",0,1,2,3,4,5]
[null,"Free cash flow calculation"]
[null,"Profit after tax",null,{"$f":"D22","v":215.84879999999998},{"$f":"E22","v":214.90245924480001},{"$f":"F22","v":214.18173887968558},{"$f":"G22","v":213.68420927275579},{"$f":"H22","v":213.40768599514044}]
[null,"Add back depreciation",null,{"$f":"-D19","v":77},{"$f":"-E19","v":74.281592000000003},{"$f":"-F19","v":71.659154676032003},{"$f":"-G19","v":69.129299879349375},{"$f":"-H19","v":66.688759076408815}]
[null,"Subtract increase in current assets",null,{"$f":"-(D28-C28)","v":5.2956000000000074},{"$f":"-(E28-D28)","v":5.1086441375999811},{"$f":"-(F28-E28)","v":4.9282885649661807},{"$f":"-(G28-F28)","v":4.7543002654686006},{"$f":"-(H28-G28)","v":4.586454448896518}]
[null,"Add back increase in current liabilities",null,{"$f":"D35-C35","v":-2.8243200000000002},{"$f":"E35-D35","v":-2.7246102067200013},{"$f":"F35-E35","v":-2.6284205679819479},{"$f":"G35-F35","v":-2.5356268082499298},{"$f":"H35-G35","v":-2.4461090394114677}]
[null,"Subtract increase in fixed assets at cost",null,{"$f":"-(D30-C30)","v":-49.815920000000006},{"$f":"-(E30-D30)","v":-48.057218760319984},{"$f":"-(F30-E30)","v":-46.36060670920574},{"$f":"-(G30-F30)","v":-44.723891849943811},{"$f":"-(H30-G30)","v":-43.144959572073276}]
[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.479902720000004,-19.04229709043712,-26.535455136434969,-33.9675162748483]
["FCF","Free cash flow",null,{"$f":"SUM(D43:D49)","v":260.86416000000003},{"$f":"SUM(E43:E49)","v":251.23096369535997},{"$f":"SUM(F43:F49)","v":241.93785775305892},{"$f":"SUM(G43:G49)","v":232.97283562294507},{"$f":"SUM(H43:H49)","v":224.32431463411277}]
[null,null,"FCF Growth Rates ---->",null,{"$f":"E50/D50-1","v":-3.6928017649645928E-2},{"$f":"F50/E50-1","v":-3.6990288958051232E-2},-3.7055061218506213E-2,-3.7122443763484503E-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":260.86416000000003},{"$f":"E50","v":251.23096369535997},{"$f":"F50","v":241.93785775305892},{"$f":"G50","v":232.97283562294507},{"$f":"H50","v":224.32431463411277}]
[null,"Terminal value",null,null,null,null,null,{"$f":"H58*(1+C55)/(C54-C55)","v":1570.2702024387893}]
[null,"Total",null,{"$f":"D59+D58","v":260.86416000000003},{"$f":"E59+E58","v":251.23096369535997},{"$f":"F59+F58","v":241.93785775305892},{"$f":"G59+G58","v":232.97283562294507},{"$f":"H59+H58","v":1794.5945170729021}]
[]
[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":1495.7451201765352}]
[null,"Add in initial (year 0) cash and mkt. securities",{"$f":"C27","v":80}]
[null,"Asset value in year 0",{"$f":"C63+C62","v":1575.7451201765352}]
[null,"Subtract out value of firm's debt today",{"$f":"-C36","v":-320}]
[null,"Equity value",{"$f":"C64+C65","v":1255.7451201765352}]
[null,"Share value (100 shares)",{"$f":"C66/100","v":12.557451201765353},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,"Graph Vertical Line Calculations 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."]

{"sheet":"Certification","index":3,"n_rows":102,"n_cols":3,"rows_kept":102,"cut":false,"hidden":false,"merged":[[0,0,0,7]]}
["CERTIFICATION"]
[null,"After completing ALL of the other parts of this workbook answer"]
[null,"\"YES\" or \"NO\" in the two yellow fill boxes. The meaning of"]
[null,"\"help\" as used below follows syllabus and class discussion."]
[]
[null,null,"I certify to the best of my knowledge and belief that I have not"]
[null,null,"helped another student to complete any part of this assignment."]
[]
[null,null,"I certify that to the best of my knowledge and belief I have not received"]
[null,null,"help from another person related to any part of this assignment."]
[]
[null,"Replace picture below with a passport style picture of yourself."]
[]
[]
[null,"Insert a picture of your signature below to certify that the above statements are"]
[null,"correct to the best of your ability and that a false statement may result in a"]
[null,"failing course grade."]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,"YES"]
[null,null,"NO"]