|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.
|Excel Template||FAQ Link||Forum Link|
|Excel Project Management Template||FAQ||Forum|
|Excel Business Valuation Template||FAQ||Forum|
|Excel Real Options Valuation Template||FAQ||Forum|
|Excel Multiple Regression Forecasting Template||FAQ||Forum|
|Excel Portfolio Performance Tracking Template||FAQ||Forum|
|Excel Portfolio Optimization Template||FAQ||Forum|
|Excel Invoice Template||FAQ||Forum|
|Excel Bond Yield Calculator||FAQ||-|
|Excel Optimal Hedging Strategy Template||FAQ||-|
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
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
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
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
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
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
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
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
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.