| Financial Statement Analysis Package (FSAP): Version 10.0 |
| Financial Reporting, Financial Statement Analysis, and Valuation: A Strategic Perspective, 10th Edition |
|
|
|
|
|
|
|
|
| FSAP User Guides: |
| By James Wahlen, Steve Baginski and Mark Bradshaw |
| The FSAP User Guides appear in column K to the right. |
|
|
|
|
|
|
|
|
| The FSAP user should only enter data in the blue-font cells shaded light green. |
| Analyst Name: |
Group 6 |
|
|
|
|
|
|
|
| Forecast Development: |
| Company Name: |
Microsoft |
|
|
|
|
|
|
|
| This Forecast Development spreadsheet provides work space in which the analyst can: |
|
|
|
|
|
|
|
|
|
|
| - build detailed revenue forecasts |
| Sales Forecast Development |
|
|
|
|
|
|
|
|
| - build forecasts of capital expenditures, property, plant and equipment, depreciation expense, and accumulated depreciation. |
|
|
|
|
|
|
|
|
|
|
| - build detailed forecasts of other financial statement amounts. |
|
| Historical Figures: |
|
| Sales Forecasts: |
|
|
|
|
| It is not necessary to use this spreadsheet to build financial statement forecasts in the FSAP Forecasts |
| Year |
2021 |
2022 |
2023 |
Year +1 |
Year +2 |
Year +3 |
Year +4 |
Year +5 |
| spreadsheet. If you use this spreadsheet to build more detailed forecasts, the you will need to link these |
|
|
|
|
|
|
|
|
|
|
| forecast amounts to the appropriate cells in the financial statements in the FSAP Forecasts spreadsheet. |
| Net Sales (in millions): |
$- |
$- |
$- |
$- |
$- |
$- |
$- |
$- |
| rates of change |
| ERROR:#DIV/0! |
ERROR:#DIV/0! |
ERROR:#DIV/0! |
ERROR:#DIV/0! |
ERROR:#DIV/0! |
ERROR:#DIV/0! |
ERROR:#DIV/0! |
|
|
|
|
| Implied revenue growth rates. |
| Sales Forecasts by Segments (from projections below): |
| Segment 1 |
$- |
$- |
$- |
$- |
$- |
$- |
$- |
$- |
| Segment 2 |
- |
- |
- |
- |
- |
- |
- |
- |
| Segment 3 |
0 |
0 |
0 |
- |
- |
- |
- |
- |
| Segment 4 |
0 |
0 |
0 |
- |
- |
- |
- |
- |
| Total Net Sales |
$- |
$- |
$- |
$- |
$- |
$- |
$- |
$- |
| FSAP users who wish to develop detailed revenue forecasts to incorporate a firm's various sources of revenue may do so here. |
| rates of change |
| ERROR:#DIV/0! |
ERROR:#DIV/0! |
ERROR:#DIV/0! |
ERROR:#DIV/0! |
ERROR:#DIV/0! |
ERROR:#DIV/0! |
ERROR:#DIV/0! |
| For an example of a detailed revenue forecast, see the FSAP Clorox model and the description in Chaper 10. |
|
|
|
|
| Implied revenue growth rates. |
| Sales by Segment: |
|
|
| Sales Forecasts by Segment: |
|
| 2021 |
2022 |
2023 |
Year +1 |
Year +2 |
Year +3 |
Year +4 |
Year +5 |
| Segment 1 |
$- |
$- |
$- |
$- |
$- |
$- |
$- |
$- |
| Sales growth rates: |
| ERROR:#DIV/0! |
ERROR:#DIV/0! |
0.00% |
0.00% |
0.00% |
0.00% |
0.00% |
| Volume growth: |
| 0.00% |
0.00% |
0.00% |
0.00% |
0.00% |
0.00% |
0.00% |
| Price changes: |
| 0.00% |
0.00% |
0.00% |
0.00% |
0.00% |
0.00% |
0.00% |
| Five-year growth rate history: |
2016 |
2017 |
2018 |
2019 |
2020 |
CAGR |
| Sales growth rates: |
0.00% |
0.00% |
0.00% |
0.00% |
0.00% |
0.00% |
| Volume growth: |
0.00% |
0.00% |
0.00% |
0.00% |
0.00% |
0.00% |
| Price changes: |
0.00% |
0.00% |
0.00% |
0.00% |
0.00% |
0.00% |
| Segment 2 |
$- |
$- |
$- |
$- |
$- |
$- |
$- |
$- |
| Sales growth rates: |
| ERROR:#DIV/0! |
ERROR:#DIV/0! |
0.00% |
0.00% |
0.00% |
0.00% |
0.00% |
| Volume growth: |
| 0.00% |
0.00% |
0.00% |
0.00% |
0.00% |
0.00% |
0.00% |
| Price changes: |
| 0.00% |
0.00% |
0.00% |
0.00% |
0.00% |
0.00% |
0.00% |
| Five-year growth rate history: |
2016 |
2017 |
2018 |
2019 |
2020 |
CAGR |
| Sales growth rates: |
0.00% |
0.00% |
0.00% |
0.00% |
0.00% |
0.00% |
| Volume growth: |
0.00% |
0.00% |
0.00% |
0.00% |
0.00% |
0.00% |
| Price changes: |
0.00% |
0.00% |
0.00% |
0.00% |
0.00% |
0.00% |
| Segment 3 |
$- |
$- |
$- |
$- |
$- |
$- |
$- |
$- |
| Sales growth rates: |
| ERROR:#DIV/0! |
ERROR:#DIV/0! |
0.00% |
0.00% |
0.00% |
0.00% |
0.00% |
| Volume growth: |
| 0.00% |
0.00% |
0.00% |
0.00% |
0.00% |
0.00% |
0.00% |
| Price changes: |
| 0.00% |
0.00% |
0.00% |
0.00% |
0.00% |
0.00% |
0.00% |
| Five-year growth rate history: |
2016 |
2017 |
2018 |
2019 |
2020 |
CAGR |
| Sales growth rates: |
0.00% |
0.00% |
0.00% |
0.00% |
0.00% |
0.00% |
| Volume growth: |
0.00% |
0.00% |
0.00% |
0.00% |
0.00% |
0.00% |
| Price changes: |
0.00% |
0.00% |
0.00% |
0.00% |
0.00% |
0.00% |
| Segment 4 |
$- |
$- |
$- |
$- |
$- |
$- |
$- |
$- |
| Sales growth rates: |
| ERROR:#DIV/0! |
ERROR:#DIV/0! |
0.00% |
0.00% |
0.00% |
0.00% |
0.00% |
| Volume growth: |
| 0.00% |
0.00% |
0.00% |
0.00% |
0.00% |
0.00% |
0.00% |
| Price changes and foreign currency exchange rates: |
| 0.00% |
0.00% |
0.00% |
0.00% |
0.00% |
0.00% |
0.00% |
| Five-year growth rate history: |
2016 |
2017 |
2018 |
2019 |
2020 |
CAGR |
| Sales growth rates: |
0.00% |
0.00% |
0.00% |
0.00% |
0.00% |
0.00% |
| Volume growth: |
0.00% |
0.00% |
0.00% |
0.00% |
0.00% |
0.00% |
| Price changes and foreign currency exchange rates: |
0.00% |
0.00% |
0.00% |
0.00% |
0.00% |
0.00% |
| Total Sales |
$- |
$- |
$- |
$- |
$- |
$- |
$- |
$- |
|
|
| ERROR:#DIV/0! |
ERROR:#DIV/0! |
ERROR:#DIV/0! |
ERROR:#DIV/0! |
ERROR:#DIV/0! |
ERROR:#DIV/0! |
ERROR:#DIV/0! |
| Forecast Development: Capital Expenditures, Property, Plant and Equipment, and Depreciation |
| Capital Expenditures: |
|
|
| CAPEX Forecasts: |
|
|
|
|
| The Capital Expenditures schedule permits the FSAP user to build detailed forecasts of future capital expenditures as a percent of future revenues, gross PP&E or any other reasonable basis for these forecast assumptions. |
|
| 2021 |
2022 |
2023 |
Year +1 |
Year +2 |
Year +3 |
Year +4 |
Year +5 |
| CAPEX: |
| PP&E Acquired |
($28,107.00) |
($23,886.00) |
($28,107.00) |
| PP&E Sold |
0 |
0 |
0 |
| Net CAPEX |
($28,107.00) |
($23,886.00) |
($28,107.00) |
#DIV/0! |
#DIV/0! |
#DIV/0! |
#DIV/0! |
#DIV/0! |
| Net CAPEX as a percent of: |
| Gross PP&E |
-63.70% |
-40.00% |
-37.80% |
ERROR:#DIV/0! |
ERROR:#DIV/0! |
ERROR:#DIV/0! |
ERROR:#DIV/0! |
ERROR:#DIV/0! |
| Revenues |
-16.70% |
-12.00% |
-13.30% |
ERROR:#DIV/0! |
ERROR:#DIV/0! |
ERROR:#DIV/0! |
ERROR:#DIV/0! |
ERROR:#DIV/0! |
| Property, Plant and Equipment and Depreciation |
|
|
| Property, Plant and Equipment and Depreciation Forecasts: |
|
|
|
|
| The Property, Plant & Equipment and Depreciation schedule automatically computes for the FSAP detailed forecasts of future PP&E based on exisitng PP&E plus projected future capital expenditures. The Depreciation expense schedule automatically computes future depreciation expense based on exisitng depreciable PP&E future capital expenditures. The expected useful life for depreciation purposes is computed below. |
|
|
|
|
|
|
|
|
|
|
| FSAP automatically links the projected amounts for gross PP&E, accumulated depreciation, depreciation expense, and capital expenditures into the financial statements in the Forecasts worksheet. |
| PP&E at cost: |
2021 |
2022 |
2023 |
Year +1 |
Year +2 |
Year +3 |
Year +4 |
Year +5 |
| Beg. balance at cost: |
|
|
| $95,641.00 |
#DIV/0! |
#DIV/0! |
#DIV/0! |
#DIV/0! |
| Add: CAPEX forecasts from above: |
|
|
| ERROR:#DIV/0! |
ERROR:#DIV/0! |
ERROR:#DIV/0! |
ERROR:#DIV/0! |
ERROR:#DIV/0! |
| End balance at cost: |
$59,715.00 |
$74,398.00 |
$95,641.00 |
#DIV/0! |
#DIV/0! |
#DIV/0! |
#DIV/0! |
#DIV/0! |
| Accumulated Depreciation: |
| Beg. Balance: |
|
|
| $- |
#DIV/0! |
#DIV/0! |
#DIV/0! |
#DIV/0! |
| Subtract: Depreciation expense forecasts from below: |
|
|
| ERROR:#DIV/0! |
ERROR:#DIV/0! |
ERROR:#DIV/0! |
ERROR:#DIV/0! |
ERROR:#DIV/0! |
| End Balance: |
$- |
$- |
$- |
#DIV/0! |
#DIV/0! |
#DIV/0! |
#DIV/0! |
#DIV/0! |
| PP&E - net |
$59,715.00 |
$74,398.00 |
$95,641.00 |
#DIV/0! |
#DIV/0! |
#DIV/0! |
#DIV/0! |
#DIV/0! |
| Depreciation Expense Forecast Development: |
|
|
| Depreciation expense forecast on existing PP&E: |
|
|
| Existing PP&E at cost, less any non-depreciable PP&E (i.e., land): |
$95,641.00 |
$5,313.40 |
$5,313.40 |
$5,313.40 |
$5,313.40 |
$5,313.40 |
| This computation shows depreciation expense based on the exisiting depreciable PP&E at the start of the forecast period. The computation assumes straight line depreciation methods, zero salvage value, and the estimated useful life computed below. If the firm has non-depreciable PP&E, such as land, you shoudl subtract that amount here. . |
|
|
| Remaining balance to be depreciated. |
$95,641.00 |
$90,327.60 |
$85,014.20 |
$79,700.80 |
$74,387.40 |
$69,074.10 |
| This computation shows the amount of gross PP&E still to be depreciated. Once this amount falls to zero, depreciation is complete.The FSAP user should be sure that these amounts are not negative. |
| New PP&E to be Depreciated: |
|
|
| Depreciation expense forecasts on new PP&E: |
|
|
| Capex Year +1 |
#DIV/0! |
#DIV/0! |
#DIV/0! |
#DIV/0! |
#DIV/0! |
#DIV/0! |
| FSAP automatically computes a new depreciation schedule for each year's capital expenditures, which are included in PP&E. These computations assume straight line depreciation methods and zero salvage value. The computations use the extimated useful life as computed below. |
|
|
| Capex Year +2 |
#DIV/0! |
| #DIV/0! |
#DIV/0! |
#DIV/0! |
#DIV/0! |
|
|
| Capex Year +3 |
#DIV/0! |
|
| #DIV/0! |
#DIV/0! |
#DIV/0! |
|
|
| Capex Year +4 |
#DIV/0! |
|
|
| #DIV/0! |
#DIV/0! |
|
|
| Capex Year +5 |
#DIV/0! |
|
|
|
| #DIV/0! |
| Total Depreciation Expense |
|
|
| #DIV/0! |
#DIV/0! |
#DIV/0! |
#DIV/0! |
#DIV/0! |
| Depreciation methods: |
2021 |
2022 |
2023 |
| PP&E at Cost |
$59,715.00 |
$74,398.00 |
$95,641.00 |
|
|
|
|
|
| FSAP automatically estimates the estimated useful life for depreciation purposes by dividing the average amount of gross depreciable PP&E by depreciation expense. This estimate assume straight line depreciation and zero salvage value. |
| Subtract any PP&E that is not Depreciable |
0 |
0 |
0 |
|
|
|
|
|
| If the firm has PPE that is not depreciable (e.g., land and construction in progress), subtract it here. |
| Depreciable PP&E |
$59,715.00 |
$74,398.00 |
$95,641.00 |
| Avg Depreciable PP&E |
| $67,056.50 |
$85,019.50 |
| Depreciation Expense |
$- |
$- |
$- |
| Implied Average Useful Life in Years |
| ERROR:#DIV/0! |
ERROR:#DIV/0! |
| Useful Life Forecast Assumption: |
| (in years) |
18 |
MB0411
Video3.xlsx
