# 金融学·课件梳理5 比率分析与财务预测1

2017-2018 学年度第二学期期末考试复习材料 金融学·课件梳理

2017-2018 学年度第二学期期末考试复习材料

@群众演员

Lesson5Ratio Analysis and Financial Forecast1 第 5 课比率分析与财务预测 1 Today’s Agenda
? Ratio Analysis （本档从略） ? Financial Forecast

Financial Forecast
1、Forecasting the Income Statement 收益表预测
? First, we will use the “Percent of Sales” method for forecasting the Income Statement ? Create a copy of the “Income Statement” tab, rename it “Pro Forma IS” ? Add a 2015* column ? Find COGS and SG&A as a percentage of sales for 2013 and 2014 ? Assume the forecast for 2015 Sales is 2,455,000 ? Use the average of 2013 and 2014 to determinethe percentage of Sales for the 2015 forecast

? Now assume the firm will invest an additional\$80,000 in fixed assets, which will result in an additional \$8,000 in depreciation in 2015. ? Assume Interest Expense is the same as 2014 (for now…we’ll come back to this) ? The tax rate is the same as before, so we now have a 2015 projection for the Income Statement 2、Forecasting the Balance Sheet 预测资产负债表
? Create a copy of the Balance Sheet ? Assume Cash is the same as 2014 ? Use the percent of sales method for A/R and Inventory ? The investment should be included in P&E ? Assume 10 years straight-line depreciation, and set the depreciation on the Income Statement equal to this cell

? Use the percent of sales method for A/P and Other current liabilities ? Assume ST Notes, Long-Term Debt, and Common Stock do not change ? Assume that the forecast for Dividends paid is 400,000 ? Isthereaproblemwith the pro-forma Balance Sheet?
1

2017-2018 学年度第二学期期末考试复习材料 金融学·课件梳理

? The Balance Sheet must balance, and the difference between the two sides we will label “Additional Funds Needed”

? Use the IF() function in Excel so that if the Assets are larger, label this number “Deficit”, “Surplus” if Liabilities are greater, and “Balanced” if they are equal ? Cowboy Corp will need to raise money to satisfy the Balance Sheet ? This is not simple: for example, they can raise Long -Term Debt, but this will, in turn, increase interest expense, lowering Net Income, which changes Retained Earnings, and thus the Balance Sheet won’t balance

? Thetextbooksolvesthisproblem circularusing references and iteration ? I prefer “Solver” so that ’s what we’ll use ? For 2014, Cowboy Corp’s interest rate was 8.9% (Interest expense divided by ST Notes plus LTD). That will be the rate for 2015 ? Use this rate for the interest expense ? See what happens if you simply change the LTD to reflect the Additional Funds Needed ? Now use Solver to find the solution to this problem 3、Linear Trend Forecasting 线性趋势预测
? We used Percent of Sales for the last round of analysis, now we’ll use linear trend forecasting 我们在上一轮的分析中使用了销售额，现在我们将使用线性趋势预测。 ? Below is thefull data for Cowboy Corp. from an earlier lecture:

? Put this into Excel, and plot the sales and year using a scatter chart. Adda line connecting the data points 把这个放到 Excel 中，使用散点图绘制销售和年份。 ? Now use the TREND () function to generate the 2015 Sales forecast 现在使用的趋势（）函数来产生 2015 销售预测 ? Next, add a trendline to our sales chart by right -clicking on the line in the chart
2

2017-2018 学年度第二学期期末考试复习材料 金融学·课件梳理

4、Regression Analysis 回归分析
? Last lecture, we looked at sales from 2010 - 2014, and used that to predict 2015 sales ? Now let’s say we want to predict 2015 COGS. We will consider the relationship between sales and COGS using regression analysis. If we think COGS are predicted by sales, we can run the following regression: = + ()+ ? First, make a scatter chart of Sales vs. COGS, with Sales on the x-axis (since it is the independent variable in the regression) 第一， 做销售的散点图和主营业务成本， 与 X 轴上的销售 （因为它是在回归自变量） ? Next, use the regression tool under the “Data” tab (Hint: first put the data into columns instead of rows) 接下来，在“数据”选项卡下使用回归工具（提示：首先将数据放入列而不是行）
nd ? Now run a 2 regression, using Sales as the dependent variable and Year as the independent variable (this is what we did in the last lecture)

5、Solver Revisited ? Last lecture we used solver to determine how much Long -Term Debt would be
3

2017-2018 学年度第二学期期末考试复习材料 金融学·课件梳理

needed to balance the pro forma Balance Sheet ? Now let’s use it for a completely different financial application: calculating loan payments. (We’ll revisit this topic later in the semester) ? Assume you borrow \$220,000 and pay 1% interest every month. ? You want to pay off this loan in 15 years ? How much is your monthly payment? ? Note that thereare 12*15=180 months, and assume the first payment is paid in one month ? Next use solver to determine the amount you can borrow if the most you can pay per month is \$1,800

6、Solver With Constraints
? Now let’s use solver to maximize the revenue for your small business: ? Here is your menu: ? Cheese pizza - \$7 ? Supreme pizza - \$12 ? Calzone - \$6 馅饼 ? Because of your oven size, you can onlymake 300 total items a day. In addition, you can make a maximum of 175 pizzas (max of 100 supreme pizzas) 因为你的烤箱大小， 你一天只能做 300 件菜。 此外， 你最多可以做 175 个比萨饼 （最 高 100 个至尊比萨饼） 。 ? What combination maximizes revenue? 什么样的组合最大化收益？ a pizza shop

? So far, we’ve only forecasted one number (for example different ways to get one number for next year’s Sales forecast) 到目前为止，我们只预测一个数字（例如不同的方式来为明年的销售预测，得到一 个数） ? In the real world, this is unrealistic, and we may want to consider many different possible numbers for a variable next year 在现实世界中，这是不现实的，我们可能要考虑明年变很多不同的号码
4

2017-2018 学年度第二学期期末考试复习材料 金融学·课件梳理

? One way to do this is by using random number generators to simulate the future. This is called Monte Carlo Simulation 做到这一点的方法之一是通过使用随机数发生器来模拟未来。 这就是所谓的蒙特卡洛仿 真 ? Let’s start with a simple example: you have a \$100,000 portfolio with an expected return of 11% and a standard deviation of 25% 让我们从一个简单的例子：你有一个预期收益 11%，标准差为 25%的 100000 美元的投 资组合 ? What is the value of yourportfolio in one year? 你的投资组合在一年内的价值是多少？ ? Do this first using your best guess for next year’s value 首先利用你对明年价值的最好猜测。 ? Next use the Excel random number generator to predict 5,000 scenarios for next year’s value 接下来，使用 excel 随机数生成器来预测下一年值的 5000 种方案。 ? Use the MAX () and MIN () functions in Excel to determine the highest and lowest simulated value for your portfolio 在 Excel 中使用 max（）和 MIN（）函数来确定你的投资组合的最高和最低的模拟值。 ? Use the “histogram” option under “data analysis tools” and then plot the results using a column chart 在“数据分析工具”下使用“直方图”选项，然后使用柱状图绘制结果。 ? Last, use the “Bin Range” to get breakpoints at every \$10,000 (such that zero are in the “more” category) 最后，使用“bin 范围”在每 10000 美元中获取断点（例如“0”）在“更多”类别中。

5

18页 1财富值 财务分析全部课件 31页 5财富值 财务...三大财务主表比率分析及评... 21页 2财富值 财务...3.2 我国财务分析体系与内容构建 可归纳为四篇。...