This page contains Excel and VBA (Macro) tutorial examples on various topics such as
finance, mathematics, statistics and other general issues. Users can learn Excel VBA topics range from simple issues such
as using VBA recorder to record macro, computing sum, average, median and standard deviation to advanced issues such as Black-Scholes
and Binomial option pricing models, multiple regression, portfolio optimization, probability distribution random numbers generator,
Bootstrap, Monte Carlo Simulation, risk analysis and many more.
Excel VBA examples and tutorials on this website are being
placed into the following categories:
Excel VBA Basic Tutorial Series
Excel VBA step by step examples and tutorials for beginners. Shows users how to create VBA programs from scratch. Beginner can create his or her first VBA application in minutes. Three (3) series in this section.
Intermediate and advanced Excel VBA examples on finance,
mathematics, statistics and other general issues. Thirteen (13) projects (10 with VBA source code).
Visual
Basic for Applications is very similar to the stand-alone Visual Basic programming language. It is the macro language for all of Microsoft's
major applications. This home page provides examples in VBA for Excel. One of the features that makes VBA for Excel very
powerful is its ability to use "Excel engine" in its programming language. For example, writing a program to invert a matrix
can be a very complicated and troublesome. However, one can use the Excel build-in functions to perform such tasks by incorporating
the functions into the program.
The objective of this website is to demonstrate how to apply Excel VBA in various academic fields
such as economics, finance, statistics, and mathematics and provide step by step examples in programming the projects to
help the readers gain a better understanding on how the projects are approached - though some of the coding might seem inefficient
in term of programming. The contents of this website should be used for academic purpose only, even though the examples used
can also be applying to the real world situation. All the examples can be accessed under the VBA section of this site.
P.S. There are tons of examples on this web site and new examples are being added
on a regular base. Here are some of the example collections (many free source codes):
Standard Deviation and Mean
A simple tutorial that shows users on how to compute mean and standard deviation from an array (also a demonstration of creating user-defined functions).
Lotto Number Generator
This example involves generating multiple unique random numbers from 1 to 54 with resampling without replacement technique by utilizingsorting one array based on another array.
Playing Card Probability
What is the probability of getting 3 cards with red hearts and two other cards when 5 cards are drawn from a deck? This tutorial simulates an actual scenario. The probability distribution derived from this simulation happens to be a Hypergeometric distribution.
Normal Distribution Random Number Generator
This tutorial shows how to create random numbers from a normal distribution given the standard deviation and the mean, and then computes the confidence interval given the level of significance. Also, a histogram is introduced.
Monte Carlo Integration
This example uses simulation to find the integral (area under) of a normal distribution curve within a specific interval.
Black-Scholes Option Pricing Model - European Call and Put
The example computes the European call and put price based on Black-Scholes option pricing models. Cumulative Standard Normal Distribution is also introduced.
Binomial Option Pricing Model
The example computes the European call and put price based on Binomial option pricing models. Binomial coefficient is also computed.
Portfolio Optimization
This tutorial demonstrates on how to obtain the optimal portfolio (highest return with lowest standard deviation) using Harry Markowitz theory. The largest Sharpe Ratio is used to determine the optimal set. The efficient frontier is plotted from the simulated sample.
Multiple Regression
Using matrix algebra by utilizing the Excel functions such as, MInverse( ) and MMult( ), which performs matrix inversion and matrix multiplication, respectively.
Bootstrap - A Non-Parametric Approach
Use resampling with replacement, a probability distribution for the median is created, along with the standard deviation of the median, which cannot be computed under mathematical formula (since there is none).
Multivariate Standard Normal Probability Distribution
This example is perhaps the most advanced example so far on this website. It generates multivariate standard normal distribution deviates from correlated variables and then compute the probability from the given z values. A numerical procedure, Jocobi search method is used to derive the Eigenvectors and Eigenvalues.
Monte Carlo Simulation
This tutorial is two of the most popular tutorials on this site (guess which is the other one?). By assuming underlying probability distributions (normal, uniform, and truncate normal) of the variables in the profit equation, we get a probability distribution for the profit. It answers the questions like 'what is the chance that we will loss profit' and 'what is the chance that we will make the X amount of profit.'
Option Greeks Based on Black-Scholes Option Pricing ModelThis tutorial contains option sensitivities (delta, gamma, vega, theta, and rho) formulas and source code. Option sensitivities are also know as the Greeks. They measures how sensitive the option price is toward changes in its parameters. All Greeks are available in user-defined VBA functions and in mathematical formulas.
Random Number Generator - Normal DistributionThe normal distribution is the most commonly used probability distribution in statistics. Many other probability distributions are related to this distribution. As the number of random variables increases, the distribution becomes a bell shaped curve. This curve is called the normal curve or Gaussian curve (in honor of the German mathematician Karl Friedrick Gauss, 1777-1855). The normal distribution is defined with mean and standard deviation. Random Number Generator - Log Normal DistributionThe log-normal distribution is often assumed to be the distribution of a stock price. A distribution is log-normally distributed when the natural log of the set of the random variables in that distribution is a normally distributed. In plain English, if you take the natural log of each of the random numbers from a log-normal distribution, the new number set will be normally distribution. Like the normal distribution, log-normal distribtuion is also defined with mean and standard deviation. Random Number Generator - Chi-Square Distribution The most common use of the chi-square distribution is to test the difference between proportions. It has a positive skew. The skew decreases when degree of freedom increases as the distribution approaches normal. The mean of a chi-square distribution is its degree of freedom. Random Number Generator - F-Distribution The F distribution is commonly used for ANOVA (analysis of variance), to test whether the variances of two or more populations are equal. For every F deviate, there are two degrees of freedom, one in the numerator and one in the denominator. It is the ratio of the dispersions of the two Chi-Square distributions. As both of the degree of freedom increase, the percentile value is approaching to one. F is also used in tests of ??explained variance?? and is referred to as the variance ration, Explained variance/Unexplained variance. Random Number Generator - Student-T Distribution Student T distribution is used commonly for small sample size -usually a sample size less than 30. A t distribution shares some common characteristics with the standard normal distribution. Both distributions are symmetrical, both range in value from negative infinity to positive infinity, and both have a mean of zero and standard derivation of one. However, a t distribution has a greater dispersion than the standard normal distribution. Random Number Generator - Log Pearson Type III Distribution vThe Log Pearson Type III distribution is commonly used in hydraulic studies. It is somehow similar to normal distribution, except instead of two parameters, stanand deviation and mean, it also has skew. When the skew is small, Log Pearson Type III distribution approximates normal. Random Number Generator - Multivariate Standard Normal Distribution This program is a derivation of the Multivariate Standard Normal Probability Distribution example. Users will be able to populate random multivariate standard normal deviates on the spreadsheet for analysis. For detail on this distribution, please refer to the Multivariate Standard Normal Probability Distribution example. Random Number Generator - Gamma Distribution The Gamma distribution is most often used to describe the distribution of the amount of time until the nth occurrence of an event in a Poisson process. For example, customer service or machine repair. The Gamma distribution is related to many other distributions. For example, when a Gamma distribution has an alpha of 1, Gamma(1, b), it becomes an Exponential distribution with scale parameter of b, Expo(b). And a Chi-Square distribution with k df is the same as the Gamma(k/2,2) distribution. Random Number Generator - Beta Distribution The Beta distribution can be used in the absence of data. Possible applications are estimate the proportion of defective items in a shipment or time to complete a task. The Beta distribution has two shape parameters, a1 and a2. When the two parameters are equal, the distribution is symmetrical. For example, when both a1 and a2 are equal to one, the distribution becomes uniform. If a1 is less than a2, the distribution is skewed to the left. And if a1 is greater than a2, the distribution is skewed to the right. Random Number Generator - Hypergeometirc Distribution The Hypergeometric distribution is a discrete distribution. It is alike the Binomial distribution. Both of the Hypergeometric distribution and the Binomial distribution describe the number of times an event happens in a fixed number of trials. The difference between the two distributions is that Binomial distribution trials are independent, while Hypergeometric distribution trials change the probability for each subsequent trial and are called sampling without replacement. Random Number Generator - Triangular Distribution The Triangular distribution is often used when no or little data is available. It has 3 parameters, the minimum and the maximum that defines the range, and the more likely (the peak). The distribution is skewed to the left when the peak is closed to the minimum and to the right when the peak is closed to the maximum. It is a simple distribution that as its name implied, has a triangular shape. Random Number Generator - Binomial Distribution The Binomial distribution describes the number of successes in t independent Bernoulli (yes or no) trails with probability p of success on each trial. It is used to answer questions such as how many times a head will come up when a coin is flipped 5 times or how many defective items will be found in 20 items. Numerical Searching Method - Newton-Ralphson Newton-Ralphson method - or simply the Newton\'s method is one of the most commonly used numerical searching method for solving equations. Usually Newton\'s method converges well and quickly, but the convergence is not guaranteed. Newton\'s method requires an initial value. This values can determine the way the search is converged. The major challenge to using this method is that the first differential (first derivative) of the equation is required as an input for the search precedure. Numerical Searching Method - Secant Method Secant method, unlike the Newton-Ralphson method, does not require the differentiation of the equation in question. Because of that, it can be used to solve complex equations without the difficulty that one might have to encounter in trying to differentiate the equations. Secant method requires two initial values. Test shows that this method converge a little bit slower than the Newton-Ralphson method. Implied Standard Deviation For Black/Scholes Call - Newton Approach The implied standard Deviation or implied volatility is the volatility value that would make the theoretical value (in this case the Black-Scholes Model) equals to the given market price. To use Newton-Ralphson method, the first differential of the standard deviation with respect to the price (Black/Scholes) is required. In this case, we can use Vega (Kappa) the sensitivity of the call price to the implied standard deviation. Implied Standard Deviation For Black/Scholes Call - Secant Approach Unlike Newton-Ralphson precedure, Secant method does not require the first differential of the of the standard deviation with respect to the price (Black/Scholes) as an input. This makes Secant method a more convenient tool to use. Nevertheless, it does require an initial value for the iteration just as any other numerical precedures. Secant method does not converge as fast as the Newton-Ralphson method. Implied Standard Deviation For Black/Scholes Call - Bisection ApproachBisection searching method utilizes linear interpolation. It uses a minimum and a maximum starting numbers in the iteration process. The steps it takes to convert depends greatly on the starting numbers. In general, this method takes more iterations to convert compares to the Newton method. Black-Scholes Option Pricing Model - European Call and Put In this example, we derived call and put option price based on the Black-Scholes model. The function procedures are used. The first function, SNorm(z), computes the probability from negative infinity to z under standard normal curve. This function provides results similar to those provided by NORMSDIST( ) on Excel. The second function and the third function compute call and put prices, respectively. Option Greeks Based on Black-Scholes Option Pricing Model This program contains option sensitivities (delta, gamma, vega, theta, and rho) formulas and source code. Option sensitivities are also know as the Greeks. They measures how sensitive the option price is toward changes in its parameters. All Greeks are available in user-defined VBA functions with mathematical formulas. European Option Model on Asset with Known Cash Payouts When a stock issues dividend, cash is paid to the holder of the asset. The call holder does not receive any part of the payout. When the stock goes ex-dividend, its value will usually decreased by approximately the amount of the dividend distribution. European Option Model on Asset with Continuous Cash Payouts (Index Option)Some assets have numerous distribution of cash payouts. An example is a broad-based stock market index portfolio (say SP500), in which nearly everyday one component stock or another will pay a dividend. Merton (1973) has derived a variant of the Black-Scholes model for an asset that pays dividends continuously. European Option Model on Currency In 1983, Garman and Kohlhagen developed a model that computes European currency options. This program demostrates the computation of Currency option prices. European Option Model on Futures Black in 1976, developed a variant of his basic model that can be applied to compute options on futures and forward contracts. The following demostrates the computation of futures option prices.
Excel Products: Templates | Business Models | Add-Ins
Excel Templates
Exchange Rates for ExcelExchange Rates for Excel is a practical tool in which you can update automatically through Internet the currency exchange rates into your Excel spreadsheets. No more manual and tedious updates or risk of using no updated data. Exchange Rates for Excel is like any other Excel function, when the function is inserted in any cell or inside a formula, the add-in automatically refreshes the exchange rates with the latest data. Exchange Rates for Excel includes a wizard to help you to use the tool. Additionally it includes different refreshing data options and reports with the exchange rates and currency codes. Profit Contribution Breakdown ExcelProfit Contribution Breakdown allows you to identify the level of surplus/profit generated by components of your business. By applying relative indicators to reflect the amount of variable and fixed expense (resource) a component consumes surplus and contribution levels are calculated. This component performance is combined with indicators for future potential and business ability to identify areas that will maximize current and future performance. Summary recommendations are provided for each breakdown and results presented in graphical and tabular form. This business tool can analyze any chosen component of an overall business or product line. Default breakdown is five Customer Segments and five Product Groups. Product and Supplier Profitability ExcelDetermine your profit by product and supplier. Product and Supplier Profitability allows you to identify the true costs associated with your products. It does this by considering initial costs (purchase, transport, receiving, and reject), on-going costs (storage, overheads), as well as finance and customer return costs. Calculations are presented in both Form and Table view for effective display. Supplier/Product profiles are created in Form view and cost analysis by unit and profile applied. Sensitivity functions allow you to test variations for Stockturn and Customer Returns. Pricing and Breakeven Analysis ExcelPricing and Breakeven Analysis uses break even analysis to calculate your current business break even point using revenue, variable, and fixed cost inputs. This is combined with price elasticity (estimates for price and sales volume variations) to produce revenue and surplus (profit/loss) forecasts by price. The model determines the Optimum Pricing to maximize your surplus and can be applied to new or established businesses, product/service lines, or individual items. Instant Quote ProfessionalInstant Quote Professional, now powered by MS Office Access 2007, is a feature rich software application designed for Any business that requires quotes and invoices. Instant Quote Pro takes the burden out of quoting and invoicing, and allows you to easily create them in seconds! With this software, you will be highly organized. Sales Leads 2008 for Excel 2007Our Sales Leads 2008 CD for Excel 2007 supplies over 10 million sales contacts and addresses sorted in to 51 States.Excel 2007 has expanded capacities from 65,000 maximum rows in Excel 2003 to one million, and this enables Excel 2007 to provide this huge store of of sales leads. Quick 3 Year PlanThree year cash flow plan helps you combine monthly forecasts quickly easily and convert into cash flows Forecasting calculations are so easy with Excel. Excel links spreadsheets together so that you can filter and sort records just anyway you want. Monte Carlo Forecasting SimsThe Sales & Profit Forecasting Simulator will accurately predict your company sales and profits by combining up to 15 different product or profit center estimates of incoming sales leads, costs per lead, leads conversion rates, price yields, gross profit margins, G&A recovery rates and their upper and lower limits. Forecasts probabilities achieving any sales or profit targets using Monte Carlo simulations. Inventory Manager PRO For ExcelInventory Manager provides manufacturing and operations decision support for inventory management and scheduling with Excel. Maximum 10,000 inventory items capacity. Determine the best inventory re-order schedules (includes safety stocks), optimize the lowest cost re-order quantities, establish ordering frequencies and lot sizes, receive alerts when you need to re-order, includes seasonality, trends and forecasting, quality control inventory analysis. Improve efficiencies and enhance customer service with INVENTORY MANAGER PRO. Financial Ratios with ExcelWindex calculates all major financial ratios in Excel. Calculates these financial ratios from balance sheet, income statement and cash flow templates provided. FastPlan 2008 Five Year Company ForecastingFastPlan version 9.6 provides a complete five year financial plan incorporating monthly fixed asset schedules with depreciation spreadsheets, 60 x monthly sales forecasts for multi products, forecast expenses, share capital inputs, three loan notes providing monthly interest charges and declining loan balances, plus working capital forecasts to generate five annual and monthly balance sheets, income statements and cash flow forecasts plus all core ratios and five years financial summary overview. FASTPLAN Version 9.6FastPlan version 9.6 provides a complete five year financial plan incorporating monthly fixed asset schedules with depreciation spreadsheets, 60 x monthly sales forecasts for multi products, forecast expenses, share capital inputs, three loan notes providing monthly interest charges and declining loan balances, plus working capital forecasts to generate five annual and monthly balance sheets, income statements and cash flow forecasts with supporting financial ratios and five years financial summary overview. Excel Costing 2008Standard costs, activity based costs, overhead cost recovery ratios, cost drivers, absorption costs and cost allocation ratios in Excel. Complete range of product costing formula with eight ready to use costing templates iin the first workbook with Excel costing formula provide unit costs for pricing and profit recovery calculations. Excel Budgeting & Financial ForecastingMaster Budgets 4.6 helps you combine up to 10 department budgets quickly and easily and tracks actuals and variances. Create seperate 12 monthly budgets and Excel consolidates to a Year to Date summary tracking actuals vs budgets. Cash Flow Forecasting 2008.xls will convert any list of AR-AP accounts into a daily and calendar month cash flow forecast up to 150 days ahead from users starting date. Import accounts payable and receivable into this Excel workbook from any accounting package and generate an instant 150 day cash flow forecast. Manage slow payers and accounts payable more effectively with closer identification of cash flow forecasts. Complete Excel Templates CollectionThe Complete Excel Templates 2008 Collection provides 150 business finance spreadsheets, Excel add–ins and Excel Learning Modules plus nine Risk Analysis Forecasting spreadsheets which will help you manage financial risks and set goals quickly and effectively. Investment-Calc 2008 spreadsheets for Present values and internal rate of return of periodic cash flows, future values of money flows, present values compounded between dates, present values of a recurring amount, present value of periodic investments. The new 2008 edition includes acquisition synergy net present values, cost of capital, terminal (to infinity) net present values plus an updated Quick Guide to Investment Decisions. Complete Excel Collection - 2008 EditionComplete Excel 2008 Collection - Upgrade/ Cash Flow & Liquidity Forecasting 2008150 days Cash flow control and forecasting in Excel plus liquidity forecasting and goal setting. Export your current accounts receivable and payable from your accounting software into this Excel workbook and you instantly create a complete analysis of Late (receivable) and Overdue (payable) accounts together with daily, weekly and monthly summaries of total cash flow forecasts and closing cash balances. Budgeting Spreadsheets for ExcelOur complete collection of our Budgeting workbooks provides 50 business finance spreadsheets plus 9 Excel Add-ins and full Excel Training Course to help you create, control and value your budgets quickly and easily. The following is a partial summary: Master Budgets 4.6 helps you combine up to 10 department budgets quickly easily Wage Calculator ExcelThe Wage Calculator allows you to define roster schedules and will automatically calculate associated wage costs by staff, Area, and Department for each day, and the week. It uses a base wage and up to six additional wage loadings to determine the wage structure. Wage classifications, and staff can be freely added. The roster format is by Department and Area, with two staff work periods per day to accommodate split shifts. Shift Scheduler Continuous ExcelThe Shift Scheduler will build a roster structure to your specifications, allocate staff automatically, and generate a printable roster, all at the click of a button. It is compact and user friendly allowing you to add staff and shifts (maximum 150) with ease. Default values for continuous shifts mean that the only shift data required is the start date/time and length for the first shift, everything else is automated. For non-continuous or varying shifts you have the capacity to overwrite default data and structure shift settings to suit your specific requirements. Profit Contribution Breakdown ExcelProfit Contribution Breakdown allows you to identify the level of surplus/profit generated by components of your business. By applying relative indicators to reflect the amount of variable and fixed expense (resource) a component consumes surplus and contribution levels are calculated. This component performance is combined with indicators for future potential and business ability to identify areas that will maximize current and future performance. Product and Supplier Profitability ExcelDetermine your profit by product and supplier. Product and Supplier Profitability allows you to identify the true costs associated with your products. It does this by considering initial costs (purchase, transport, receiving, and reject), on-going costs (storage, overheads), as well as finance and customer return costs. Calculations are presented in both Form and Table view for effective display. Supplier/Product profiles are created in Form view and cost analysis by unit and profile applied. Sensitivity functions allow you to test variations for Stockturn and Customer Returns. Pricing and Breakeven Analysis ExcelPricing and Breakeven Analysis Excel will determine the impact of a price change on your business. It calculates current breakeven points using revenue, variable cost, and fixed cost inputs. These are combined with estimates for price and sales volume variations to produce revenue and surplus (profit/loss) forecasts by price. The model determines the Optimum Pricing to maximize your surplus and can be applied to new or established businesses, product/service lines, or individual items.
Excel Models
Queuing Model ExcelThe Queuing Model will calculate the optimum number of customer service points (staff) to minimize costs for your business. It considers the average arrival rate of customers, the average customer service rate, the cost to the business of customer waiting time (customer dissatisfaction), and the cost to operate customer service points. Queuing models are used extensively in call centers, toll booth operations and situations where a there is a queue for service including, counter staff, service staff, call response staff or maintenance staff. Decision Assistant Model ExcelThe Decision Assistant Model Excel allows you to value a decision and determine the impact on your business. It calculates the range of possible outcomes for your decision, their likelihood of occurring, the average outcome, and the probability of the outcome being positive (profitable). It applies three decision return estimates (Pessimistic, Middle, and Optimistic) and associated decision costs. Outputs cover a 12 month period and are presented in tabular and graphical form. Outputs include a decision Output Curve, Cash Flow Scenarios, a Risk Profile, and an Action Sheet. Business Valuation Model ExcelThe Business Valuation Model is software designed to provide an economical, efficient and effective business valuation. It combines relative indicators for future performance with basic financial data (Revenue, Variable and Fixed Costs) to value a business. By uniquely applying your intuitive business and market knowledge a 3 year performance forecast with sensitivity analysis, investment return, and a business valuation is calculated. It is compact, easy to use, and requires minimal inputs. Input should be based on your subjective views. These are translated into quantifiable values for model operation. There is no need to provide \"perfect\" answers. Billing Model ExcelThis model was originally designed to test changes in billing structures for a Medical Practice (Public versus Private Billing), however it is equally suited to any professional practice and can be used to test the impact of fee changes. When billing structures are altered they impact on client numbers and in turn revenue and profit. The Billing Model allows you to forecast what this impact will be. It does this using a Client Number Change Percentage. Financial Excel All Programs EditionThe Financial Excel All Programs Edition provides over 100 business finance spreadsheets, Excel add – ins and Excel Learning Modules. Queuing Model ExcelThe Queuing Model will calculate the optimum number of customer service points (staff) to minimize costs for your business. It considers the average arrival rate of customers, the average customer service rate, the cost to the business of customer waiting time (customer dissatisfaction), and the cost to operate customer service points. Queuing models are used extensively in call centers, toll booth operations and situations where a there is a queue for service including, counter staff, service staff, call response staff or maintenance staff. Production Mix Model ExcelThis model calculates the Product or Service Mix that maximizes overall profit considering available resources. It will identify the mix (number) of each Production Item to produce and any remaining resource. Up to six unique Production Items can be handled with ten specific resource inputs. Production Items can be considered as the output of any process including manufactured items and the provision of services. Resources are anything that is needed to produce or provide the Production Item. This may include materials, labor, machine time, floor space, transportation services etc. Decision Assistant Model ExcelThe Decision Assistant Model Excel allows you to value a decision and determine the impact on your business. It calculates the range of possible outcomes for your decision, their likelihood of occurring, the average outcome, and the probability of the outcome being positive (profitable). It applies three decision return estimates (Pessimistic, Middle, and Optimistic) and associated decision costs. Business Valuation Model ExcelThe Business Valuation Model Excel combines relative indicators for future performance with basic financial data (Revenue, Variable and Fixed Costs) to value the business. This valuation method can be used for business purchase, sale, or establishment. The model uniquely applies your intuitive business and market knowledge to provide a 3 year performance forecast with sensitivity analysis, investment return, and a business valuation. Business Performance Analysis ModulesBusinessPerformanceAnalysis.com provide a suite of easy to use business performance analysis services. These Analysis Modules are delivered via a browser based software interface and consist of business forecasting and business valuation modules. You can access all Analysis Modules directly from our site or download and run the Business Performance Analysis Modules on your computer. All modules are designed to run in your browser, do not alter your system in any way and all data is saved locally on your computer. This provides the highest level of security possible, protecting you, your computer and your data. Budget Tool Business ExcelThe Budget Tool Business Excel provides an effective means to generate a budget for up to five years. Full flexibility allows you to add Revenue, Variable Cost, and Fixed Cost categories and lines to suit any business. Operation is via an easy to use toolbar and function buttons. Billing Model ExcelThis model was originally designed to test changes in billing structures for a Medical Practice (Public versus Private Billing), however it is equally suited to any professional practice and can be used to test the impact of fee changes. When billing structures are altered they impact on client numbers and in turn revenue and profit. The Billing Model allows you to forecast what this impact will be. It does this using a Client Number Change Percentage. This is the percentage change in client numbers when your billing structure is changed. For each business this is influenced by the level of competition, differentiation of the business, client profiles, and type of service provided. An estimate of this percentage combined with basic data on average billing fees and cost percentages allow revenue, profit and client numbers to be forecast for the range of billing fee combinations.
Excel Finance Templates
Profit Loss Report SpreadsheetThe Forecast and Budget Builder Excel is a streamlined tool for developing a 3 year business forecast and a 12 month budget. It requires minimal inputs and generates Optimistic to Pessimistic forecasts for your business. From your forecasts a 12 month master budget is built. You then have the capacity to refine this budget as required. Forecast and Budget Builder ExcelThe Forecast and Budget Builder Excel is a streamlined tool for developing a 3 year business forecast and a 12 month budget. It requires minimal inputs and generates Optimistic to Pessimistic forecasts for your business. From your forecasts a 12 month master budget is built. You then have the capacity to refine this budget as required. Budget Tool Business ExcelThe Budget Tool Business Excel provides an effective means to generate a budget for up to five years. Full flexibility allows you to add Revenue, Variable Cost, and Fixed Cost categories and lines to suit any business. Operation is via an easy to use toolbar and function buttons. The budget can be displayed in any combination of month, quarter, or year. Change indicators provide means to adjust revenue and costs year on year with initial default values applied to minimize input requirements and simplify use. Full what if and sensitivity analysis is available by varying the Change indicators. Budgets can be freely formatted for printing and graphing. The Budget Tool Business is suitable for both existing and proposed businesses. Budget Compiler QuickBooks ExcelThe Budget Compiler will apply and format your Profit and Loss data from QuickBooks. Using this information it will compile a flexible Excel Profit and Loss report allowing you to select the amount of detail displayed and use the data for performance review and budget formulation. The created budget can be imported to the Quickbooks budget or used as a standalone tool. Applying your existing data in a flexible and easy to read layout with the capacity to automate budget creation provides substantial benefits. These include an improved understanding of business operations, enhanced budget development, and significant time saving in performance reviews and budget creation. Display view options include by Month, Quarter, and Full Year with Current Year, Last Year, and Budget values. Charts display both Actual and Budget values. Universal Pricing CalculatorsThe Universal Pricing Excel Spreadsheets calculate price - demand relationships and uses standard accounting formula to give you the exact price which will produce the maximum profit for any product. The user supplies two datasets, demand1@price1 and demand2@price2. The program calculates elasticity of demand (change in demand for $1 change in price) and the price which yields the maximum profit. Savings & Investment PlannerSavings & Investment Planner provides the perfect financial assistance if you want to seriously improve your personal finances. Savings template. Personal expenses spreadsheet to enter all your expenses as they occur. Use + values for really essential expense items and – values for non essential expenses. Excel works out the exact amounts of essential/non essential expenses, monthly savings if you eliminate all non essential costs and the percentage spent on non essentials. QuickValue PRO 2008QuickValue PRO 5.0 provides all our company valuation spreadsheets plus eight free bonus Excel Add-in files to make your Excel 100 times more productive. Quick Value helps you create valuations quickly and easily. The following is a partial summary: Quick Five Year Financial Plans Plus RatiosFastPlan provides a complete five year financial plan using Microsoft Excel to create balance sheets, income statements and cash flow forecasts. Also included in the CD edition are our Company Valuation spreadsheets using multiple ratios to obtain valuations, plus a complete Agreement to Buy or Sell a Business (legal Agreement template Word file). Lease/Loan OrganizerLoans & Lease Organizer will manage up to 100 lease/loan accounts into combined month end reports. Combines current period payments of principal and interest, provides outstanding balances, months remaining, payments remaining and asset book values (S/L or DDB depreciation). Investment-Calc 2008 - NPV - IRR SolutionsInvestment Appraisal (Monte Carlo investment outcome simulation). This spreadsheet will run 1,000 forecasts combining up to 80 variables of any investment project to predict the most probable 10 year pre-tax profits, cash flows, NPV, IRR and ROIC. Instant Balance Sheets Excel TemplatesInstant Balance Sheets provides 12 monthly linked balance sheet templates which always balance. You can quickly and easily create 12 month profit & loss schedules and cash flows (source and application of funds) which automates all the figures in your 12 monthly balance sheets. Ideal for when you want to turn your monthly profit and loss forecasts or last 12 months data into monthly and year end balance sheets and cash flows. INVESTMENT-CALC PRO 2008 IRR & NPV SolutionsInvestment Appraisal (Monte Carlo investment outcome simulation). This spreadsheet will run 1,000 forecasts combining up to 80 variables of any investment project to predict the most probable 10 year pre-tax profits, cash flows, NPV, IRR and ROIC. Google Spreadsheets Excel Templates Collection25 Excel files in total. 23 upload and run in Google Docs Spreadsheets, plus 2 Cash Flow Forecasting with file sizes that are greater then the Google Docs limit which enables larger data volumes. Funding Plan PRO 10 Year Plan for ExcelFunding Plan PRO provides a complete ten year financial plan incorporating monthly fixed asset schedules with depreciation spreadsheets, monthly and three monthly sales forecasts for multi products, forecast expenses, share capital inputs, three loan notes providing monthly interest charges and declining loan balances, plus working capital forecasts to generate five annual and monthly balance sheets, income statements and cash flow forecasts with supporting financial ratios and five years financial summary overview. Fixed Asset Accounting With ExcelOur fixed asset accounting workbooks provide all with all you need to manage and keep track of up to 10,000 asset records in Excel plus depreciation and book values. Each asset is defined by location (100 possible), asset class (100 possible) and depreciation months and salvage percentage.
Excel Add-ins & Softwares
Conditional Formatting for ExcelIn order to ease the analysis of specific data in your spreadsheet, you might give it a special format (for example: highlighting it), using one of the most useful tool that Microsoft Excel has, \"Conditional Formatting\", but the bad thing is that it just supports until 3 conditional levels. Well, Jabsoft, thinking of this lack, has created: Excel Conditional Formatting, an excellent addin, which allows you formatting all the cells and ranges that you need, using \"n\" conditions (in other words, all of conditions you desire) Color Palette for ExcelBring life, color and style to your Excel charts and reports. Color Palette for Excel will allow you to add new, brilliant and professionally selected colors to Excel and give a new and personal look to your charts and reports. Chart Tools for ExcelUsing Chart Tools for Excel, you will become convinced that it has never been so easy to handle Excel charts. Categorizing Data for ExcelIf your problem consists in categorizing or stratifying specific data of your Excel spreadsheet information, then Categorizing Data for Excel is the solution. VehiCalc Car Loan/Lease Analyzer Home EdVehiCalc Car Loan/Lease Analyzer allows you to quickly calculate lease and loan payments as well as compare the true overall cost of your lease versus purchase. This will allow you to determine whether leasing truly is the better option even if the payment amount is significantly lower than borrowing. Prepared to be surprised! Want even more money saving tools? The Loan Amortization Schedules included allow you to generate flexible schedules that allow extra, skipped and varying payment amounts. Amortization Tables, a Solve for Missing Number calculator, plus dozens of financial calculators round out this offering. QuikCalc Mortgage & Loan Manager Personal EditionMortgage and loan software includes the powerful Amortization Schedules Plus! feature that allows you to override, skip or add any number of payments. The Loan Management feature allows you to track actual loan terms and varying interest rates, as well as your actual payments made and projected payments. The Loan Manager also allows you to assess late fees and charges and track insurance, taxes, etc. Includes 14 different Day Count Conventions including 30/360 and Actual/Actual, plus many different Payment Types including Normal, Continually Adjusting Payments, Fixed Principal plus Interest and Interest Only. QuikCalc Mortgage & Loan Manager Lite EditionMortgage and loan software includes the powerful Amortization Schedules Plus! feature that allows you to override, skip or add any number of payments. Includes 14 different Day Count Conventions including 30/360 and Actual/Actual, plus many different Payment Types including Normal, Continually Adjusting Payments, Fixed Principal plus Interest and Interest Only. Compare schedules side-by-side and see before and after results of your changes. Also includes dozens of powerful calculators as well as many reports including annual summaries. QuikCalc Amortization Premium EditionMortgage and loan software includes the powerful Amortization Schedules Plus! feature that allows you to override, skip or add any number of payments. The Loan Management feature allows you to track actual loan terms and varying interest rates, as well as your actual payments made and projected payments. The Loan Manager also allows you to assess late fees and charges and track insurance, taxes, etc. Includes 14 different Day Count Conventions including 30/360 and Actual/Actual, plus many different Payment Types including Normal, Continually Adjusting Payments, Fixed Principal plus Interest and Interest Only. Also includes dozens of powerful calculators as well as many reports including annual summaries and a Solve for Missing Number Calculator. Data file backup, integrated calculator. QuikCalc Amortization Plus! EditionMortgage and loan software includes the powerful Amortization Schedules Plus! feature that allows you to override, skip or add any number of payments. Includes 14 different Day Count Conventions including 30/360 and Actual/Actual, plus many different Payment Types including Normal, Continually Adjusting Payments, Fixed Principal plus Interest and Interest Only. Compare schedules side-by-side and see before and after results of your changes. Also includes access for up to 5 loans to the Premium Edition"s Loan Manager and a Solve for Missing Number Calculator. Also includes dozens of powerful calculators as well as many reports including annual summaries. Data file backup, integrated calculator. Also available in three additional versions. Loan Payment CalculatorLoan Payment calculator software includes easy to use amortization tables and schedules that allow any interest rate, payment and compounding frequency, including accelerated payments. Includes many reports including annual summaries. See before and after comparisons of your changes. Data file backup and integrated field level popup calculator also included. Also available in three additional versions, QuikCalc Plus! QuikCalc AmortizationMortgage and loan software includes easy to use amortization tables and schedules that allow any interest rate, payment and compounding frequency, including accelerated payments. Includes many reports including annual summaries. See before and after comparisons of your changes. DebtCalc Debt Elimination Planner Home EditionQuickly add any number of debts. Whether you have two or ten debts (or even 100 for that matter), DebtCalc can handle it. The Debt Elimination Planner allows you to enter all manner of debts including loans, mortgages, credit cards and lines of credit. DebtCalc Debt Elimination Calc Plus! Ed.Quickly add any number of debts. Whether you have two or ten debts (or even 100 for that matter), DebtCalc can handle it. The Debt Elimination Planner allows you to enter all manner of debts including loans, mortgages, credit cards and lines of credit. Wage Calculator ExcelThe Wage Calculator allows you to define roster schedules and will automatically calculate associated wage costs by staff, Area, and Department for each day, and the week. It uses a base wage and up to six additional wage loadings to determine the wage structure. Wage classifications, and staff can be freely added. The roster format is by Department and Area, with two staff work periods per day to accommodate split shifts. Department, Area and staff can be configured to suit almost any business strucure and size. Once you have set up your wage and roster frame work you can easily allocate staff to work periods to build your weekly roster and at the click of a button calculate wage costs by day and week. Standard Excel print functions are available providing flexible print formats. Using the Wage Calculator you can define your staff roster schedules and calculate your wage costs with ease. Shift Scheduler Continuous ExcelThe Shift Scheduler will build a roster structure to your specifications, allocate staff automatically, and generate a printable roster, all at the click of a button. It is compact and user friendly allowing you to add staff and shifts (maximum 150) with ease. Default values for continuous shifts mean that the only shift data required is the start date/time and length for the first shift, everything else is automated. For non-continuous or varying shifts you have the capacity to overwrite default data and structure shift settings to suit your specific requirements. Password Page Protection SoftwarePassword Page Protection Software allows you to add password protection to any web page. It generates password protection code which is added to the top of a web page to prevent unauthorized access. When the web page is called from the server the password protection code runs before the web page is sent to the user. Instead a password request page is displayed in the users browser. When the user submits a password it is compared to the password set in the page protection code. If they match the protected web page is sent from the server. This provides a high level of security and reliability. MailMailer SoftwareMailMailer is a compact easy to use server (web) based email mailer. It is specifically designed for the small business market, not to be the biggest or most featured but to be a clean efficient contact tool helping you to send newsletters to your clients, contact your staff, send one email or many. Form1 Builder SoftwareInvestment Analysis Software provides the capacity to easily analyze the performance of any investment. It applies basic input variables to build an investment analysis by year. Investment performance is converted to equivalent current year values enabling the actual investment performance to be determined. Applying this approach allows comparisons to be made between different investment types. It also provides an effective means of projecting investment performance and determining the results of different investment mixes. Loan & Bank Refund Claim Software 2008Our software enables you to calculate the additional interest you can claim over and above the overcharges debited by your lender plus it will help you write the letter to send your lender substanting your claim for a refund check. Five Keys To Business Success (Excel Add-Ins)Excel add-ins provide ready made solution for forecasting, investment economics, portfolio optimization, business resource optimization and material planning. Excel Add-ins & Training Bible Complete CollectionCombined Excel Add-ins and Excel Training Bible package. Nine Excel add-ins plus the Complete Excel Training Bible modules 101-701. Excel Add-Ins CollectionShare Offering Memorandum pro-forma wording and layout to make it quick and easy for you to raise $1,000,000 new equity or loan capital. Save $10,000 - $25,000 on legal fees. Just add the figures and your business plan into the document provided following the guidance notes. Share subscription agreements are supplied. Help provided for non US companies how to structure an offering to secure finance. Popular with start ups, franchises, entrepreneurs and small businesses. List of capital sources supplied. SiteMap XML SoftwareSiteMap XML Dynamic SiteMap Generator Software, is a php based sitemap generator for Google, RSS, HTML and Text SiteMaps. It includes a range of configurable variables to define the SiteMap type and content and can be run as an online form for Static SiteMaps or to create real time Dynamic SiteMaps that can be directly called. It is designed for ease of use, with the ability to define the desired site map type, content and settings. SiteMap XML is best suited to medium size sites of up to 5000 pages. Google is currently using XML site maps to assist the indexing process. Site Builder SoftwareSite Builder Software allows you to easily build a dynamic web site with an automatically generated menu structure, auto keyword and description meta tag generation, inbuilt RSS feeds and valid XHTML coding. Budget Compiler QuickBooks ExcelBudget Compiler QuickBooks Excel PL Compiler MYOB ExcelThe PL Complier will import and format your monthly Profit and Loss data from MYOB. Using this information it will compile a flexible Excel Profit and Loss report allowing you to select the amount of detail displayed and use the data for performance review and budget formulation. Applying your existing data in a flexible and easy to read layout with the capacity to automate budget creation provides substantial benefits. These include an improved understanding of business operations, enhanced budget development, and significant time saving in performance reviews and budget creation. Display view options include by Month, Quarter, and Full Year with Current Year, Last Year, and Budget values. Charts display both Actual and Budget values. Investment Analysis SoftwareInvestment Analysis Software provides the capacity to easily analyze the performance of any investment. It applies basic input variables to build an investment analysis by year. Investment performance is converted to equivalent current year values enabling the actual investment performance to be determined. Applying this approach allows comparisons to be made between different investment types. It also provides an effective means of projecting investment performance and determining the results of different investment mixes. Form1 Builder MYSQLWith Form1 Builder MYSQL you can automatically have form submission data added to your MYSQL database. To use the MYSQL option you must have a server based MYSQL database and it must have an existing table with field names that correspond to form field names. Form1 Builder GoldMineWith Form1 Builder GoldMine you can automatically use GoldMine"s WebImport function to add form submission data directly to your GoldMine database. It also allows you to specify the full range of GoldMine functions. This includes running duplication checks and automatic processes. Instructions for these are easily set in the form and processed when GoldMine reads the GoldMine formatted email with the form data. Use Form1 Builder GoldMine to add WebImport forms to your site. Forecast and Budget Builder ExcelThe Forecast and Budget Builder Excel is a streamlined tool for developing a 3 year business forecast and a 12 month budget. It requires minimal inputs and generates Optimistic to Pessimistic forecasts for your business. From your forecasts a 12 month master budget is built. You then have the capacity to refine this budget as required. The methodology used applies relationships between current data and relative indicators for future performance to produce a verifiable budget quickly and easily. This forecast and budget tool can be utilized for existing and proposed businesses, products, or services. Outputs include a 3 Year Forecast with Sensitivity Analysis, and a Budget by Month in tabular and graphical form.
Learn Excel and Excel VBA
Complete Excel CourseThese lessons have been written by OzGrid Business Applications who develop Excel spreadsheets on a professional basis. Each lesson is drawn from 'real world' experience and not made up class room scenarios. There are 50 lessons, each between 5 and 10 Word Document pages long, and 25 Workbook examples. There is also an online Microsoft Pivot Table tutorial as well as Time Saving Microsoft Excel Solutions. Excel Training BibleComplete training in all aspects of Excel applications with spreadsheet examples. Learn all the hidden secrets within Excel for more effective working and save hours of work time. Unlimited version licenses means that the download files can be shared among work colleagues. Learn how to write Macros to automate repetitive tasks in your Excel Step by step development of VBA applications and you can copy the VBA codes into your Excel VBA editor. Excel and Excel VBA Tips and TricksThis HUGE collection has a Workbook with special tips on Conditional Formatting, Vlookup, Validation, Dynamic Ranges within a range and some slick uses for Checkboxes. There are some links to some excellent Excel software sites that sell Excel software and Add-ins. Excel Download PackageThis package includes 33 fully accessible Workbook examples that cover topics like Vlookup, Match, Index, Dfuntions, Advanced Filter, Validation, Conditional Formatting, Custom VBA Functions, VBA Functions, Listbox, Combobox, Working with Excel Menus in VBA, Printing Long List Code, Dynamic Named Ranges and much more!
Excel VBA Tools
PL Compiler MYOB ExcelThe PL Complier will import and format your monthly Profit and Loss data from MYOB. Using this information it will compile a flexible Excel Profit and Loss report allowing you to select the amount of detail displayed and use the data for performance review and budget formulation. Applying your existing data in a flexible and easy to read layout with the capacity to automate budget creation provides substantial benefits. These include an improved understanding of business operations, enhanced budget development, and significant time saving in performance reviews and budget creation. Display view options include by Month, Quarter, and Full Year with Current Year, Last Year, and Budget values. Charts display both Actual and Budget values. Form1 Builder MYSQLWith Form1 Builder MYSQL you can automatically have form submission data added to your MYSQL database. To use the MYSQL option you must have a server based MYSQL database and it must have an existing table with field names that correspond to form field names. On form submission a new record is added to the MYSQL database table and MYSQL database table fields that have the same name as form fields will include the input values from the form field Additional form variables can be added to the MYSQL table by including a field in the MYSQL table that has the same name as the field to be included. Form1 Builder GoldMineWith Form1 Builder GoldMine you can automatically use GoldMine's WebImport function to add form submission data directly to your GoldMine database. It also allows you to specify the full range of GoldMine functions. This includes running duplication checks and automatic processes. Instructions for these are easily set in the form and processed when GoldMine reads the GoldMine formatted email with the form data. Use Form1 Builder GoldMine to add WebImport forms to your site. The software includes the base Form1 Builder plus an additional GoldMine WebImport feature. The GoldMine WebImport feature formats the form data and adds processing instructions allowing a GoldMine database to recognize the import instructions in the email, and create a contact record with data captured from the web form, automatically. Form1 (the generated form code) runs on your web server. To run Form1 your server must support the php scripting language. Most commercial web servers support php.
10/8/2008
To provide easy and convenient access to our site, we have transitioned to a new layout. If you prefer to view the classic version, please click the link below.
Creating Your First Macro
In this sub section, we will show you how to create your first macro
(VBA program). We will use the world classic "Hello World!"
example.
Recording a Marco
In this example, we will record a macro that sets the cell background
color to light yellow.
Modules and Procedures and
Their Scope
A module
is a container for procedures as shown in our prior examples. A procedure
is a unit of code enclosed either between the Sub
and End Sub statement or between the Function and End
Function statements.
If you pass an argument by reference
when calling a procedure, the procedure access to the actual variable
in memory. As a result, the variable's value can be changed by the procedure.
Objects are the fundamental building
blocks of Visual Basic. Anobject is a special type of variable
that contains both data and codes. A collection is a group of
objects of the same class. The most used Excel objects in VBA programming
are Workbook, Worksheet, Sheet, and Range.
A workbook
is the same as an Excel file. The Workbook collection contains all the
workbooks that are currently opened. Inside of a workbook contains at
least one worksheet.
Range
represents a cell, a row, a column, a selection of cells containing
one or more contiguous blocks of cells, or a 3-D range. We will show
you some examples on how Range object can be used.
Each object contains its own methods
and properties. A Property represents a built-in or user-defined
characteristic of the object. A method
is an action that you perform with an object.
Objects are the fundamental building
blocks of Visual Basic. An object is a special type of variable
that contains both data and codes. A collection is a group of
objects of the same class. The most used Excel objects in VBA programming
are Workbook, Worksheet, Sheet, and Range.
A workbook
is the same as an Excel file. The Workbook collection contains all the
workbooks that are currently opened. Inside of a workbook contains at
least one worksheet.
Range
represents a cell, a row, a column, a selection of cells containing
one or more contiguous blocks of cells, or a 3-D range. We will show
you some examples on how Range object can be used.
Each object contains its own methods
and properties. A Property represents a built-in or user-defined
characteristic of the object. A method
is an action that you perform with an object.
This is an essential skill for creating
a simulation. Topics in this section cover declare an array, resize
an array, manage dynamic array, create multi-dimensional array, and
find the array size.
Compute the probabilty from a standard
normal distribution.
Excel VBA Basic Tutorial Series
Excel VBA Basic Tutorial Series 1
Creating Your First Macro
In this sub section, we will
show you how to create your first macro (VBA program). We will use the world classic "Hello World!" example.
Recording a Marco
In
this example, we will record a macro that sets the cell background color to light yellow.
Modules and Procedures and Their
Scope
A module is a container for procedures as shown in our prior examples. A procedure is a unit of code enclosed either between
the Sub and End Sub statement or between the Function and End Function statements.
If you pass an argument by reference when calling a procedure, the procedure access to the actual
variable in memory. As a result, the variable's value can be changed by the procedure.
Objects are the fundamental building blocks of Visual Basic. Anobject is a special type of variable that contains both data and codes. A collection is a group of objects of the same class.
The most used Excel objects in VBA programming are Workbook, Worksheet, Sheet, and Range.
A workbook is
the same as an Excel file. The Workbook collection contains all the workbooks that are currently opened. Inside of a workbook
contains at least one worksheet.
Range represents a cell, a row, a column, a selection of cells containing
one or more contiguous blocks of cells, or a 3-D range. We will show you some examples on how Range object can be used.
Each object contains its own methods and properties. A Property represents a built-in or user-defined characteristic
of the object. A method is an action that you perform with an object.
Objects are the fundamental building blocks of Visual Basic.
An object is a special type of variable that contains both data and codes. A collection is a group of objects of the same class.
The most used Excel objects in VBA programming are Workbook, Worksheet, Sheet, and Range.
A workbook is
the same as an Excel file. The Workbook collection contains all the workbooks that are currently opened. Inside of a workbook
contains at least one worksheet.
Range represents a cell, a row, a column, a selection of cells containing
one or more contiguous blocks of cells, or a 3-D range. We will show you some examples on how Range object can be used.
Each object contains its own methods and properties. A Property represents a built-in or user-defined characteristic
of the object. A method is an action that you perform with an object.
Sometime
a method takes more than one argument. For example, the Open method for the Workbook object, takes 12 arguments.
Excel VBA Simulation Basic Tutorial 101
This is an essential skill for creating a simulation.
Topics in this section cover declare an array, resize an array, manage dynamic array, create multi-dimensional array, and find the
array size.
A tutorial on how to create a histogram by passing array into the histogram sub procedure
and returns the frequency distribution from the array.
Excel VBA Statistics and Math
