Support for frequently asked questions about Business Spreadsheets templates

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 (requires Adobe Acrobat Reader).

More questions and and answers can be found in Excel Help Forums for Business corresponding to each Excel template product.

General FAQ  TOP

What version of Excel do I need?
All templates are compatible with Microsoft Excel 97 or above.

How can I open the download files?
The download files are "zipped" to to minimise the file size. You will need some extraction software like Winzip 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 Tools >> Options >> Security >> Macro Security, and selecting the Medium level. You should now be presented with the option to enable macros on opening the template file.

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 substitued 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.

I need a customized Excel solution. Does Business Spreadsheets undertake customized development work and consultancy?
No. Business Spreadsheets does not offer a service to develop customized Excel solutions. Business Spreadsheets can, however, highly recommend the fee based Excel consultancy and development services offered by Excel Business Solutions.

Project Planning and Management  TOP

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 particpant, you can add tasks using yourself as the person responsible. You can also leave the hourly rate as zero so that the Earned Value Analysis can be based on hours, rather than task cost value.

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 partipants are working on more than one of the projects as the auto-timing function will allocate time resources for the partcipants 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.

When I try to email participants, there is nothing the the body text of the emails. What should I do?
The emailing functionality uses a Windows API call and 'sendkeys' statements to compile the emails so that it will work with a variety of emailing software. Because of this, the results may vary depending on your system and emailing software configuration. It has been tested to work with Outlook, Outlook Express, and Lotus Notes. The first thing to try is to close and reopen your emailing software and try to send the emails again. If this fails, you can create individual participant workbooks using the option presented in the same form and manually send them as attachments. This method also offers the ability to update your model with the remote workbooks created.

Investment and Business Valuation  TOP

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 prioritising business proposals and accelerating approval and Decision Making processes.

Real Option Valuation  TOP

When should I use Real Option 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 Option Valuation has gained considerable popularity as a mechanism to understand and quantify the potential options embedded in proposals. Further detailed information about Real Option 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 Option model?
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.

The Game Theory model asked me for the Solver add-in. What is the Solver add-in?
The Solver add-in ships with Microsoft Excel and uses an algorithm to find solutions to "what-if" scenarios based on adjustable cells and constraint cells.

When I open the model I get a "Compile Error in hidden module" message. Why is this?
This issue has now been rectified. Please download a new version of the model here.

Multiple Regression Analysis and Forecasting  TOP

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.

Why is the number of independent variables limited to ten?
The number of independent variables that can be run is limited to ten in order to maintain reasonable processing times on slower computers. It is unusual to have more than ten independent variables that all hold a strong relationship to the dependent variable, without there being any multi-colinearity between them.

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.

Portfolio Performance Monitoring  TOP

I want to optimise 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 optimisation process which creates a hypothetical optimal portfolio to maximise the return to risk ratio. The Portfolio Optimisation 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.

Portfolio Optimization  TOP

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 optimisation can be applied at a variety of different levels within an organisation reconciling up to the total organisation. Such levels typically include business units making up the entire organisation, 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 can assist in accomplishing this.
The ability to apply optimisation analysis to a portfolio of businesses represents an excellent framework for driving capital allocation, investment, and divestment decisions.

The results are slightly different each time I run the optimization process with the same data. Why is this?
The optimization process utilises random portfolio weightings to select the most optimal one. For this reason results are likely to be slightly different each time the model is run. Increasing the number of iterations in the CoVar sheet will minimise this difference at the expense of processing time.

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.

Why does the model take so long when I have a large number of products?
The problem is due to the large number of iterations that the model uses for a large number of products in the portfolio. The time it takes will depend on the processor speed of your computer. You can, however, reduce the number of iterations to a more suitable level for your requirements by altering the formula in cell "E4" of the "CoVar" sheet. For example, altering the formula to "=MIN((G4^2)*100,1000)" will limit the number of iterations to 1,000; thus speeding up the optimization process.

Customer Invoice Template  TOP

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 retreived 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 repurpose 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 organisation and the recipient.

Bond Yield Calculator  TOP

The model asked me for the Solver add-in. What is the Solver add-in?
The Solver add-in ships with Microsoft Excel and uses an algorithm to find solutions to "what-if" scenarios based on adjustable cells and constraint cells.

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.

When I open the model I get a "Compile Error in hidden module" message. Why is this?
This issue has now been rectified. Please download a new version of the model here.

Optimal Hedging Strategy  TOP

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.