This web page contains a live and functional spreadsheet created with
SpreadsheetConverter
. Please enable JavaScript in your web browser for the live calculations to work properly.
Step 1-Instructions
Step 1-Instructions
Step 2-Cash Flow Projections
Step 3-Discount Rate Selection
Step 4-Profitability Dashboard
ROI
Payback Period
NPV
IRR
Instructions
This online tool is provided in accompaniment of the
De-Risking Emerging Market Opportunities (DEMO) Toolkit
, serving as a template for assessing the profitability of emerging opportunities. We recommend that all users first consult the toolkit.
In addition to this overview, there are two tabs that you will need to navigate to titled
Cash Flow Projections
and
Discount Rate Selection
, serve as templates for you to input your assumptions. The final tab titled
Profitability Dashboard
,
serves as the primary output.
Cash Flow Projections
This form serves as a template for your cash flow projections, which will flow into calculations on subsequent pages. It will prompt you to state a few assumptions regarding demand, price, and cost, and calculate your cash flows from them. It also leaves room for you to manually input other cash flow line items that don't fall under these assumptions.
Discount Rate Selection
This form takes data on your financing sources as an input and calculates 3 common discount rate choices for you. You are then required to specify which discount rate you would like to use, which is then used in subsequent calculations on other sheets.
Profitability Dashboard
Taking your cash flow projections and discount rate as inputs, this page provides a dashboard for each of the four profitability metrics provided: ROI, Payback Period, NPV, and IRR. It also provides a space for you to evaluate each metric as positive, neutral, or negative to aid in decision making.
Note, any and all cells that require your
input
are shaded in yellow as below. These cells are editable.
Content Name
Cells that contain
major outputs
are shaded in grey as below. These cells are not editable.
Content Name
Value
Cells that contain
other outputs that do not need your attention
are shaded in blue as below. These cells are not editable.
Content Name
Value
Disclaimer: Note that this toolkit is meant to serve as a guide and we would encourage users to engage financial expertise prior to make any investment decisions. As such, we do not assume any responsibility or liability for business decisions that are made from the insights provided in this tool.
Cash Flow Projections
Step 1 -
Fill in the following table with your assumptions for the number of units sold, selling price, and cost of goods sold (COGS) over the next 5 years. To save time and effort, consider leveraging your findings from Section 1 for speed of onset (how quickly you expect to start selling) and duration of the opportunity. You can toggle these assumptions to see how sensitive the profitability metrics are to your choices.
Demand, Price, & Cost Assumptions
Assumption
Year 1
Year 2
Year 3
Year 4
Year 5
Units Sold
Assumption
Value
Selling Price
Cost of Goods Sold (COGS)
Step 2 -
Leveraging your estimated transition costs from Section 3, fill in the table below with the sum of your up-front transition costs and any new, ongoing expenses associated with the new opportunity
.
For example, up-front costs could include new machinery purchases and ongoing costs may include additional wages paid to staff the new opportunity.
Transition Cost Assumptions
Assumption
Up-Front
Total Up-Front Costs
Ongoing Costs (Annual)
Step 3 -
Based on the assumptions above, the first 4 lines of the cash flow table below will automatically generate. If you want to add additional line items that aren't captured by some of the simple assumptions above, do so in the yellow boxes labelled
Other
. The final row will automatically sum your net cash flows for each of the periods listed.
Category
Item
Up-Front
Year 1
Year 2
Year 3
Year 4
Year 5
…
Operations
Sales revenue
Cost of goods sold
Transition Costs
Up-Front
Ongoing
$0
Other
Net Cash Flow
Discount Rate Selection
Step 1 -
Fill in the following information regarding your firm's equity financing, debt financing, and corporate tax rate.
Debt Financing
Equity Financing
Corporate Tax Rate
Source
Amount
Interest Rate
Amount ($)
Tax Rate (%)
Min. Expected Return
Step 2 -
The table below (left) automatically calculates some common discount rate choices from your inputs above.
Common Discount Rate Choices
Weighted Average Cost of Debt
Weighted Average Cost of Capital
Min. Expected Return
Step 3 -
Using the guidance provided in the DEMO Toolkit, select your discount rate and enter it in the cell on the right below.
Selected Discount Rate
Profitability Estimation Dashboard
ROI
Payback Period
NPV
IRR
Discount Rate
ROI
Taking your cash flow projections as given, the table below will automatically calculate the return on investment of the project based on your cash flow inputs on the previous sheets.
ROI
Payback Period
Taking your cash flows as given, the table below calculates the Payback Period of the opportunity automatically.
Period
Up-Front
1
2
3
4
5
Net CF
Cumulative CF
Payback Period
Net Present Value (NPV)
Taking your cash flows and discount rate as given, the table below generates the NPV of the opportunity automatically.
Discount Rate
Period
Up-Front
1
2
3
4
5
Net CF
PV of CF
NPV
Internal Rate of Return (IRR)
Taking your cash flows and discount rate as given, the table below calculates the IRR of the opportunity.
Discount Rate
Period
Up-Front
1
2
3
4
5
Net CF
IRR
Print All Information
Please host the converted page in server to use Print All.
Reset
Print All