1. technology and computing
2. software
3. databases

News and Information for Users of Sage 300 Construction and Real Estate & Sage Estimating
July 2013
Report Design Best Practices:
How to Build a Useful Report
by: Bruce Vanderzyde
Anterra Technology
Report Design Best Practices:
How to Build a Useful Report...............1
Sage Estimating Database Reports...............3
Equipment Cost Setup for Sage 300 CRE –
Part 1 – Getting Started.......................4
TUG Committee Leadership
and Involvement...................................6
MyAssistant – Emailing Direct
Deposit Notices....................................7
From the TUG Talk Bulletin Board:
Users to Users......................................8
Financial Statement Designer: Use Columns
to Insert Cap Rate Values &
Mortgage Info!......................................9
The Data Black Market...What is Your
Data Really Worth?.............................12
TUG Conference – Changing
User Perspectives..............................13
WebEx Training and Open Forums..............13
Forecast for Great Financial Statements
with a Chance of Awesome!..............14
Estimating Tips and Tricks..........................15
T
here are lots of complaints in
construction and real estate companies
about information overload. The real
cause of this complaint is a lack of useful
reports. At Anterra, we’ve built our company
around business intelligence for construction
and real estate and spend all of our time
designing and building reports. The purpose
of this article is to share our knowledge on
building reports that people like. The key is to
make reports useful to the person reading it.
Do you or your colleagues:
• Have to run multiple reports to answer
a question or monitor a part of
your business?
• Use a highlighter or red pen on reports?
• Use Excel to build useful reports?
• Run multiple reports looking for the one
whose format you liked?
• Receive a reporting package that has
content that you consistently skip over?
Some of your challenges can be solved by
focusing on the design of your reports.
Report Design Concepts
There are 3 main areas of report design –
content, layout and style.
Report Content
You might have heard “Content is King” for
web page design; it’s the same for reports.
Before you start building a report (or
engaging a consultant to build it for you)
consider the main purpose of the report
and who will use it. Here are the steps to
determine report content:
1) List the questions you are trying to
answer with this report.
2) Identify each item of information you
need to answer each question.
3) Identify the source of each item of
information.
You should see that some of the information is
the same for multiple questions – if you have
dozens of pieces of information you likely are
trying to answer too many questions on one
report. This is how “kitchen sink” reports
get created. Report designers try to answer
many questions with an all-purpose report
but end up with a dump of data. Kitchen
sink reports tend to be printed in landscape
format on legal size paper. With 20 columns
and 30 rows of data, a user has to look at
600 data points per page – no wonder they
feel overloaded!
o Continues
July 2013
News and Information for Users of Sage 300 Construction and Real Estate & Sage Estimating
Keep your reports focused by answering
more specific questions and having fewer
columns of information. For example, a
report that shows how much money you’ve
paid your subcontractors over the last year
should be separate from an outstanding
commitment report.
Report Layout
After you’ve identified your content you
should lay out your report title, columns and
rows in Excel. Like your construction jobs,
it’s always easier to organize information and
then build a report than work on design and
construction at the same time. A good report
layout has the following:
a)
b)
c)
d)
e)
f)
A logical flow of information.
A clear title so readers can immediately
interpret report content.
Obvious answers – i.e. the most
important number on the far right so
scanning the report is easy.
Sortable columns if possible (sometimes
done as report parameters – i.e. vendor
number or vendor name).
Good report parameters to filter the
content to information relevant to a
particular user or audience (i.e. filter by
division or project manager).
Good header and footer information so
readers know when the report was
run, the current page number and the
total number of pages.
Review your report design with some of
the users who will be running it. In most
cases, they will have good suggestions for
improvement. Collecting the suggestions
before you start building your report will save
you from the need to rework and retest it later.
Report Style
Just like you know a beautiful web page when
you see one, you’ll make your reports more
useful and easier to read with good styling.
Report style has the following components:
a)
Fonts – title, header and footer fonts
should be modern, easy to read and
properly sized for their prominence on
the page.
b)
c)
d)
e)
f)
Bolding – consistently bolding titles and
totals makes it easy to separate
information within the report.
Page breaks – keep data content related to
one major item on each page when possible.
Clear totals and subtotals (removing
subtotals where only one item is present
to lessen clutter and report length).
Color – include indicator lights or
conditional formatting to indicate which
items are most off plan. This has
immediate impact by drawing the reader
to look at what is most important on
the report.
Use white space well – space columns
and rows so they are easy to read.
Lay out your report design in Excel as a mock
up before you start writing it. Test your design
by asking your questions and see if your report
provides complete answers. Sometimes it is
best to use sub reports to drill down to further
detail to keep the top level report clean.
Sample Report with Drill Down
and Column Sorting
the TUG Pulse
Published by TUG, The Users
Group for Sage 300 Construction
and Real Estate & Sage Estimating,
and sent to all members.
Board of Directors
Scott Bishop, President
Marlene Williams, Vice President
Jon Banse, Secretary
Sharon Hessong, Treasurer
Tim Cooke, Past President
Donald Bannister
Alan Cusson
Mary Jo Hamik
Barbara Morse
Val Steffen
Michael Suhovecky
Lenni M. Witt
Publications Committee
Natalie Allen
Jon Banse
Sharon Hessong
Tom Love
Liz Perez-Lavin
Val Steffen
Eire Stewart
Barbara Morse
This simple report has sortable columns
and can drill through to more detail for key
fields. It was written for Sage 300 data using
Microsoft SQL Server Reporting Services.
Parameters can be used to filter the report by
division, date or project manager.
TUG
3525 Piedmont Road
Building Five, Suite 300
Atlanta, GA 30305
email: [email protected]
Phone: 404.760.8171
Conclusion
Taking the time to plan out your report
designs can greatly increase the usefulness
of your company’s reports.
Further Information
Further information on reporting and Business
Intelligence is available at www.anterratech.
com or contact Sharon Dodds at (832) 3421579 or [email protected].
u2u
Fax: 404.240.0998
Toll Free: 866.846.0999
email
[email protected]
Website
www.TUGweb.com
News and Information for Users of Sage 300 Construction and Real Estate & Sage Estimating
July 2013
Sage Estimating Database Reports
by: Scott R. Bishop, CEP
HDR Constructors, Inc.
D
uring the TUG National Users Conference,
I was asked if there was a way to see the
different Formula Tables in the database.
The answer to that question is yes, which then
begs the question, what other information
can you report on from your database? The
answer is you can report on Phases, Addons,
Material Classes, Subcategories, Formulas,
Items, Assemblies, Crews, WBS Codes, and
Models. All of these reports can be printed
and/or exported to various supported formats.
To access these reports, click on the Database
pull-down menu in Sage Estimating. At the
bottom of that pull-down you will see Reports
with a side arrow and within that are the
different database reports you can run. For any
database report you can print/export the entire
list or choose a selected portion of the list by
using the Range Selection option in the Report
Options window.
unit description, miscellaneous description
and job cost phase.
•
Addon reports will show you in list form
the addons you have in your database.
The report will show the addon number,
description, cost basis, criteria selection,
taxable or not, range information, and
the rate.
• Material Class and Subcategory reports
provide you a list and description.
• The Formula report shows the name, unit,
and the formula with all variables.
• Variable reports include variable name,
default value, minimum and maximum
values, and any help notes.
• Formula tables provide list of tables with
return values.
• For database items you can run the
following reports:
o Pricebook will print a complete list of all
database items or a selected range
showing the item description, and the
labor, material, equipment,
subcontractor, and other category
unit costs.
o Item Detail Report will return a list
of all database items or a selected
range providing the item number,
description, crew, productivities, unit
costs, and job cost information.
o Price Code Report will print a list of
database items by price code and show
price code, phase number, item
number, and item description
o Price Link Report will print a list of
database items that are linked by price.
• The Phases report provides a list of all
of the phases included in your database
in the order in which they were built. You
can see the phase number, description,
• For Database Assemblies you can run the following reports:
o Assembly Detail Report will list all or a
selection of assemblies with all of
u3u
the items and item tables included in
the assembly along with the
calculations used to develop quantities.
o Assembly Summary Report will list all
or a selected section of assemblies
without any of the detailed items or
item tables.
o Item Table Report will list all or a
selected section of item tables. This
report shows the variables used in one
or two dimensional item tables, the
items that are used in the item table,
and the calculations.
• Database Crews have the following reports
available:
o Crew Detail Report lists the crew name,
description, all resources included with
descriptions and the quantity of the
resource included in the crew.
o Crew Summary Report lists the crew
names and description, but does not
show any resources.
o Resources Report lists all of the
database resources by Resource Name
and will also show the resource
description, type of resource and
alternate ID.
o Rate Tables Report will list all or a
selected rate table in detail and will
show the resources, raw rates, total
rates and any benefits included.
o WBS Report includes a list of all or
a selection of WBS Codes and values
with description and Unit of Measure.
o Models Report lists all or a selection of Database Models with various
detailed information. You can print just
a list of models, or you can show each
line in the models with the questions and question formulas.
It is recommended that whenever you make
significant changes to the database, or at set
points throughout the year, you should backup
your database and print a complete set of
database reports.
July 2013
News and Information for Users of Sage 300 Construction and Real Estate & Sage Estimating
Equipment Cost Setup for Sage 300 CRE – Part 1
Getting Started
by: Gemma Fulton
Frederick Derr & Company, Inc.
How to Get Started –
Equipment Cost Setup
Application
5. Verify the Current Period and Next Period dates.
6. Select the appropriate Print Selection information.
7. Click [Start] to begin Close Period.
Equipment Cost tracks various types
of cost and revenue information for
equipment. The system is flexible,
allowing you to accumulate detailed
cost information for pieces of
equipment and then allocate the cost of operating the equipment to
jobs, billings and/or general ledger. These charges are accumulated
as revenue to the equipment, giving you the ability to analyze
utilization of equipment.
And the “Historical Totals” options change based on your selection. So if you select Period Size Daily, the system will allow you to
accumulate totals by Day, Month and Year.
From the File menu, select Company Settings > EQ Settings.
Determine prefix and calendar preferences based on GL Settings.
And it changes to allow you to accumulate historical
totals by Week, Month and Year if you choose Weekly.
Selecting a period size allows you to determine the level of
detail to accumulate and produce reports based on the period.
There is a “closing” process required for the period selected
under the Period Size (Day) (Week) (Bi-week) or (Semimonth) setting.
1. Run Post Entries.
2. Print all period-end reports.
3. Back up your files.
4. From the Tasks menu, select Close Period.
o Continues
u4u
News and Information for Users of Sage 300 Construction and Real Estate & Sage Estimating
July 2013
Account tables affect how accounts pre-fill in Equipment Cost. Each
entry type (debit or credit) can have its own account retrieval. For
example, you can track revenue separately for each type of equipment
by setting up a revenue credit account table listing General Ledger
accounts by equipment.
If you choose “Not used” for period size, your historical totals will
accumulate by Month and Year only.
Posting Settings
Determine if you want
to show cost, revenue
and misc codes for each
Equipment ID.
If you are using account tables, the system looks to the account tables
before using the default account set up in EQ Settings.
Journal Options
Entry Settings
You can use default journals or design your own for each type of
journal.
You
can
determine
flexibility in rates used and
accounting date assigned
to the transactions in the data entry screens.
Now you are ready to set up your Equipment Types, Equipment
ID’s, Cost, Revenue, Miscellaneous Codes, and your Rate Tables
if you choose.
GL Entry Settings
GL Entry Settings are only accessible if the General Ledger interface
is activated in Post & Interface Settings. Enter the account to use if,
during data entry, no other account can be retrieved using the account
retrieval hierarchy. If you are not retrieving prefixes from equipment,
enter a full account.
The equipment costing application is a great system for tracking cost
of equipment and allocating those costs to jobs, billings and general
ledger. The time spent setting up all the defaults results in a real time
savings during data entry tasks.
Account tables enable you to define which General Ledger account
numbers pre-fill the debit and credit account columns in entry grids.
To use account tables, you must mark the Use account table box in
EQ Settings, GL Entry Settings, for each account using account tables.
u5u
Next Month:
Part 2 – Setup Items
July 2013
News and Information for Users of Sage 300 Construction and Real Estate & Sage Estimating
TUG Committee Leadership and Involvement
by: Scott R. Bishop, CEP
President, TUG
I
f you are looking to increase your involvement with TUG, or you are curious about becoming involved with TUG, now is the time! We are currently
looking for volunteers to serve as committee chairs and/or committee members for TUG’s 2013-2014 fiscal year (term runs until June 30, 2014).
Committees meet online using WebEx, typically once a month for about an hour, and discuss potential training webinars, TUG Pulse articles,
and conference sessions.
Take advantage of this opportunity to learn and share your Sage 300 Construction and
Real Estate (CRE) and Sage Estimating experiences with other users by participating in
a committee. Committee involvement also offers specialized training and networking
on specific modules, such as Project Management, Estimating, and Construction
Accounting. Members are eligible to earn TUG Bucs for their involvement, which can be
redeemed for a variety of gift cards.
We currently have the following committees: Construction Accounting; Education; Estimating; Local Chapters; Project Management; Publications;
Real Estate; Service Management; and Membership. For detailed committee descriptions and a list of the current chairs and members, please see
the “Who’s Who @ TUG” link on www.TUGweb.com.
If you are interested in joining or leading a committee, please contact TUG staff at [email protected].
The current committee list includes:
Committee
Chair
Board Representative
Construction Accounting
Liz Perez-Lavin
The Users Group for
Val Steffen
Education
Tom Moore
Sharon Hessong
Estimating
Carl “Ozzie” Mest
Tim Cooke
Local Chapters
Marlene Williams
Jon Banse
Project Management
Mary Jo Hamik
Don Bannister
Publications
Eire Stewart
Barbara Morse
Real Estate
Lenni Witt
Alan Cusson
Service Management
Marlene Williams
Sharon Hessong
Membership
Jon Banse
Tim Cooke
u6u
Sage 300 Construction and
Real Estate & Sage Estimating
News and Information for Users of Sage 300 Construction and Real Estate & Sage Estimating
July 2013
MyAssistant – Emailing Direct Deposit Notices
by: Val Steffen
Dean Snyder Construction
O
ne of the primary reasons we purchased the MyAssistant software
is to send direct deposit and general human resource notices
to employees via email. It was made easy when the email cell
was added to the employee setup with the 12.1 upgrade because
MyAssistant was also updated to add an employee email directory
which pulls from the setup.
•
MyAssistant comes with a few hundred reports already available for
you to set up and use. You may select any one of the reports in any
application category and activate, open and revise, run (just runs
the task), run and send (runs the task and emails the notifications if
applicable), copy the task and revise the parameters of the setup or
write a new task from scratch.
Once you have completed the initial setup and click Finish, you may
click on the task and select Open to revise any of the settings you have
just completed. You will have the condition setting available for revision
now as well. You may edit the overview of the condition in the condition
summary box. To revise the condition, click on modify. You can use the
application records to assist with constructing the conditions.
The task for running the direct deposit notices didn’t match our
company’s pay periods so I chose to copy the task and revise it to suit
our needs. To do this, highlight the task name and click on copy. This
will open a copy task box that allows you to rename
the task and select create a copy of the condition and/
or activate the task. Next, you will get general setup
options for the copied task. You must save the task and
open it again to edit the conditions.
can be arranged and what additional information can be
accessed through reports/inquiries.
Notification Options lets you select options for sending
notifications to recipients. For instance, for this task you might
select Send notifications in: A separate email for this task and
Notify each time the task is run (as opposed to Notify once).
When I first set up the direct deposit task by using the
default task and adjusting the date to accommodate
our weekly payroll, the report took hours to run and
slowed down our entire system. The task was looking
through every employee and every check in current
to find any that met the condition requirements (I
had changed it from New to Current also because
we usually have payroll posted before the direct
deposit upload is completed by the bank). I revised
the condition order so the employee email address
requirement came first so only employees with an email address will
run. I also had to correct this condition to look at employees that have
an email address not equal to ‘ ‘(blank). I further revised it to look at
anything with a period end date up to 8 days from the current date.
• The General Information tab lets you describe and
categorize the task (sort it in the list of MA tasks
–PR/Processing, for instance).
• Scheduling allows you to set the run schedule for
the task. It can be set to run at specific dates/
times, on demand, or upon completion of a task
in Sage.
• Email allows you to set up the email rules,
directory, and any reports that attach to the email
or to the run notice that shows in MyAssistant.
• Additional Information allows you to specify how
notifications
o Continues
u7u
July 2013
News and Information for Users of Sage 300 Construction and Real Estate & Sage Estimating
Save the condition and click on the X to close the screen, then click
OK to close the condition screen.The email setup window is already
set to use the employee email directory. I left that as is and only enter
employee email addresses in the specified cell if they have elected
to receive their notice and all HR notifications electronically. I set up
a cell in the Custom Fields tab to accommodate email addresses for
those who receive checks or hard copy notifications.
You may click on the checkbox in the lower left corner to include the
information in the email. If you leave this box unchecked, the data will
appear in the notification when the task is run.
You may run a task at any time by highlighting the task and selecting
Run or Run/Send from the MyAssistant menu. Otherwise, the task will
run as scheduled.
To print the report, you will
need Adobe Acrobat Reader/
Standard version 10 or higher
(versions 8-9 may work with
some adjustment). You can
download or update the reader for free on-line from the Adobe website.
If the file was password protected, you will need your password to open
the file for viewing or printing.
Make sure to let your employees know they may need to check
their junk mail and add the email address to their safe sender list.
Because of the mass mailing appearance of the email, it tends to go
to the junk mail folder in Gmail, Hotmail, Yahoo, etc.
To add password protection to the direct deposit attachment, right click
on the attachment name and select Edit Parameters. A Report Options
box will open. Click in the Protect PDF with password box and select the
field to use for the password. You may also elect to embed the report in
the body of the email. Click OK.
Also in this window, you may select fields to appear in a report/
notification as seen in the above example, across the bottom of the box.
There is a MyAssistant email that works really well in making sure
that everyone is receiving their emails and was useful in finding
some of our setup errors when we first started with MyAssistant.
In summary, MyAssistant is more complicated than I expected but
I love it for the few things I have had time to figure out how to use
so far!
From the TUG Talk Bulletin Board: Users to Users
INFORMATION TECHNOLOGY:
Data Folders/Servers
Maryann Pilgreen asked if anyone knows if there is an adverse
impact on performance if the data folders are on a Network Attached
Storage unit instead of the server that has the program files.
Brian Fulmer replied with the following: “Quick answer: REALLY
bad idea. Long answer: Network Attached Storage (NAS) units are a
cheap and cheerful way to add storage capacity to a network. They
are to supplement/replace storage from a “real” server. This storage
isn’t equivalent to adding more hard disks to a server. A Storage
Area Network (SAN) is equivalent to adding more hard disks to a
server. This is the opposite of cheap and cheerful, however. If your
Pervasive database server accesses data files on a NAS device, you
are dramatically increasing the latency of each transaction, while
dramatically increasing the amount of data being pumped over the
network. Every transaction would be something like: Client requests
dataset from the STO server. The STO server does a file read over
the network to the non-local data folder. The NAS thinks about it
and sends that data over the network. The STO server thinks about
it and sends the dataset back to the client. The client thinks about it
and sends the dataset back to the STO server which thinks about it
and writes it back to the NAS data folder. Yech. This presumes that
Pervasive would even mount a database folder through the network
redirector, which I would HOPE it would not. It’s an extremely bad
idea. Considering how often Sage support has to resort to handwaving “network problems” as a cause for random STO issues,
you would be setting yourself up for trouble. However much more it
costs to add local storage to your STO server, it’s worth it. Ideally,
your STO server should be running Pervasive/STO and NOTHING
else. If you are running STO on a server that is hosting printers and
general purpose file sharing, then moving those roles OFF the STO
server and to a NAS is a win-win. Better STO performance, better
file serving performance, happier users.”
u8u
News and Information for Users of Sage 300 Construction and Real Estate & Sage Estimating
July 2013
Financial Statement Designer: Use Columns to
Insert Cap Rate Values & Mortgage Info!
by: Eire Stewart
JP DiNapoli Companies Inc.
W
ho says you can’t have it all? On a single Income Statement,
you can create Net Operating Income (NOI), add a Capitalization
Rate (Cap Rate), derive a Stream of Income Valuation, capture
the mortgage balance and calculate the property’s Net Worth! It does
take some fancy footwork, but once you understand how to change
column definitions and create a couple of new accumulators for your
calculations, you’ll be dancing in to Management’s office with some
impressive reports!
The first step is creating some GL Unit accounts to store Cap Rates for
each property:
GL Unit Account Setup
You create a unit account much the same way as you would any other
GL account.
1. In GL, [Setup], click on [Prefix/Base], then [Base].
2. Enter the base account number and “Stream of Income Cap
Rate”, and then designate the Account Type as Units. You have
all the same options you would have with other account in terms
of storing budgets and debit activity. The steps for the Account
Setup are the same as they would be for any other Account Type.
3.[Save].
Populating Unit Accounts
1.
Unlike other account types, Unit Accounts accept one sided entries.
a.Click on [Tasks], [Record Entries], and then select [Both] so that
the Cap Rate is available for any Financial Statement use.
b.Enter the Prefix and Base account numbers and the Cap Rate.
c.[Finish], [Start], [Post].
Financial Statement
1. Open Financial Statement, click on [File], [Open] and select an
Income Statement Design. In this example, I’ve used the Sage
Sample Data for Gold Coast design [IncSAccr.FSD].
2. We need to remove Interest Expense (we would also remove any
Amortization or Depreciation accounts) in order to determine
NOI for our calculation:
a.Click on the Account line for Expenses to open up the Amount
Options.
b.Change the From Account and To Account Exclude the Interest
account as follows:
3. Insert 10 lines below Net Income (Loss).
4. Click on two lines below Net Income (Loss). Type “Capitalized
Value” and click Bold and Underline.
5. Click on the next line, [Design], [Insert], [Amount] to open the
Amount Options. Select our newly created Stream of Income Cap
Rate account 81000 for the From/To spread. Type “Capitalization
Rate’ in the [Title] field and [Save].
6. Creating Accumulators – We need to create two Accumulators for
our calculations – one to be used in calculating our Capitalized
Value and a second to calculate Net Worth.
a.Creating our Cap Rate Accumulator:
i. Click on [Tools], [Accumulators].
ii.Name this Accumulator “Cap Rate”. Click on From Account
and List. Find the unit Base account created above called
“Stream of Income Cap Rate”.
iii.This Accumulator only pulls from one unit account, rather
than a spread, so the To Account will be the same as the
From Account.
o Continues
u9u
July 2013
News and Information for Users of Sage Timberline Office Software
iv.Tab over to Field and click on List. You have to opportunity
to select the Field in the same manner you would for
creating a column in FS. For this use, a selection of Balance
Fields and Current Balance will work nicely.
v.[Save].
b.Using Copy to create our NOI – Accrual Accumulator:
i. Select the existing Accumulator named “Default Net Income
– Accrual” and click on [Copy].
ii.Name this new Accumulator “NOI – Accrual” and change
the From/To Accounts to remove Interest Expense as
follows: [Save] and [Close].
9. Skip down one line, type “Less Loan Balance”, Bold and
Underline.
10. Now we need another new set of Column designs because
we need Current Balance amounts without the expression. Click
on [Design], [Insert], [Columns] and select [Account], [Account
Title], [Current Balance], [Current Balance], [Current Balance],
[Current Balance], [Horizontal Total], add all four columns for
our total.
11. In the respective Current Balance columns, select your Prefix
Groups, click [OK].
12. Skip down one line, click on [Design], [Insert], [Amounts] and
select a To/From range equal to your Note Payable base
account series.
13. Again, we need a new set of Column designs, this time to create
the Expression that calculates Net Worth. Unfortunately, Sage
CRE300 cannot add and subtract calculated results.
a.Click on [Design], [Insert], [Column], Click on [Design], [Insert],
[Columns] and select [Account Title].
b.Next, click on [Expression] and create the first expression as
follows:
7. Now we need a series of new Column designs.
a.Click on [Design], [Insert], [Columns] and select [Account Title].
b.Next, click on [Expression] and create the first expression as
follows:
c.Click [OK] and select a [Prefix Group] if this is a Columnar
Comparison statement; click the right arrow to create the
next column.
d.Click on [Expression] and create the second expression the
same as above, click [OK] and select a [Prefix group] select a
[Prefix Group] if this is a Columnar Comparison statement; click
the right arrow to create the next column.
e.Repeat as necessary for this statement.
f. Add [Horizontal Total], click [OK] to close [Column Options].
8. Click on the next blank line on our Income Statement, [Design],
[Insert], [Amount] to open the Amount Options.
a.Select our newly created Stream of Income Cap Rate account
81000 for the From/To spread. Click on [Summarize on one
line] and name the [Title] field “Capitalized Value.” Click on this
line and BOLD it.
c.Click [OK] and select Prefix Groups if necessary, click the right
arrow to create the next column.
d.Click on [Expression] and create the second expression the
same as above, click [OK].
e.Add [Horizontal Total, click [OK] to close [Column Options].
14. Skip down one line, click on [Design], [Insert], [Amounts] and
select a To/From range equal to your Note Payable base
account series.
15. Click [Summarize on one line] and name the [Title] “Net Worth.”
Modifications to include Cashflow Information
1.
2.
Skip down one line, type “Cash Flow”, Bold and Underline.
Now we need new Column designs because we need activity
totals as opposed to Current Balance. Click on [Design], [Insert],
[Columns] and select [Account], [Account Title], [YTD Net], [YTD
Net], [YTD Net], [YTD Net], [Horizontal Total].
o Continues
u 10 u
News and Information for Users of Sage Timberline Office Software
3.
4.
5.
6.
7.
8.
In the respective YTD Net columns, select Prefix Groups 400-1000,
400-1200, 400-1210, and 400-3600, click [OK].
Click on the next blank line on our Income Statement, [Design],
[Insert], [Amount] to open the Amount Options.
Next line, click on [Design], [Insert], [Total] and add the Net Income
(Loss) line several rows above.
Next line, click on [Design], [Insert], [Amount] and select a From/To
of 64000 to 64000, give this line [Print test] instructions of [Never
Print], [Save].
Next spread will be From/To equal to your Note Payable base
account series, give this line [Print test] instructions of [Never
Print], tick the [Reverse sign] box, [Save], [Close].
Next line, click on [Design], [Insert], [Total] and add the two lines
immediately above it, [Close]. Name this total “Debt Service”,
remove the [Line above] and [Close].
July 2013
Annualizing NOI for Correct Calculations
There are two methods for annualizing NOI calculations that can be
done in Sage 300 CRE.
Using Units to Calculate Months to Date:
1. Create a GL unit Account to store the number of months.
2. Setup this account for each of your property prefixes.
3. Create a Recurring Journal Entry for the newly created accounts,
designating the entries as “Monthly” and the debit amount as
$1. This Recurring Journal Entry will be posted once each month
to increase the number of months year to date.
4.
5.
Create an Accumulator for the Months GL unit account titled MTD.
Create a Columnar Expression that takes YTD Net/MTD*12. This
will yield an annualized amount based on the YTD Net Balance
divided by the months to date and multiplied by 12 months.
Financial Statement Monthly Field Types
9. Next line, click on [Design], [Insert], [Total] and add Net Income
(Loss) and subtract [Debt Service], [Close]. Title this line “Net
Cash Flow”.
Sage 300 CRE has four GL field types that can be used in FS. One of
the options is [Monthly fields]. To learn how to use these fields, read
the “Forecast for Great Financial Statements” article in this issue of the
TUG Pulse!
Now that you’ve mastered inserting new Column Definitions into your
Financial Statement Designs, you have a tool that will expand the
statements you create! You can add Fixed Asset balances to create
IRR calculations, feature 1031 Exchange information on statements
for use in analysis, add Prepaid Commissions activity and Tenant
Improvement costs to get to a true Cashflow number and a plethora of
other possibilities! See, you can have it all!
SAVE THE DATE
20
14
TUG National Users Conference
May 6-9, 2014
Gaylord Texan Resort & Convention Center
Grapevine, Texas
Plans are well underway to bring you a rip-roaring
good time, so saddle up and join us for four days
of non-stop educational sessions, hands-on labs,
roundtable discussions, third-party exhibits
and lots of networking, not to mention the
down-home feel of Texas.
u 11 u
July 2013
News and Information for Users of Sage Timberline Office Software
The Data Black Market...What is Your Data Really Worth?
by: Scott Lewis, President/CEO
Winning Technologies, Inc.
I
n the first six months of 2012, more than
twenty million pieces of personal data traded
hands through the data black market. To
put this into perspective, this amount is more
than twice as much data that traded hands in
all of 2011, and 2013 appears to be on track to
surpass 2012 numbers. Do you want to know
what your data is worth on the black market?
It is estimated about $1.2 trillion of stolen data
will trade hands in 2013.
How is this data stolen? First and foremost, bad
habits by internet users and IT professionals
leave cracks in the system which provides
an opportunity to steal data. These bad
habits include using the same password
on multiple accounts, failing to update
web browsers to current versions, not
checking websites for encryption by
looking for the padlock on the URL line
and failing to logout from websites.
These are all simple things that are
often overlooked.
The next question would be what
exactly is the data black market? In
simple terms, it is very similar to the
black market for actual products. It is
a pool of cybercriminals who make a lot of
money buying and selling stolen personal and
corporate data. These cybercriminals produce
malware in the form of viruses, SPAM, or other
programs that infect your system with the
intent of capturing your personal information.
In order for this cyber black market to be
successful, it takes a team of very skilled
individuals. One of the myths is that in a typical
cyber-attack it will be obvious to the end user
that they have been compromised; the reality
is just the opposite. Cybercriminals need
access to your system, both personal and
corporate. In order for the mining of data to be
successful, they must go undetected so there
won’t always be the alarm, flashing lights and
crashing systems.
So who makes up the team? It all starts with
the programmers; someone has to develop
the malware, SPAM or virus, develop the
delivery system, and determine how the
data is going to be delivered in the form of
your data. Web designers have to develop
the websites to capture and manage the
incoming data. Technical people who manage
the infrastructure and servers and who route
the data and the traffic to ensure that tracing
them is very difficult or near impossible, and
the intermediaries who take that data and find
sellers and buyers for the data and collect the
money for a percentage of the fee paid.
?
What are the most common items bought
and sold on the cyber black market? On the
personal side it is credit card numbers, social
media account passwords, and email lists. On
the corporate side it is accounting and banking
information such as direct deposit information,
emergency contact information, routing
numbers, and access to hacked servers.
Behind every virus, Trojan, worm or other
malware, there is a thriving business and
now, in some instances, government probing
and looking for weaknesses in our systems.
These businesses, just like legal businesses,
must promote themselves in order to buy
and sell their products or services. In some
cases they go as far as to have promotions,
u 12 u
demonstrations of their abilities, service
guarantees and even discounts for large
purchases or reoccurring purchases. How
does the sales process take place on the data
black market? Obviously these transactions
are electronic and very difficult to trace, but
the client and vendor take advantage of social
media to promote and start the negotiation
of the product. The product is then delivered
through a series of underground online stores
to actually conduct the transaction. They
establish a legitimate method of payment such
as PayPal or historical credit card transaction
or if it is a cash transaction, Western Union
is always available. Then, which has
always been interesting to me, is they
will actually establish a customer support
methodology so that if the credit card
number is invalid or the product is not
what the buyer expected, there is a way
to reconstruct the transaction because
remember, this is a business, repeat
customers are critical to the ongoing
black market economy.
Now that you have a basic understanding
of what the cyber black market is and
how it works, in the next issue of TUG
Pulse we will go through some steps on how
to protect yourself and your business so you
don’t become a victim.
About the author: Scott Lewis is the President
and CEO of Winning Technologies Group
of Companies, an international technology
management company. Scott has more than
30 years of experience in the technology
industry and is a nationally recognized speaker
on technology subjects such as Collocation,
Security, CIO level Management, Data and
Voice Communications and Best Practices
related to the management of technology
resources. Learn more about Winning
Technologies at www.winningtech.com or call
877-379-8279.
News and Information for Users of Sage Timberline Office Software
July 2013
TUG Conference – Changing User Perspectives
by: Carl “Ozzie” Mest
STV Incorporated
At the 2012 conference, I was approached by the leaders of the
Estimating committee and asked if I would be willing to increase my
involvement in the committee. As I enter into the my first term as
chairperson for the TUG estimating committee, I look at the difference
attending the TUG conference has made in my knowledge as an
estimator and how it is viewed and how TUG brings users together.
During my first TUG conference in 2010, the presenter in one of the
workshops asked who worked for contractors, property managers, etc.
When asked if there was anyone else, I raised my hand and stated I
worked for an Architectural Engineering (A/E) firm. Looking back on it
now, it is funny but back then it was somewhat uncomfortable. In my 29
years as an estimator, I have found that there seems to be this love/hate
relationship between engineering firms and contractors.
At that conference, over the next few days during meals and on our
breaks, there were discussions among attendees on how we create
a cost estimate. Through the years of working and learning it had
been emphasized that we were putting together a fair market value
estimate of what a particular project design based on the drawings and
specifications. My organization did not want to be the low bid or the
high bid. We wanted to be somewhere in the middle and come within
plus 5% minus 10% of the contractor low bid; which in most cases
we have been very successful in accomplishing. This is different from
being an estimator for a contractor where the responsibility is to come
up with an estimate for the project where the firm can make a fair
profit, while keeping employees working and employed. Talking about
these differences with my fellow conference attendees had taught me
a respect for the contractor estimators and I hope that they came away
with the same respect and understanding for the engineering estimators.
I appreciate the opportunity that TUG provides to network with other
users and share ideas on how to do things. TUG gives members a better
understanding of how other contractors in the industry do their jobs, how
they use the software and a better appreciation of others in the field.
Since joining TUG I have met some great individuals and I am looking
forward to the coming year as the estimating committee chairperson.
Have a great day, week and month!
TUG Online WebEx Training and Open Forums
AUGUST 2013
Tuesday
8.6.13
1:00 p.m. ET
PJ: USING STO DESKTOP + WORKFLOW + ENTERING CONTRACTS AND
SCHEDULE OF VALUES
Thursday
8.8.13
2:00 p.m. ET
REAL ESTATE MEETING PLACE- CONFERENCE 2014 PLANNING
Friday
8.9.13
2:00 p.m. ET
PUBLICATIONS COMMITTEE MEETING
Tuesday
8.13.13
1:00 p.m. ET
PJ CUSTOMER MANAGEMENT: BILLING WORKFLOW
Wednesday 8.14.13
2:00 p.m. ET
CONSTRUCTION ACCOUNTING COMMITTEE & FORUM
Tuesday
8.20.13
11:00 a.m. ET
SERVICE MANAGEMENT COMMITTEE & FORUM- PROCESSING PAYROLL IN
SERVICE MANAGEMENT
Tuesday
8.20.13
1:00 p.m. ET
PJ CUSTOMER MANAGEMENT: MANAGING OWNER CHANGE ORDERS
Tuesday
8.27.13
1:00 p.m. ET
PJ PROJECT MANAGEMENT TIPS & TRICKS
11:00 a.m. ET
ESTIMATING COMMITTEE & FORUM
Wednesday 8.28.13
To attend an online event, please email attendee(s) name and email address to [email protected]. You will receive an email confirmation
along with connection instructions once you are registered. These classes are free of charge and open to members only – one more benefit
of belonging to TUG! We are adding more sessions every day. Check the Online Event Calendar at TUGweb.com for an updated list. If you
have any suggestions on sessions you would like to see, please email [email protected].
u 13 u
July 2013
News and Information for Users of Sage Timberline Office Software
Forecast for Great Financial Statements
with a Chance of Awesome!
by: Eire Stewart
JP DiNapoli Companies, Inc.
F
or years, Users have been asking for the ability to create
statements that featured static months rather than STO’s
floating periods. Balance fields in Financial Statement Designer
had to be thought of in future, current and prior tenses in order to
accurately design a statement. Used in a FS design, Prior Period 1, as
an example, would equal January if the Period Ending date is set to
2/28/11, but would mean April when Period Ending reached 5/31/11.
Budget fields behave the same way. As a result, to accurately reflect
monthly activity and future budgets in a projection format, a FS design
needed to be created 12 times – a separate design for each Period
Ending date. 1 Year Ago fields work the same way. If a Monthly field of 1 Year Ago,
Period 1 is used in a design, a Prefix A set to a calendar fiscal year end
will always populate with the prior year’s January balances, no matter
what Prefix’s Period Ending date.
You may not even know they are there but, in STO release 9.5, Sage
added new “Monthly” fields. To access these fields, open a design
and click on [Design], [Insert], [Column]. The Insert Column dialog
box will open. Select [Monthly fields]. These Period fields are static;
their timing determined by the Fiscal Ending date for the Company
or Entity (Prefix A) being used. Period 1 – 12 will provide balances
based on where each period falls on your Fiscal calendar. If a Monthly
field of Period 1 is used in a design, a Prefix A set to a calendar fiscal
year end will always populate with January’s balances, regardless
of the Prefix’s Period Ending date. If the Period has not occurred yet,
the field will not populate. As an example, if used in a FS Design, the
Monthly field for Period 6 (in this case, June) would be blank if the
Period Ending date for this Prefix was set for Period 5 (5/31/2013).
Assuming a Calendar Fiscal Ending date of 12/31/2013 and a Period
Ending date of 6/30/2013, the Forecast fields would populate Balance
amounts for Periods 1 through 6, and would pull Budget amounts
for Periods 7 through 12. When using Forecast fields, you will need
to specify which Budget to use, just as you would need to do when
pulling Budget fields.
Even better, we now have “Forecast” fields. These are “intelligent”
fields that perform a system generated “if, then” statement, pulling
either the Balance field or the Budget field by comparing the Period to
the Period Ending date. It works like this: if Period 4, Forecast is less
than or equal to Period Ending date, then use Balance field; if not, use
Budget field.
Forecast fields will trigger a default header in Column Options of
“Month _” and “Actual/Budget”, however Sage CRE knows which
months this aligns with within your fiscal year and will automatically
generate the correct name of the month on the printed Statement. If
the Forecast fields results in a Balance amount being used, “Actual”
will print below the month. If a Budget amount is used, Sage CRE will
automatically print the word “Budget”.
o Continues
u 14 u
News and Information for Users of Sage Timberline Office Software
Monthly fields can be used in Expressions in the same way that you
would use Balance or Budget fields. Imagine a quarterly statement
that could project future balances. Now you can design it! An
Expression could be created for each fiscal quarter using Monthly
fields. Period1 + Period2 + Period3 would always return amounts
for your first quarter, and so on. If you used Forecast fields in these
Expressions, the system would automatically pull either the Balance or
the Budget information based on your Period Ending date. When using
Expressions to pull this data, you will no longer have a designation of
“Actual” or “Budget” on the printed statement.
Just imagine! Now, instead of creating 12 designs in the same
format but using different Periods, by pulling from Monthly fields,
you can create one design that automatically pulls Periods based
on your Fiscal Ending date! Cloudy skies are gone! The “forecast”
ahead is for terrific new Financial Statement designs and the outlook
is, indeed, awesome!
ESTIMATING TIPS & TRICKS
TO: The Guru Master
In the time crunch of doing a takeoff, my users sometimes don’t pay as much
attention as they should to the variables in an assembly and instead just accept
the defaults. Obviously, this can cause some problems. This is really only an
issue for the first pass, as most of the spec variables often don’t change. Is
there any way to require them to stop and look at the important variables?
– Accidentally Bought the Job
TO: Accident
Ahhh, the old “too busy to pay attention” ploy. Fortunately there is a cure
and it’s simplicity itself. You just need to set a minimum on the variable to
be greater than the default. For instance, if the default is blank (or 0) set the
minimum to 0.5. When the estimator gets to that variable they will not be able
to continue unless they enter a value greater than .5 (this assumes that the
variable is always more than .5). If the variable can be left blank or 0, set the
default for the variable to -1 and the minimum to 0. Change the default and
minimum values in Edit Variable. The easiest place to do that is to go to the
assembly with variables you want to control, click on the variable, then right
click and select Edit Variable.
– I am, as always, THE Guru
u 15 u
July 2013