September 2015 Newsletter
Lost in Spreadsheets
“In large spreadsheets with thousands of formulas, there will be dozens of undetected errors.”
What we’re talking about in September:
- Spreadsheet errors are pandemic. But you knew that.
- CMD Groups Economic Index – betcha can’t sort just one!
- Drum roll, please! Introducing Sage HRMS!
- That great Sage Add User and Modules once-a-year special.
- Tips and Tricks for Sage 100 CON, Sage 300 CRE and Sage Estimating.
The Dangers of Spreadsheets
It’s probably safe to say in your organization that EVERYBODY uses spreadsheets. We all export data from our legacy systems and dump it into Excel, and we slice and dice to our heart’s content!
That Does Not Compute
Did you know that Market Watch says that 88% of spreadsheets contain errors? And we’re not talking just fat-fingering or copy + paste errors. There are math errors are in the formula cells! The article quotes Ray Panko, a professor of IT management at the University of Hawaii and an authority on bad spreadsheet practices, “Given that Microsoft says there are close to 1 billion Office users worldwide, “errors in spreadsheets are pandemic…”
In a very dramatic example, one spreadsheet error cost shareholders of TimCo $100 million! A regulatory filing by Goldman Sachs – based on an Excel spreadsheet – implied the company’s value at $4.2 billion. An analysis revealed an Excel error (duplicate) which placed the company’s value at $4.1 billion. Read the rest of the story here.
The Devil You Know
The familiarity of Office software is the only driving reason I can find for migrating and mashing data on personal desktops, where the value benefits an individual in lieu of becoming a corporate ASSET. Or worse yet, that the customized analysis doesn’t reflect a global picture of the company and is used for personal or departmental advantages.
It’s amazing how large of a business problem spreadsheets can be. And employees in EVERY role use them, from accounting and HR, to sales and marketing, and estimating to procurement. Let’s look at a couple of reasons how spreadsheets are not performing to current business needs.
Why spreadsheets are wrong for Construction Estimating:
- Generational transition
If your estimators are baby-boomers, there is a good chance that they built custom, personalized estimates in Excel, which they save to their local drive…or rarely save to a network/shared drive. As millennials enter the job market, they have been educated in the use of modern software applications and are likely to expect those modern solutions when considering accepting a job. They have grown up in the age of databases, rate tables, and integrations…not spreadsheets with pivot tables. By continuing to use spreadsheets, decades of experience and legacy knowledge can be lost with one erroneous click of the mouse while being in a centralized system provides the peace of mind that all historical data is always available at the tips of your fingers.
- Comparing estimates to estimates
You could open five spreadsheets and drag and re-size the five windows around and maybe get a glimpse of five projects in Excel. OR you could run comparison and variance reporting across those five projects with a couple keystrokes.
- Breaking down an estimate
Can your spreadsheet handle multi-building estimates, multiple work breakdown structures, and various other bid breakdowns? Can it handle variable cost add-ons, such as overtime hours, or material cost escalations?
- Protecting your work
Ever close a spreadsheet without saving it by accident? Bye, bye valuable hours of work. When you’re in Sage Estimating – your work is saved every time you press a key! That peace of mind is critical for trusting in your software investment.
Why spreadsheets are wrong for CRM:
- Protecting your contacts database
Is your company rolodex going to walk out the door with the salesperson you let go because of non-performance? Contact data is a company asset, and not a recruiting asset for your competitor.
- Accountability and sales visibility
How do you know how your salesperson is performing or what stage a lead is in, in the sales funnel? How can you even forecast your sales without access to a company-wide process for handling the sales cycle?
- Customer relationships
Yes, just as the name implies, you need to manage customer relationships pre-, during, and post-sales. How can one department follow know to follow up with a customer without a process in place? How could sales ever convert a service ticket to a cross or upsell opportunity without documentation in a central system?
Lastly, spreadsheets are not configured to run your business, protect you from risk, leverage your assets, alert or notify you of potential business dangers. When you purchase software that is specific for an industry, it should be from a Value Added Reseller like Ledgerwood who can help you analyze the gaps in your system, recommend core solutions and complementary products, and lastly, be a local resource to help you implement and/or troubleshoot down the line.
While I don’t believe we’ll ever see the extinction of spreadsheets, they should be appropriately used for the tool that they are. Just a small tool in the big picture.
Buy 2 Get 1 Free
Sage’s Annual Add Users and Modules Promotion
B2G1 “on schedule” modules:
- AP, AR, Payroll
- Fixed Assets
- Project Management
- Property Management
- Service Ops
Additional OEM products:
Purchase two Sage 300 CRE Modules or User licenses and get one FREE Module or User license!
Order online HERE, or call 480-423-8300
Our friends at CMD Group are always coming up with relevant, up-to-the-minute construction data. One of their latest industry contributions is the Expansion Index. Sortable by two overall indices (MSA and state), you can also sort by eight construction verticals.
What does an Expansion Index show?
“RCD (CMD) collects data about commercial non-residential construction projects across all vertical markets from architects in the US and Canada. Included in the database is new construction, renovation, and stimulus recovery projects.”
The dynamic map shows three segments:
- Locations with shrinking markets (an Expansion Index of 1.0 or less)
- Locations with expanding construction markets an Expansion Index greater than 1.0)
- “Hot spots” – locations that are rapidly growing (an Expansion Index greater than 5.0) Construction volume in these locations is expanding dramatically in the next 12 months.
Ledgerwood Associates, Inc. has expanded our portfolio again. We are proud to announce that we are adding HRMS to our Sage suite. We already have two companies that are implementing HRMS along with CRM and Sage 300 CRE.
Sage HRMS is a complete, human resource management solution for small and medium sized businesses, enabling you to maximize every dollar you spend on employees.
The core modules in Sage HRMS are Payroll, HR, Employee Self Service (ESS), and My Workforce Analyzer (MWA). Additional modules like Talent Management, Decision Support, or Recruiting and Onboarding are available to fully empower your HR team. Your people are your most valuable asset! Take a look at an overview of Sage HRMS here.
“In the Family” – 20% Savings
Do you currently have Sage 300 CRE or Sage 100 CON and are thinking about investing in an HRMS system? Now is the time to pull that trigger! Between now and September 28th, customers on either of those construction solutions will receive a 20% savings benefit.
Free Estimating Webinar
Apples to Apples: Standard Estimating vs. Extended
Join LAI’s Tony Merry on Wednesday, September 16th as he goes “apples to apples” and demonstrates the functions and differences in the two Sage estimating products.
- Do you need crews, resources and rates tables?
- Have you grown to need more than one user-defined WBS level?
- Want to compare multiple estimates?
Learn more from Tony – because he’s USED the software. Pick his brain in this interactive session! REGISTER HERE.
Want more power? Sage is currently offering half off the difference to upgrade from Standard to Extended Estimating! Click HERE for pricing or to order! ENDS SEPTEMBER 28TH.
Don’t forget to forward your newsletter to a friend, and follow us on Social Media for immediate construction and technology news!
Twitter | Facebook | LinkedIn LAI Page | LAI Sage 300 CRE Users Group
Upcoming LAI Online Training and Networking Events:
Employee W-2 Preparation and Audit
Join an LAI consultant for a live, online FREE session.
1099 Preparation for Year End
Join an LAI consultant for a live, online FREE session.
GL vs. Job Costs Reconciliation
Join an LAI consultant for a live, online FREE session.
1099 Prep for Year End
What’s Your Type?
Submitted by Walt Mathieson, Certified Sage 100 Consultant
Last month I wrote about creating a user-defined Purchase Order Type to help track and control materials returned to suppliers for credit. Now, I’d like to review ideas for Types in the many areas where users can create their own:
- 3-5 Jobs – Types can be created to categorize the work (e.g. painting vs drywall for an interior finishes subcontractor) or billing method (e.g. time and materials billing vs progress billing) of jobs. Less desirable uses of Job Types might be to use types to identify the salesperson, project manager or department as fields already exist for these purposes.
- 3-6 Receivable Clients – Types can be created to categorize the clients for an organization. Options may be numerous, and input from management is often sought so that we can respond to data requests by client type. Options may include commercial vs. residential, multifamily property manager vs commercial general contractor, homeowner vs investor – anything management may find useful.
- 4-4 Vendors – Types can be created to categorize the vendors for an organization. Vendor Types can be simple (e.g. suppliers, subcontractors, overhead vendors) or more detailed (lumber yard, plumbing supplier, excavation subcontractor, plumbing subcontractor, insurance, banking, employees, etc.). Consider how detailed vendor types can help an estimator, purchasing agent or project manager find “all the plumbers we have used in the past” when seeking quotes.
- 6-4-1 Change Orders – Since change orders have several roles in Sage 100 Contractor, use Types to help separate them into their various roles – prime contract changes, internal budget adjustment changes, subcontract change order only, etc. Then use the Type as a report selection criterion when running a change order list report so that you only get the types of change orders you want in the list.
- 6-6-1 Purchase Orders – In addition to a type for materials returned, types can be used to separate job materials purchases, overhead purchases and stock or inventory purchases. If purchase orders are also used for subcontracts (rather than the 6-7-1 Subcontract module) a separate type for those may also be in order. Types can be used as report selection criteria to ensure that job materials purchases print to a specific materials purchase order report form, and subcontract purchase orders print to a separate, subcontract-specific report form.
- 6-7-1 Subcontracts – Types can be used in the subcontract module to relate specific specialized report forms to types of subcontracts. For instance, you may want to use different report forms for subcontracts that only provide subcontract labor, subcontracts that provide labor, installation materials and customer selected fixtures, or subcontracts that are for professional services like architectural and engineering services. In each case, the report form may have different terms and conditions that are important to the user.
- 6-11 Document Control – Types can be defined for several documents to relate them to specialized reports and report forms:
- 6-11-1 Requests for Proposals
- 6-11-2 Requests for Information
- 6-11-3 Transmittals
- 6-11-4 Submittals
- 6-11-8 Correspondence
Each document has the option of creating user defined types that may be useful to your specific business. For instance, you might have separate request for proposal forms for materials purchase quotes and subcontract quotes. Requests for information might be categorized as those to an architect or engineer, versus those to the owner or owner representative. Correspondence might be categorized as to format – memorandum vs formal letter. The possibilities are many.
- 8-3 Equipment – Users of the equipment management module can create their own list of equipment types to categorize their equipment for reporting purposes.
Sage 100 Contractor is designed to be adaptable to most clients’ unique needs. Take advantage of these options to make it work for you. Contact your Sage Certified Consultant for more information or assistance.
Current Version Information
The latest “recommended” version of Sage 100 Contractor is version 19.6.41, the release of which was announced on May 28, 2015. Since then, there have been two “Optional” updates that contain payroll tax table updates for North Dakota, Connecticut, Ohio and Idaho, plus a couple of minor report fixes. You may want to consider the optional updates as well.
More about Sage 100 Contractor here. Call Ledgerwood Associates at 877-918-8301 today and we’ll match your needs to the best solution.
Email Walt at email@example.com.
Retiring Closed Bank Accounts
Submitted by Kyle Zeigler, Sage Senior Certified Consultant
Bank accounts are a critical component in the everyday processing of transactions in Sage 300 CRE software. When organizations change banks, new bank accounts are created in Cash Management (CM) and the old bank accounts are no longer used. For some Sage 300 CRE users, particularly those with property development and property management operations, the number of CM closed bank accounts can be bothersome.
Options for managing closed bank accounts are:
- Deactivate the account to prevent it from being used by interfacing applications.
- Use Cash Management > Setup menu > Bank Accounts to select the bank account.
- On the GL Cash Accounts tab, clear all check boxes in the Select the applications that can use this bank account section and click Close.
Note: The bank account can still be used in Cash Management and will appear in inquiries and ranges for reports. Change the bank account description to “Do Not Use” if needed.
- Delete the account.
- Verify that all accounts to be deleted have been fully reconciled and that all balances are zero.
- Use CN > Tools > Move Entries > Register to History to move all transactions for the selected bank account to history.
- Use Common Tasks > Tools > File Tools to make a backup of the Master.CMM file. Do not delete accounts without first making this backup!
Note: If you delete a bank account and you have not moved all your transactions to history, or your bank account balances are not zero, then Cash Management may appear out of balance and some reports may not work correctly.
If you are uncomfortable deleting bank accounts, or if you encounter issues in reconciling closed accounts or moving all transactions to history, use the first option or contact LAI for assistance at 480.423.8300.
Seven Tips to Make Job Cost Accounting Easier
Submitted by Bryan Eto, CPASeven Tips to Make Job Cost Accounting Easier
Job costs are the lifeblood of your construction business and accurately estimating them will determine if a project will make money. Managing job costs across the life of the project will ensure that your firm makes money on every job. Moreover, those job-by-job profits make the office and your executive salary possible.
Despite this, some Owners, CFOs and controllers don’t take job costs seriously! Some see tracking those costs as more trouble than it is worth, while others think that the costs are so obvious that tracking them seems like extra, unnecessary work. Neither are true and both can limit your firm’s profitability.
Tip #1: Set Priorities at the Top
Tracking job costs is a process that involves every level of your organization. All of your valued employees intuitively know the value of tracking costs by job. If you begin to place an emphasis on the accurate identification of every cost by job for every purchase, they will gladly join in and help identify jobs with enthusiasm.
Tip #2: Set up Solid Communication Between the Field and the Office
Cost tracking starts in the field, where the materials are delivered and the purchase decisions are made. Field people are well-placed to know which costs go with the jobs. The trick is making it easy for them to flag the job name or number so that the person entering the invoice, credit card or debit card charge into the computerized accounting system can follow the process of assigning the proper cost code.
Tip #3: Provide Information to Bookkeeping Staff Readily
Bookkeepers may be tempted to let it go when the job information isn’t available, promising to assign the proper job number later. This is the single most common source of errors. Making job information readily available to the bookkeeping staff is the best way to counteract this tendency for misinformation to cloud your reports.
Tip #4: Require Purchase Orders
Purchase orders are a good way to ensure the success of your job cost system, so have your accounting, finance or tax professional help you develop a good system. Purchase order systems work when the office must issue a unique order and all supplies must get purchase order numbers from field staff before providing materials to any job. An effective system helps ensure that no invoice will come to your office without a job identified on it.
Tip #5: Use Caution Handing Out Company Credit Cards
With credit and debit cards, there is usually no way to include a job name or number on the receipt. Provide cards only to responsible crew leaders. They should be required to send receipts right away to the office that identify the jobs. This can be done either by texting receipts as images, e-mailing scanned copies of receipts to the bookkeeping department, or dropping paper receipts off with the job name or number marked on them.
Tip #6: Clearly Separate Costs
Job costs differ from office and overhead costs by getting a job number that is distinct from the general ledger account number. The chart of accounts or general ledger can be a help or a hindrance depending on the skill of the accounting, finance or tax professional who develops your job cost system.
For example, general ledger expense codes typically start with the 5,000 series of account numbers. Job cost tracking then becomes easier for everyone if they are coded with 5,000 series numbers, which allocated costs are coded with 6,000 series numbers and office and overhead costs get 7,000 series account numbers.
If the chart of accounts and job cost ledger are set up professionally, cost allocations will become easier and more accurate and job cost reports will be more accurate and useful.
Tip #7: Follow Best Practices
The actual job number you assign should be carefully chosen following best practices. For example, a good job number is not just the next number in a haphazard sequence that starts with some arbitrary number and has three or four digits. A good job number always conveys information such as the year the project started, the specialty trade involved, and whether the expenditure was a material cost, equipment rental cost, labor cost, or subcontractor cost.
Consult with your accounting, finance, or tax professional who is familiar with construction best practices. This will make your life easier down the road as well as more profitable.
2201 E. Camelback Rd.
Phoenix, AZ 85016
Job Cost Coding in Estimating
Submitted by Jim Hoeppner, Sage Senior Enterprise Managing Consultant
Sage Estimating can send over budgets and change orders automatically to Sage 300 Accounting. This saves a great deal of time over manual entry. The items in the database however, need to be cost coded and categorized correctly. If you try to enter in different cost codes or categories than what exist in accounting, there will be a reject file created, meaning that an incomplete budget was sent over.
To code and categorize the estimating database correctly, you can use Database Editor. It will allow you to spread the cost codes over a large group of items that can roll up to that coarser level of detail, meaning the job cost code. Also, you can quickly change the categories. However, Estimating Tools is probably the most efficient way to change the categories to the L, M, S, etc., naming convention.
When you think have cost coded and categorized correctly, it is worthwhile to do a test by pulling off all the items that could possibly be used into an estimate. Then you would sort that estimate by cost code to find any problems. Also, when exporting the budget to job cost, you will generate a report aside from the file. This report should be reviewed before proceeding with the import.
As for the change order, it is probably only worthwhile for contractors who do a lot of self-perform work. One or two lines change orders are easy to create within Project Management, when sending a CO. Any items that would go over would need to be cost coded and categorized correctly, just as in the case of sending over a budget.
Would you like a copy of our newsletter in PDF format? Email Joanie@ledgerwoodassociatesusa.com and we will email it to you!