Docstoc

Slide 1 - Financial Modeling with Excel

Document Sample
Slide 1 - Financial Modeling with Excel Powered By Docstoc
					the VIRTUAL Winery

gwrdc.com.au

the VIRTUAL Winery








The VIRTUAL Winery is a business simulation used to create a long range strategic plan. The system provides for both US and metric measurement. The plan encompasses sales, cost and production forecasts with a capital budget and a flexible debt/equity structure. Generates sales and margin forecasts (by product and product line), balance sheets, operating and cash flow statements for 10 years.

the VIRTUAL Winery
Accounting Flow
This chart reflects the accounting schema used to build the Virtual Winery V3 System. This flow chart, a multilevel chart of accounts and sample financial reports for a winery operation are included with your

VIRTUAL Winery
download.

the VIRTUAL Winery








The VWS V3 is written in Visual Basic and uses Microsoft Excel to perform calculations, maintain the production and sales databases, and generate graphs, analysis and reports. The forecast footnotes are in Microsoft Word and are linked to the Excel based financial forecast. The program can maintain and recall any number of scenarios. Scenarios can be easily emailed to facilitate support and collaboration. The program’s design includes two parts:
 

Production and Sales Forecast Component Financial Forecast Component

the VIRTUAL Winery
Production Component Flowchart

the VIRTUAL Winery
Product Entry
The first step is to enter your wine products. Use this menu choice to go to the Product Entry Workbook. You can delete the sample product sheets, then Add your own product sheets. Each product sheet will post up to 15 years of data (4 Inventory, 1 Crop and 10 Forecast years) to the product database.

the VIRTUAL Winery
Workbook Data Flow

the VIRTUAL Winery
Product Entry Workbook

Above is the Splash Screen for the Product Entry Workbook. The next three slides show a typical product entry sheet.

the VIRTUAL Winery
Post a Product to the Scenario File

the VIRTUAL Winery
Recall the “Posted” Scenario to the Database

the VIRTUAL Winery
Product Database Sheet Posted from Product Entry Workbook

the VIRTUAL Winery
Product Rollup
Whenever changes are made to the database, you need to perform a “Roll-up”. The product rollup will insert each product line of the database into the product template, calculate and then add the results to the Production and Sales forecast.

You can also choose to rollup only a group of products so you can analyze the results from a single wine program.

the VIRTUAL Winery
Production Forecast
You can go to most of the analysis and reports from the Preview/Print Reports menu selection. From this menu you can also access the database sheets and make changes to “fine tune” the forecast. The next two slides show the Production and Sales Forecast analysis built from processing the Product Rollup

the VIRTUAL Winery
In addition to the Sales and Forecast Analysis, here are some samples of other reports and graphs generated by executing a Product-Roll-up.


Sales and margins are chartered for 10 years



Monthly production is chartered for the first 60 months.
Verification report - This analysis will give you a quick overview of each product so you can spot modeling errors and anomalies.



the VIRTUAL Winery
10 Year Sales Analysis

The sales analysis is a 10 year annual report of sales, margins and case sales by product, sub product and sales channel. It also includes graphs of annual sales, margins and case sales by sales channel. These analysis are Excel Pivot Tables generated from the sales database. The sales database is re-built each time that there is a product “roll-up”. The next two slides include samples of these reports and graphs.

the VIRTUAL Winery
60 Month Production & Sales Graphs

The graphs menu choice will display the production and sales graphs for the first 60 months. These graphs are very useful for spotting modeling errors and data anomalies. The next two slides show some samples of these graphs.

the VIRTUAL Winery
Do I have the necessary working capital to support these inventory levels?

That is a lot of barrels – Where am I going to store them all?

the VIRTUAL Winery
Will my cellar operations support 78,000 gallons in process?

Oh! Now I understand – Those peeks are the wine club shipments.

the VIRTUAL Winery
Verification (Summary) Report
To go to the Verification report, click on the Product Summary sheet tab. This sheet also includes a drill-down analysis (the “Audit Cube”) so you can inspect each product by month by individual line item (e.g. Opening Inventory).

the VIRTUAL Winery
Documents
The Documents menu selection provides access to the various MSWord documents and Excel workbooks that support and document the program including: • Report cover, Accountant’s Report and Forecast Footnotes. • Chart of accounts including an accounting system schema (see Slide 2).

• Systems flowcharts (See Slides 5, 7 and 28)
• Program documentation – a 55 page MSWord document. • Menu report listing all the menu selections with explanation.

the VIRTUAL Winery
Update the System

the VIRTUAL Winery
Systems Setup

The System selection provides access to systems setup and maintenance functions. Notice, the measurement can be either metric or U.S. method.

the VIRTUAL Winery
About Screen

the VIRTUAL Winery
Financial Component Flowchart

the VIRTUAL Winery
Transfer to Financial

When you are ready to transfer the Production and Sales Forecast to the financial component of the program, you must select which years that you want to show monthly and which years where you want to show only the annual totals.

the VIRTUAL Winery
Go to Financial
When you are ready to tackle the Financial part of the forecast, select Go To Financial from the menu. The next slide shows the opening Index sheet of the financial component workbook. You can navigate the entire Financial component from this sheet.

the VIRTUAL Winery
Forecasted Balance Sheet

the VIRTUAL Winery
Forecasted Operating Statement

the VIRTUAL Winery
Forecasted Cash Flow Statement

the VIRTUAL Winery
Additional Data Required for the Financial Model
 







Labor and Benefits Capital Budget Credit Line, Term Loans (principal Interest), Receivables and Payables information Production, Sales & Admin. and Production Cost Budgets. The automated budgeting system makes this easy! Opening Balances – Balance Sheet required; the cash flow and operating comparative balances are useful but optional. Additional Equity contributions, as required (UGH!)

The following four slides show the Labor & Benefits entry and the Automated Budgeting system.

the VIRTUAL Winery
Entering your Labor Costs

the VIRTUAL Winery
Automated Budgeting System - Budget Type & Parameters

the VIRTUAL Winery
Automated Budgeting System – Period Allocations
Expenses can be allocated to monthly periods using any one of 20 Period Allocation Base Options, or you can enter your own monthly percentages.

the VIRTUAL Winery
Automated Budgeting System – Budget Data Report


				
DOCUMENT INFO