金融学·课件梳理10 盈亏平衡分析和枢轴表


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

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

金融学· 课件梳理

Lesson10Breakeven Analysis and Pivot Table 第 10 课盈亏平衡分析和枢轴表 Today’s Agenda
? Break -even analysis 盈亏平衡分析 ? Pivot table 枢轴表

Break-Even Points
1、Break-Even Points 盈亏平衡点
We will assume there are two types of costs, variable (V) and fixed (F) ? Example: if you own a small business, let’s say a burger restaurant, some variable costs would be the cost of the burgers, buns, etc. An example of a fixed cost would be the monthly rent for the restaurant space ? A break-evenpointisthe levelofsales(measuredin units or dollars) that causes profits to be zero For example, we could find the quantity(Q) that results in an EBIT of zero in the following equation:?(–)? = Where Q=quantity of units sold, P=price per unit, V=variable costs per unit and F=total fixed costs We can solve the equation on the previousslide for the break-even quantity (Q*):

Where Q=quantity of units sold, P=price perunit, V=variable costs per unit and F=total fixed costs Example:for your burger restaurant, you charge $5 for a burger, your variable costs per burger are $1.75, and your total fixed costs are $30,000 for the year.Howmany burgers do you have to sell in a year to break even?

2、Setting a target EBIT 设定目标的息税前利润 We can solve the equation from before to find a target quantity ():


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

Where Q=quantity of units sold, P=price per unit, V=variable costs per unit, F=total fixed costs and =target level for EBIT ? How many hamburgers would you need to sell if you want an EBIT of $50,000? 3、Break-Even Points
Now let’s set up an income statement for the burger restaurant, with the same numbers as before plus the following: ? Interest expense is $12,000 ? T ax rate is 38% Use Solver to find: ? First, the number of burgers you need to sell to break even in terms of Net Income ? Second, the number so that Net Income is $10,000 (this will be the target Net Income)

4、Financial Break-Even 财政收支平衡 Financial break-even occurs when the present value of the future cash flows equals the cost of the project If your burger restaurant costs $50,000 to start, how many do you need to sell next year to for financial break-even (assume your discount rate is 11%) ? Reminder: cash flow is net income + depreciation ? In this example there is no depreciation (hence CF=NI) Now assume you sell 15,000 burgers your first year How many burgers would you need to sell in year 2 to break even financially? Last, assume that because of positive Yelp reviews, you’ll sell 125% more burgers in year 2 than in year 1. How many would you have to sell in year 1 to break-even financially? Sticking with this example, assume you now want to open a bigger restaurant, which will cost $450,000, has fixed costs of $85,000, interest expense of $22,000, and will operate for 5 years Assuming each year you sell 10% more burgers than the previous year, how many do you need to sell in year 1 to break even financially?

1、Pivot Tables
PivotTablesarebasicallyaninteractivetablethatallowsyoutodisplayand calculate key characteristics of a data set Use the “Salesperson Data”spreadsheet from D2L for the PivotTable analysis

First, create a simple PivotTable showing the sum of orders by salesperson

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

To format the numbers, right-click on one of them, select “Value Field Settings” and click on “Number Format” Next, add “country” to the “Report Filter” ? Try changing the country to see what happens Right-click on one of the sales numbers, and show the values as a “% of Grand Total” Now create a copy of this PivotTable and sort the values from highest to lowest Next click on “Salesperson” on the right and choose “Value Filters” and then “Top 10” and set it to only show the top 3 sales people Create another copy of this PivotTable and add “Order Date” to the Row Labels Instead of specific dates, we want to show Quarterly performance of the sales force To do this, click on a date in the PivotTable, then click on PivotTable Tools, then “Group Selection” and choose “Quarters” Switch the order of the Row Labels and see how it changes the PivotTable 2、Midterm Review #1 Starting with a blank spreadsheet, let’s set up a customized loan with the following details: ? Borrow $75,000 now ? 0.4% monthly interest ? Pay X from month 1 to month 36 ? Pay 9X from month 37 to 72 ? Reminder: use “Solver” to find X 3、Midterm Review #2 Starting with a blank spreadsheet, do the following retirement problem: ? You will work for 40 years, then retire for 25 years ? You will save $3,500 next year, and you will increase this amount by 4% a year ? You want your retirement spending to increase by 2.5% per year to offset inflation ? Your rate of return will be 7% during your working years and 4.75% during retirement ? What is the most you can spend your first year of retirement?