Worksheet PowerView

Worksheet PowerView

Worksheet PowerView

The Worksheet PowerView provides you spreadsheet functions right inside your appraisal report. This allows you to set up your own custom calculations inside forms. Therefore, if you have a different way of doing the math or wish to add math in a form, you can write worksheet formulas to do so here. The formulas could be actual arithmetic or they could be functions that copy the data from field to another.

Our Worksheet PowerView even reads spreadsheets saved from Microsoft Excel. Therefore, if you are familiar with spreadsheets and already have some of your own outside of WinTOTAL, then you'll be up and running in no time with this.

Worksheet Basics

What is a worksheet / spreadsheet A worksheet is a tool that enables you to set up formulas and models for calculations. A calculator typcially only lets you do one calculation at a time, but a worksheet enables you to change an entire page of calculations by adjusting just one factor.

? Individual elements are called "cells".

? Cells are referenced by row number and column letter.

? Cells can contain text, numbers or formulas.

? Functions can take the contents of one or more cells and perform math calculations or other functions on them.

? For one cell to be a function of other cells, the first character needs to be "=" (example: =A1+B1)

? Just like a calculator, operands may be grouped using parenthesis. (example: A1*(B1+C1))

? Changing the information in the source cell automatically updates cells containing formulas based on that cell.

Common math functions When writing functions in your worksheet, use "=" as the first character in the formula bar. Instead of typing the cell references, simply click in the

The Worksheet PowerView incorporates "live" spreadsheets into your appraisal reports letting you set up models for the calculations in a report. They're compatible with MS Excel too!

Worksheet PowerView ? Worksheet Basics

Page 10.1

Worksheet PowerView

cells. When you resume typing your operands, the worksheet will know to put you back in the formula bar.

Add:

=A1+A2

Sum:

=SUM(A1,A2,A3,B1,B2,B3) or =SUM(A1:3,B1:3)

Multiply: =A1*A2

Divide: =A1/A2

Rounding: =ROUND(9899.435, -2) returns 9900

Condition: =IF(A1>10, "Greater", "Less") returns "Greater "if the value of A1 is greater than 10 and "Less" if A1 is less than 10

Handy text functions To return only rightmost 5 characters: =RIGHT(A1,5)

To concentrate cells and text: =A1&A2

Linking the fields to your forms

To link a field on the form and field on the worksheet, first click the field on the form and then click in the cell on the spreadsheet.

? Click the yellow "uplink" button if you want to push the contents of the worksheet's cell into the form.

? Click the red "downlink" button if you want to push the contents of the form into the worksheet.

Use the Unlink button to remove the connection between two fields.

Formatting cells

To make your worksheets more readable as well as specify data types, use formatting. Just select the cells you need to customize and click the format cells button.

The Sample Worksheet

Using a variety of functions WinTOTAL already has a sample worksheet built into it. It shows a variety of functions and formatting. Here's how to use it:

1. Open a URAR report - preferably one that you don't mind using as a test file. Make sure the sales comparison section is filled out.

Formatting assists with presentation as well as defining data types.

Worksheet PowerView ? The Sample Worksheet

Page 10.2

Worksheet PowerView

1. Click on the Worksheet PowerView. You'll notice your screen splits.

2. Click Open in the WorkSheet PowerView to get to our sample.

3. Double click the WRKSHEET subfolder and then double click SAMPLE.XLS.

4. You may be receive a message regarding existing worksheets being cleared. Click OK. You'll see our sample load.

5. The equations in this worksheet are tied to fields in the sales comparison section. So, simply scroll to the comps grid.

6. The yellow fields on the worksheet indicate information that is being pushed back up into the form. Click on one of the yellow worksheet fields and notice the active field on the appraisal form switches to the field linked to that cell on the spreadsheet.

7. The red fields on the worksheet are places where data is being read from the form into the worksheet. Click on a red field and the cursor on the appraisal form will jump to its source.

8. The blue fields on our sample worksheet are colored blue simply to point out that is where you can change parameters for the calculations. We did this with the formatting options.

9. If the sale date, sale price, and room counts aren't filled for your subject, enter data in those fields now.

