## FIGURE 142 Actual Balance Sheet for Cutting Edge B2B

Forecast Forecast Forecast of Sales Current Liabilities Accounts Payable Short-term Debt Shareholders' Equity Paid-in Capital 3. The Income Statement and Earnings Per Share. Some Income Statement items are based on the Key Assumptions section, others are forecasted as a percentage of sales, and others are simple additions or subtractions. o Gross Margin Sales - Cost of Good Sold. Enter C13-C14 in cell C15 and copy it across. o Earnings Before Interest and Taxes (EBIT) Gross Margin - SG& A...

## Why I Wrote This Book

My goal is simply to change finance education from being calculator based to being spreadsheet modeling based. This change will better prepare students for the 21st century business world. This change will increase student satisfaction in the classroom by allowing more practical, real-world applications and by enabling a more hands-on, active learning approach. There are many features which distinguish this book from anything else on the market Teach By Example. I believe that the best way to...

## Craigs Challenge

I challenge the readers of this book to dramatically improve your finance education by personally constructing all 53 spreadsheet models in all 20 chapters of this book. This will take you about 27 to 53 hours depending on your current spreadsheet skills. Let me assure you that it will be an excellent investment. You will n gain a practical understanding of the core concepts of Corporate Finance, n develop hands-on, spreadsheet modeling skills, and n build an entire suite of finance...

## How To Build This Spreadsheet Model

Open the spreadsheet that you created for Corporate Financial Planning - Adjusted Present Value and immediately save the spreadsheet under a new name using the File Save As command. 2. Debt, Equity, and the Total. The Debt row is a repeat of the Debt input. Enter B22 in cell B30 and copy it across. Equity is the Present Value of Future Cash Flows (APV) less Debt. Enter B27-B30 in cell B31 and copy it across. Sum these two rows to get Debt + Equity. Enter B30+B31 in...

## Skill Building Problems

Suppose a firm is considering a labor-saving investment. In year 0, the project requires a 11,700 investment in equipment (all figures are in thousands of dollars). This investment is depreciated using the straight-line method over five years and there is salvage value in year 5 of 4,500. With or without the cost-reducing investment, all cash flows start in year 1 and end in year 5. The inflation rate is 2.6 in year 2 and declines to 1.4 in year 5. The real growth rate is 21.3 in year 2 and...

## Sensitivity Analysis

Consider the same project as Project NPV - Working Capital. Assume that the product life-cycle of seven years is viewed as a safe bet, but that the scale of demand for the product is highly uncertain. Analyze the sensitivity of the project NPV to the units sales scale factor and to the cost of capital. Solution Strategy. Copy the pattern of unit sales in the base case to a new location and multiply this pattern by a scale factor to get the new unit sales scenario. Assume that the real...

## FIGURE 179 Spreadsheet Model of Binomial Option Pricing Estimating Volatility

Standard Deviation Annual I 90.23 How To Build This Spreadsheet Model. How To Build This Spreadsheet Model. 1. Collect Historical Stock Price Data. Go to Yahoo Finance quote.yahoo.com , enter AMZN the ticker symbol for Amazon.com in the Get Quotes box, click on Chart, at the bottom of the page click on Other historical quotes, adjust the start date if you want more than three months of data, click on Download Spreadsheet Format, and save the csv file. Launch Excel and open the csv file. 2....

## FIGURE 123 Spreadsheet for Sensitivity of Costs Revenues and Accounting Profits to Unit Sales

Select the range B21 G26 for the Data Table. This range includes both the list of input values at the top of the data table and the output formulas on the side of the data table. Then choose Data Table from the main menu and a Table dialog box pops up. Enter the cell address B12 Unit Sales in the Row Input Cell and click on OK. 6. Graph the Data Table Results. Highlight the data table C21 G26 and then choose Insert Chart from the main menu. Select an XY Scatter chart type and...

## Using It To Determine Forward Rates

Given the yield curve as published by the financial press, calculate the implied forward rates at all maturities. Solution Strategy. We will use the yield curve that you entered in a spreadsheet for The Yield Curve -Obtaining It From Bond Listings. We will calculate the forward rates implied by the yield curve and then graph our results. FIGURE 8.3 Spreadsheet Model of The Yield Curve - Using It To Determine Forward Rates. FIGURE 8.3 Spreadsheet Model of The Yield Curve - Using It To...

## How To Build Your Own Spreadsheet Model

Start with the Net Present Value - General Discount Rate Spreadsheet, Insert Rows, And Move One Item. Open the spreadsheet that you created for Net Present Value - General Discount Rate and immediately save the spreadsheet under a new name using the File Save As command. Select the cell A8 and click on Insert Rows. Select the cell A13 and click on Insert Rows. Select the range A9 G9, click on Edit Cut, select the cell A13, and click on Edit Paste. 2. Inputs. Enter the inputs in the range C8...

## Alternative Notation Versions

One nice thing about spreadsheets is that you can use long descriptive labels to describe most variables and their corresponding formulas. However, some finance formulas are complex enough that they really require mathematical notation. When this happens, I provide alternative notation versions that match the notation of all popular corporate finance textbooks. The spreadsheet below shows the symbols that are used in all notation versions. I have selected the notation to fill in any gaps.

## General Discount Rate

A project requires a current investment of 100.00 and yields future expected cash flows of 21.00, 34.00, 40.00, 33.00, and 17.00 in periods 1 through 5, respectively. All figures are in thousands of dollars. For these expected cash flows, the appropriate discount rate starts at 8.0 in period 1 and declines to 7.0 in period 5. What is the net present value of this project Solution Strategy. We will calculate the Net Present Value of this project using a Time Line. This is the only...

## Future Value

A single cash flow of 747.25 is available now in period 0 . For this cash flow, the appropriate discount rate period is 6.0 . What is the period 5 future value of this single cash flow Solution Strategy. We will calculate the future value of the single cash flow in three equivalent ways. First, we will calculate the future value using a time line, where each column corresponds to a period of calendar time. Second, we use a formula for the future value. Third, we use Excel's FV function...

## System of Four Annuity Variables

There is a tight connection between all of the inputs and output to annuity valuation. Indeed, they form a system of four annuity variables 1 Payment, 2 Discount Rate Period, 3 Number of Periods, and 4 Present Value. Given any three of these variables, find the fourth variable. Solution Strategy. Given any three of these variable, we will use as many equivalent ways of solving for the fourth variable as possible. In solving for the Payment, use the formula and PMT function. In solving...