# 金融学·课件梳理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