| Home > Support and FAQ | Share |
This page contains a list of frequently asked questions (FAQ) concerning Excel templates by Business Spreadsheets. You can navigate directly to FAQ on each model by using the links in the table below.
Detailed information about using the models can be found in the User Guides.
More questions and and answers can be found in Excel Help Forums for Business corresponding to each Excel template product.
What version of Excel do I need?
All templates are compatible with Microsoft Excel
97 or above for Windows and Microsoft Excel 2004, 2011 or 2016 for Macintosh OS X. The templates are not compatible with Excel 2008 for Mac as they require VBA support which is not included in the Excel 2008 version for Mac.
How can I open the download files?
The download files are "zipped" to to minimize the
file size. You will need some extraction software like PeaZip to
open them on your computer once they are downloaded.
Why can't I see all of the cell formulas?
The worksheets are protected to ensure the robustness
of operation. However, critical formulas are outlined in the embedded
help prompts.
When I open the file, it asks me if I want to enable macros. What
should I do?
You need to click "Yes" to accept macros in order
to run the tools.
When I open the file, it doesn't ask me if I want to enable macros.
Therefore, the macros are disabled. What should I do?
In this case you need to set your macro security settings
to Medium. You can do this by opening a blank workbook and choosing:
Excel 97-2003: Tools >> Options >> Security >> Macro Security, and selecting the 'Medium' level.
Excel 2007: Click on the top left ribbon button and choose Excel Options >> Trust Center >> Trust Center Settings... >> Macro Settings and select 'Disable all macros with notification'.
Excel 2010-2016: File >> Options >> Trust Center >> Trust Center Settings... >> Macro Settings and select 'Disable all macros with notification'.
Upon restarting Excel you should now be presented with the option to enable macros on opening the template file.
The files are saved as .xls files (Excel 97-2003) so that they are compatible with all versions of Excel from and including 97. To maintain interoperability between all versions of Excel they should remain in this format and run in 'Compatibility mode' in Excel 2007 and above. The files can be saved in .xlsm (Excel Macro-Enabled Workbook) for Excel 2007 and above; however they will no longer be able to be opened in versions of Excel 97 to 2003. A button is provided in the first sheet of each template to save the file as an XLSM version for the latest versions of Excel.
Nothing happens when I click on any of the buttons in the template.
First, make sure that macros are enabled in Excel but following the instructions above. If the issue persists, make sure to extract the zipped file into a directory somewhere on your computer. You may then need to right click the Excel file and go to Security and choose to unblock the file.
Why are your VBA macros not digitally signed and how can I trust them to be safe?
Trust is an important part of our business and all of our VBA macros are tested as 100% safe and virus free. We have considered digitally signing our solutions and decided not to do so due to the following issues with digital signatures.
When I enter my registration code it tells me that it is invalid. What
should I do?
Please make sure that the email address matches that
which is specified with your registration code. Furthermore, make sure
that you haven't substituted a number "1" for a letter "l" or
a number "0" for a letter "o", or vice versa. The easiest
way to make sure that you have the correct code is to copy and paste it
directly from your confirmation email.
Why can't I see the VBA code?
The VBA code has been protected to ensure the robustness
of operation and for intellectual property purposes.
Can other users open the models that I have completed?
When other users open the model for the first time
on another computer, they are presented with the 30-day trial version.
After 30 days, if they have not registered the model, they will still
be able to view completed models, but not run processes. In this way,
the templates can be freely distributed to clients and/or interested
parties to view results.
I want to customize a model for my own use or to distribute it to
multiple users in my organization. What are my options?
Upon purchasing the products, you will receive the
password to unlock cells, sheets, and the workbook. Since much of the
functionality depends on the original workbook structure, modifications
made are at the sole risk of the user. Site licenses are available on
purchase so that the model can be registered for an unlimited number
of users within the same organization.
An updated version of a model is available on your site. How can
I upgrade my older registered version?
You can simply download the new version and it will
work as a registered version on the same computers that you have your
older version on.
How can I subscribe to be notified of product updates?
To subscribe to product update email notifications, select the products in question on the purchase page, enter your email address and leave the option checked to receive update emails. On clicking submit, you are not obliged to follow through with the order and your details are automatically saved to receive update information.
I need a customized Excel solution. Does Business Spreadsheets undertake
customized development work and consultancy?
Business Spreadsheets does undertake customized Excel development work and consultancy; however we believe that your specific development needs are most efficiently fulfilled by accessing a community of wider professional experts. Accordingly, we want to ensure that the most competent match is made for you to complete your development project requirements.
There are many project management software titles available. Why
should I use this one?
Many of the project management software titles available
are very large and complex. This model is 'light', and while it may not
have all of the features of a large software title, such as Microsoft
Project, it does provide the core features necessary to successfully
manage and monitor a project. Furthermore, since it is Excel based, it
offers the ability to link data from it to other spreadsheets for financial
analysis.
I am working on the project alone, but the model won't let me add
tasks until I have entered some participant information. Is there any
work around?
Yes. By adding yourself as a participant, you can add
tasks using yourself as the person responsible. To ensure that the Gantt schedule diplays correctly, you should set the
hourly rate to one. You can also then choose to base the Earned Value Analysis on
hours, rather than task cost value for the project status reporting.
I need to manage multiple projects at the same time. Can the template
manage multiple projects, or do I need to create a separate workbook
for each project?
The project management template is designed to manage
one project with multiple phases and corresponding tasks. Therefore the
most comprehensive solution is to create a separate instance of the template
for each project. However, since the template is Excel based, a separate
'master' workbook can be created to monitor each of the projects' dynamics
by linking into each of the individual template's Status Report and other
required information.
Another more limited alternative is to use the predefined project phases
and corresponding tasks to mimic separate projects. This is especially
convenient if the same partisans are working on more than one of the
projects as the auto-timing function will allocate time resources for
the participants accurately over the sub projects. This approach is limited,
however, as each sub project will not be able to be split into phases
and projects defined by early phases will be allocated time resources
before those defined by later phases.
How can I forecast inputs for the model?
You can use regression methods to produce statistically
robust forecasts as inputs for the model. The Regression Forecasting
tool is specifically designed to accomplish this and can be downloaded here.
Why does the model attribute value to the "PV of capital commitments
after year 5"?
A unique feature of the model is to fully account
for all actions taken during the forecast period. Therefore the impact
of any remaining capital commitments after year 5 (resulting from actions
taken before year 6) are discounted back to the end of year 5 and included
in the overall valuation.
What is Economic Value Added (EVA)?
EVA is a valuation methodology trademarked by Stern
Stewart & Co. Further detailed information about EVA can be found here.
What is the "finite" terminal valuation option, and when should I
use it?
A unique feature of the model is to provide an alternative
to the traditional perpetuity terminal value calculation. In highly dynamic
and competitive environments common today it is sometimes unreasonable
to expect cash flows to grow at a constant rate into infinity. As products
and businesses mature, decline, and cease to be profitable, this alternative
provides a suitable method for mimicking such life cycles.
Why should I use the Comparable Investment Score (CIS)?
The CIS is a unique feature to benchmark and compare
different and competing investment proposals within your business. It
provides an excellent tool for prioritizing business proposals and accelerating
approval and Decision Making processes.
When should I use Real Options Valuation?
While traditional discounted cash flow analysis is
an essential part of a business proposal, it fails to account for any
strategic options that may be embedded in the proposal. This limitation
has become more prevalent in today's increasingly dynamic, competitive
and technology driven business environment. To address this, Real Options Valuation has gained considerable popularity as a mechanism to understand
and quantify the potential options embedded in proposals. Further detailed
information about Real Options Valuation can be found here.
How do I know which Real Option model to use?
The Menu sheet in the model outlines the characteristics
of each valuation method and how they apply to specific situations. It
is also common to apply more than one model to the same investment proposal.
For example, if a new product investment is being considered, it is useful
to evaluate both the Option to Delay the investment, and critical market
entry points under the Game Theory model.
Can I link my Investment Valuation results to the Real Options template?
Yes. You can create links to the results of the Investment
Valuation or other models from any of the blue input cells in the Real
Options Valuation model. This is a benefit of using multiple Excel templates
and is a recommended approach.
Where can I get market volatility data for the Pre-defined input
sheet?
Predefined volatility data should be based on businesses
that relate as closely as possible to the underlying business being evaluated.
This can be from similar historic business cases in the same organization
or market equity data of businesses similar in nature, size and location
as a proxy. Market volatility data can be acquired from market data providers
(such as Bloomberg or Reuters), Stock Market Exchanges, Investment Houses,
or specific research organizations.
I want to forecast my financials, but where do I start?
The first goal of forecasting variables with multiple
regression is the identification of the value drivers with the strongest
relationship to your desired forecast variable. In order to do this you
must first determine what data exists that you can attain to try in the
model. This might include macro-economic data, volumes, prices, or other
financial time-series data.
Is there a limit to number of independent variables?
The number of independent variables and observations that can be run
is unlimited and the feature selection can remove variables by finding the optimal
combination for predictive forecasting.
I have text categorical and binary/logistic data. Will the regression handle this?
The regression analysis will detect any variables that resemble binary/logistic, such as True or False as well as categorical text in any of the variables. When such a variable is detected, an option is presented to automatically create a dummy proxy variable with numerical mappings. In doing so, the original variable is kept and turned off to keep the mapping and the newly created numerical variable is used in the regression analysis.
Why should I ensure all statistical tests are met before running
my forecast?
By satisfying the key statistical tests, when testing
the relationships of your data, the validity of you forecast will be
much stronger. This, in turn, can help you better understand what the
true value drivers of your business are, and can improve business case
quality.
I want to copy use my forecast results into another model, but I
want them to read across the page, not down. How can I do this?
You can copy your forecast results and then after
selecting the first destination cell in your other model, choose Paste
Special, then check the Values and Transpose boxes to paste the values
across the page.
I want to convert my forecast time series to a higher frequency (like
from months to years). How can I do this?
You can convert you forecast time series into a different
frequency by creating a pivot table report of the forecast data and corresponding
date information. In your pivot table, you can right-click the date dimension,
and choose Group and Outline, then Group to transform the data into a
higher time frequency.
I want to optimize my portfolio weightings. How can I do this?
If you have historical pricing or return data on the
investments in your portfolio, you can run an optimization process which
creates a hypothetical optimal portfolio to maximize the return to risk
ratio. The Portfolio Optimization tool accomplishes this, and can be
downloaded here.
When should I "Roll Over" my portfolio model?
The model can be "rolled over" to a new reporting
period at any time. Usually this is on an annual basis; however some
users choose to report periodic portfolio performance more frequently.
I "Rolled Over" my portfolio, and all of my transactions disappeared.
What's going on?
When you "roll over" your portfolio, all transactions
are cleared out and appended to the History sheet in order to record
transactions for the new reporting period.
The "Net Return Over the Period" for one of my investments look wrong
in the Performance sheet, compared to the start and end values. Why
is this?
The "Net Return Over the Period" calculation takes
into account of distributions, incremental investments, and divestments
made during the reporting period; thereby adjusting the straight return
calculation accordingly.
I have multiple copies of the model running for multiple clients
and want to automatically update them with common data. How can I do
this?
Investment Advisors currently use this model for monitoring
multiple client portfolios. To facilitate the update of price data
to multiple client portfolio files, a batch update module has been separately
developed and is freely available here.
I thought that portfolio optimization was for financial instruments.
How can I use it for my business?
A business is simply a collection of investments.
Portfolio optimization can be applied at a variety of different levels
within an organization reconciling up to the total organization. Such
levels typically include business units making up the entire organization,
products and services making up business units, and so on. It is important
that profitability for each business or product can be identified by
attributing costs and revenues that otherwise be recorded at an aggregated
level. Methods such as Activity Based Costing or Value Stream Mapping can assist in accomplishing
this.
The ability to apply optimization analysis to a portfolio of businesses
represents an excellent framework for driving capital allocation, investment,
and divestment decisions.
Should I just change my portfolio as the model tells me?
The model results should be used as a guide to making
decisions about the make up of your portfolio. It is important to remember
that the results are based on historical input data that may not be reflective
of future circumstances. Further criteria to be considered should be
the ability and constraints to change weightings, and the cost of transactions.
I have differing time periods for my input data. Will the model still
work?
The model requires for each product or business data
to be based on the same time period and frequency. This is to ensure
that return and volatility parameters are not biased by missing or zero
values.
When I optimize my portfolio, one investment always gets 100% weighting. Why is this?
A 100% weighting for one asset in the portfolio can occur when the return
risk profile for that investment asset is better than all others combined.
To work with this issue there are several possibilities:
I deleted a product from the system and now, when I load an old order,
this information is missing. How do I get it back?
When a product or customer is removed from the system,
the information is omitted from any loaded invoices related to the product
or customer. This is due to the identifier structure of the system to
optimize efficiency. It is not recommended to remove customers or products
which have been populated into historical invoices for this reason. The
information cannot be retrieved once deleted unless a backup of the template
was made prior to the deletion.
I want to monitor stock levels but the report only shows this for
one product specified before running the report. How can I see a list
of all products with low or zero stock?
This type of analysis can be made directly in the
'Products' sheet by running a Filter or a Sort on the 'Units in Stock'
column. Alternatively, the 'Products' sheet can be exporting into Excel
for further analysis or text format for integration with and existing
inventory management system.
What is Microsoft InfoPath and why should I export my invoices in
this format?
Microsoft InfoPath is an information gathering and
sharing application using XML that was released with Office 2003. The
purpose of the application is to " gather information flexibly and
efficiently in rich, dynamic forms and more effectively share, reuse,
and re purpose information throughout your team or organization".
Using the 'Export to InfoPath XML' function in the invoice template enables
import of the invoice details to InfoPath compatible applications for
both the invoicing organization and the recipient.
I changed my bond payment data but the yield to maturity didn't update.
Why is this?
You must rerun the Yield to Maturity process each
time you change the bond data input.
The model cut off my bond payments when I used the automatic input
form. Why is this?
The model is limited to handle a maximum of fifty
bond payments. The automatic input form omits any payments that exceed
this limit.
What is Economic Value Added (EVA)?
EVA is a valuation methodology trademarked by Stern
Stewart & Co. Further detailed information about EVA can be found here.
Sometimes the result tells me that the Optimal hedging strategy loses
value. Why is this?
The results depend on a balance between risk aversion
and financial exposure parameters. Sometimes the nature of the input
parameters makes this result not possible. In such circumstances, hedging
100 percent of the exposed earnings remains the optimal strategy.