10. As you enter data in those fields, notice how it triggers the calculations in the Worksheet PowerView. The sample has equations to trigger automatic adjustments based on sale date and differences in the room count.

11. Click on the worksheet and enter a different yearly appreciation rate and see how the adjustments in the comps grid changes in both the worksheet and the form.

The worksheet in the sample is just that, a sample. You can add your own calculations to a worksheet and link it with any field of any form.

? The top of the worksheet calculates the days between the subject and comps sale and estimates appreciation based on an annual rate.

? Similarly, you can do automatic room adjustments by assigning values based on differences in the number of bedrooms and baths.

The sample worksheet shows an interesting formula for calculating a time adjustment. It demonstrates how to use cell formatting to in conjunction with arithmetic to find the number of days between the subject and comp sale.

Worksheet PowerView ? The Sample Worksheet

Page 10.3

Worksheet PowerView

Creating Your Own Worksheets

Become familiar with spreadsheet functions In this context, we can't teach you how to use a spreadsheet and our technicians realistically can't go over fine details of spreadsheet functions on the phone. Basically, you should use our worksheet as you would MS Excel or Lotus. There are dozens of books and videos that can teach you spreadsheet functions. We highly recommend them.

Example: Add two cells together: Start with numbers in cells A1 and B1. 1. Click in the cell that is to contain the result - say C1. 2. In the formula bar, type an = sign. 3. Click in the cell that contains the first item you wish to include in the sum. You'll see its cell reference appear in the formula bar. 4. Click in the formula bar and type a + sign after the cell reference. 5. Click in the cell that contains the second item to add. You should now see =A1+B1 in the formula bar. 6. Press ENTER.

Example: Transfer sales comparison value to final reconciliation Here is a way to make the indicated value by the sales approach transfer into your final reconciliation. 1. Open a report to the URAR and click on the Worksheet PowerTab. The screen will split to shoe the form on top and the worksheet on the bottom. 2. In the Sales Comparison Analysis, on page 2 of the URAR, click inside the Indicated value by Sales Comparison Approach field. 3. Now click in the cell A1 and click the "Link down" button. Cell A-1 will turn red and have data that field's data copied to it.. 4. Click in cell B1. While cell B1 is selected click inside the formula bar. 5. Inside the formula bar press the equal [=] key. 6. Now click icell A1, =A1 will appear in the formula bar, and press ENTER. 7. Now click back in cell B-1.

Worksheet PowerView ? Creating Your Own Worksheets

Type formuals into the formula bar. This particular formula equals 3/(4+5) or as you can see in cell D1, .3333333. Shows which cell the formula is used in.

Page 10.4

Worksheet PowerView

8. On the URAR, still on the second page, within the Reconciliation section, click in the Final Value field.

9. Last, on the Worksheet PowerTab click the Link up button. Cell B1 will turn yellow and have the same data as A1.

The fields are now linked. Any data entered in the Sales Approach, from Sales Comparison Analysis,will transfer automatically to the Final Value in the Reconciliation section.

Create spreadsheets in Excel for WinTOTAL The worksheets used in WinTOTAL are compatible with MS Excel 2000. It may be easier for you to create your base formulas in Excel since it is an entire application dedicated to creating them.

1. Once you've saved a file in Excel, close it.

2. Go back to WinTOTAL's Worksheet PowerView and click Open.

3. Browse to the file you saved and double-click it.

Worksheets can also be copied and pasted from MS Excel.

Saving / loading worksheets If you've created complex worksheets you don't want to type again, you can click the Save button to keep an external copy of them. WinTOTAL also remembers which fields were linked to the form and saves those in the same directory with a DAT extension.

To load a previously saved worksheet, simply click the Load button and select the file. If a DAT file exists in the same directory, then WinTOTAL will look in it to see if it contains references to field links.

More Chapters

Click here to go to the online Table of Contents where you can access additional chapters in the WinTOTAL user guide. Or, view the other parts of the manual by selecting Contents from WinTOTAL's Help menu. You will find more tutorials on all of WinTOTAL's PowerViews. The more you know about the software, the more efficient and competitive you can be.

Load presaved worksheets to automatically trigger calculations in forms.

Worksheet PowerView ? Creating Your Own Worksheets

Page 10.5

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download