Forecasting Techniques
Use Excel's built-in forecasting features to create a sales forecast. The Average formula and moving averages can be used to estimate seasonal indices. Adjust the actual data with seasonal indices and plot the resulting data. Use Excel's trendline feature to add a best fit line to the chart. This workbook contains an example of a linear and exponential sales trend. You can compare trendline R^2 values to find the best fit. R^2 is percent of variation explained by trendline -- Higher R^2 values are best -- 1=perfect fit. After finding the best fit, use the trendline formula and seasonal indices to forecast future periods.Comparative Ratio Analysis
You can use Excel formulas to compare actual data to Robert Morris Associates annual statement studies. RMA comparative ratios are available in the reference section of most business libraries. See the sheet named RMA for more information. Lending institutions use the RMA ratios as one measure of a business that is applying for a loan. By becoming familiar with the RMA ratiios, you can improve the financial measures of your business - before it is time to apply for a loan.Return on Investment and Return on Equity Business Model
Workbook model of key inputs to the ROI and ROE measures including profit margin, asset turnover and equity multiplier. Du Pont style analysis of performance using goal seek and multiple scenarios.Pivot Table Multiple Consolidation Example
Use the multiple consolidation range feature of pivot tables to summarize accounting data arranged in columns by month. The pivot table data can be summarized by quarter using the built-in pivot table grouping feature. Each page of data included in the pivot table can be assigned a unique identifier in a new page field that is created when you define the pivot table.Data Input and Analysis for Business Information
You can link charts, PivotTables and Ranges of data to produce a basic information system. With a command button, you can use the built-in Data Form to add data to the range. If you name the range "Database", you can link the PivotTable to the named range. When you add new data, the range will expand and the new data will be included in the PivotTable. You can link a chart to the data range of the PivotTable and the chart will automatically change to show new values in the PivotTable.PivotTable to Calculate Budget Variances
When you create a Pivot Table field to show Budget Variance with the "% of" calclulation option, the PivotTable will show Actual as a percent of Budget and Budget as a percent of Budget. You can create a Visual Basic for Applications procedure that will hide the Budget as a percent of Budget columns.PivotTables With Accounting Data
The powerful grouping capability of pivot tables can be used to bring flexibility to the manipulation of financial totals by month, quarter, etc. If the source data is arrayed as columns of monthly accounting data, you can use the PivotTable Multiple Consolidation Range option to create a date field you can group by month, quarter or year.IRR, XIRR, NPV, XNPV Functions
You can use IRR and NPV to calculate internal rate of return and net present value for even cash flows. For uneven cash flows, use the XIRR and XNPV functions found in the Analysis Pak Add-In.Maximum Sustainable Growth
You can use Excel's iteration feature to solve a circular reference. In a maximum sustainable growth model Excel can iterate to solve the circular formulas where maximum sales growth depends on profit generated and profit depends on sales. If you set the iteration parameters when the workbook opens with a Visual Basic Macro, Excel will iterate to find the maxium sustainable growth. You can use the Tools, Auditing features to trace the circular references.GANTT Chart for Scheduling Resources
You can create a Visual Basic for Applications procedure that uses a list of start and stop times on a worksheet to build a table of durations for a bar chart. The bar chart can display the date in a resource usage GANTT format.Highlight Exception Values
You can use the OnEntry property to run an exception check. The procedure can change the font color of the cell and can perform an operation on the ActiveCell if you want to validate or change color cell where data is entered. There is a "hole" in the logic of OnEntry: when the user copies a cell and pastes the value on the worksheet the OnEntry property does not run.Number of Days, Months and Years Between Two Dates
You can create a Visual Basic for Applications User Defined Function to return the number of years, months and days from a start date to an end date. Use the Function in formulas or to create a text string: "As of yy/yy/yy, nn Years, mm Months and pp Days have elapsed from the start date xx/xx/xx".Using Built-In Workday Calculation Functions
You can use Excel's built in Workdays and Networkdays functions to perform calculations with dates taking into account weekends and holidays. Workday returns a date some number of workdays in the future. Networkdays calculates the number of workdays between two dates.
![[HRule Image]](../images/div.gif){kind=small}