ANNUAL UPDATE FOR FINANCE MANAGERS Jeanne H. Yamamura CPA, MIM, PhD
Topics: The Economy Excel Tips Open Source Software Cloud Computing Ethics TOO BUSY TO KEEP CURRENT?
DOING BUSINESS 2010 • Ongoing project sponsored by the World Bank and the International Finance Corporation (launched 8 years ago) • Benchmarks the ease of doing business in 183 economies worldwide looking primarily at laws and regulations • Goal – to provide an objective basis for understanding and improving the regulatory environment for business. • www.doingbusiness.org
1. STARTING A BUSINESS The challenges of launching a business • The number of steps entrepreneurs can expect to go through to launch • The time it takes on average • The cost and minimum capital required as a percentage of gross national income (GNI) per capita
2. DEALING WITH CONSTRUCTION PERMITS The procedures, time, and costs to build a warehouse • Obtaining necessary licenses and permits • Completing required notifications and inspections • Obtaining utility connections.
3. EMPLOYING WORKERS The difficulties that employers face in hiring and firing workers: • Difficulty of hiring • Rigidity of hours • Difficulty of redundancy (termination) • Redundancy costs (weeks of salary)
The ease with which businesses can secure rights to property: Number of steps Time required Cost involved 4. REGISTERING PROPERTY
5. GETTING CREDIT Addresses credit information sharing and the legal rights of borrowers and lenders: • Strength of legal rights • Depth of credit information • Availability of credit information through public registry or private bureau
6. PROTECTING INVESTORS Addresses three dimensions of investor protection: • Transparency of transactions (Extent of Disclosure Index) • Liability for self-dealing (Extent of Director Liability Index) • Shareholders’ ability to sue officers and directors for misconduct (Ease of Shareholder Suits Index)
7. PAYING TAXES The tax that a medium-size company must pay or withhold in a given year, as well as measures of the administrative burden in paying taxes. • Number of payments an entrepreneur must make • Number of hours spent preparing, filing, and paying • Percentage of profits to be paid in taxes
8. TRADING ACROSS BORDERS The costs and procedures involved in importing and exporting a standardized shipment of goods: • Documents to export • Time to export • Documents to import • Cost to export • Time to import • Cost to import
The ease or difficulty of enforcing commercial contracts: Number of procedures involved from the moment a plaintiff files the lawsuit until actual payment Time involved Cost involved 9. ENFORCING CONTRACTS
The time and cost required to resolve bankruptcies: Time Cost Recovery rate (claimants’ recovery rate from insolvent company) 10. CLOSING A BUSINESS
Singapore New Zealand Hong Kong TOP THREE COUNTRIES FOR DOING BUSINESS
LATEST ECONOMIC PROJECTIONS FOR 2010 • As of May 2010 • Solid (slow) growth through October • Increased consumer and business spending • Continued tight credit, high debt, high unemployment
IMPACT ON LOCAL GOVERNMENTS • Headline “Bond Costs Drag Reno Budget” 5/29/10 – Reno Gazette Journal • Bond payments of $2.5 mm for 2010-2011 a huge drain on general fund budget • Resulting in temporary shutdown of fire stations • Room taxes designated to cover bond payment expected to bring in $4.7 mm, not $7.2 mm needed for bond payment • Sales taxes will have to be tapped to make up difference
2002 City of Reno sold $108 mm in revenue bonds to build downtown events center and refinance National Bowling Stadium Bonds backed by 3 different room taxes, combined equal to a tax of 2.5% on lodging; Also backed by pledge of up to 15% of city’s sales taxes IMPACT ON LOCAL GOVERNMENTS
IMPACT ON LOCAL GOVERNMENTS • 2005 Bonds repackaged to free $18 mm cash to build Downtown Ballroom next to Events Center • One set of bonds sold for $72.6 mm in auction-rate bonds • The other two sets to be paid once the auction rate bonds are paid
IMPACT ON LOCAL GOVERNMENTS • 2008 Financial disaster!! • Auction-rate market collapses • City must pay extra $125,000 PER WEEK in interest on bonds • $5 mm reserve fund for bonds wiped out • Feb. 2009 – bonds switched to variable interest rate • Required payment to Bank of America of $1 mm fee for a letter of credit to guarantee payment • The $1mm fee must be paid annually • One set of bonds sold for $72.6 mm in auction-rate bonds • Now planning to refinance bonds again
SPREADSHEETS • A blessing and a curse! • Too many worksheets • Repetitive, redundant • No established protocol for creation • Hidden waste or time loss as a result • Errors!!
SPREADSHEET ERRORS • December 2007 – “Impact of Errors on Operational Spreadsheets” • Powell, Baker & Lawson • Study of 25 operational spreadsheets • Identified 381 potential errors • Confirmed 117 actual errors • 47 (40%) had no quantitative impact • 70 (60%) effect of error ranged up to $100 million • Only 9 spreadsheets had NO errors
MAKING A POLITICAL PLUS OF A MATH ERROR Star Tribune, 12 Apr 2006 Minneapolis-St. Paul, Minnesota DFLer Rebecca Otto is accusing Auditor Pat Anderson, a Republican, of sloppy work. In a column reporting the percentage changes in unreserved fund balances from 2003 to 2004, instead of dividing the difference by the 2003 figure, the auditor's office divided it by the 2004 figure. Deputy Auditor Tony Sutton said "The researcher who worked on that report just made a mistake in the formula in the spreadsheet. He feels bad about it."
HUD ALLEGES OVERPAYMENT FOR SECTION 8 Columbia Tribune, 22 Feb 2006 Columbia, MO • Government audit requested repayment of $216,352 resulting from excess Section 8 payments • Columbia Housing Authority agreed to pay $118,387 resulting from a spreadsheet data entry error that overpaid landlords
CONFIDENTIAL DATA RELEASED Sun Life Financial Inc. Toronto, Canada • Fourth quarter profits released a day early after possible leak. • The company’s quarterly statistical supplement is routinely provided to analysts before the actual earnings announcement. The supplement is supposed to have the latest quarter blanked out. • The spreadsheet with the info is converted into a document file. • But the censored information was retrievable using Acrobat software.
SPREADSHEET ANALYSIS • Inventory ALL of your Excel spreadsheets • Audit existing spreadsheets • Create and use spreadsheet templates
1. INVENTORY SPREADSHEETS • Generate list of file names • Click Windows Start button • Click Run • Type “cmd” (no quotes) and press Enter (or “command”) • Click “OK” • To list the files in the C drive in a text file named XLSlist.txt in a designated location, e.g., the F drive • dir C:\*.xls>F:\XLSlist.txt
CORRECTED COMMAND TO LIST SPREADSHEETS IN SUBFOLDERS From C:\Documents and Settings\Jeanne> dir_/s_*.xls>C\ExcelFiles.txt Note: The spaces are important!
1. INVENTORY SPREADSHEETS • Import data from the text file into Excel using the open file command • Text Import Wizard • Sort by columns using an “IF” statement to see if preceding file “equaled” the current file • Sort by date to find most current files
2. AUDIT EXISTING SPREADSHEETS • Formula Auditing Tool Bar • Error Checking – checks for errors in spreadsheet • Trace Precedents – arrows will identify source cells for formula elements in current cell • Remove Precedents’ Arrows
2. AUDIT EXISTING SPREADSHEETS • Formula Auditing Tool Bar • Trace Dependents – arrows will identify any other cells using data in current cell • Remove Dependents’ Arrows • Trace Errors – arrow will identify cause of error in current cell • Add Comment – comment entered appears only when cursor hovers over the cell, cells with comment marked by small red triangle
2. AUDIT EXISTING SPREADSHEETS • Formula Auditing Tool Bar • Circle Invalid Data – highlights cells which fail Data Validation rules • Note: To set up data validation rules, click on “Data” and then on “Validation” • Remove Circles Around Data • Show/Watch Window – enables you to watch cells and formulas of particular interest while you are moving around on a spreadsheet
2. AUDIT EXISTING SPREADSHEETS • Formula Auditing Tool Bar • Evaluate Formula – will walk you through each of the steps in a calculation
3. CREATE AND USE SPREADSHEET TEMPLATES • To create generic template: • File > Save • Select “Template (*.xlt) from “Save as Type” • Type in name and click “Save”
SUGGESTIONS FOR WORKSHEET TEMPLATES • Multiple tabs • File Info • File name, Date revised, Who revised • Assumptions • Analysis • Include comments or hidden text to help you remember where/how/why the data were obtained
SUGGESTIONS FOR WORKSHEET TEMPLATES • Maintain logical flow • Top-left to bottom right • Front spreadsheet to back spreadsheet • Move constants into their own cells • Break long and complex formulas into separate cells
OPEN SOURCE SOFTWARE • Software with freely available source code • Studied, changed, and improved by users • Often developed in public, collaborative manner • Estimated savings of $60 billion per year to consumers
SPECIFIC REQUIREMENTS FOR DISTRIBUTION OF OPEN SOURCE SOFTWARE • License must allow free distribution • Program must include source code and allow distribution of source code and program • License must allow modifications and derived works with same free distribution rights
SPECIFIC REQUIREMENTS • License may restrict distribution of modified source code if distribution of patch files allowed • License must not discriminate against any person or groups • License must not restrict use in any specific field of endeavor • Rights attached to program apply to anyone receiving program
SPECIFIC REQUIREMENTS • Rights attached to program cannot restrict to a specific product • No restrictions allowed on software distributed along with licensed software • License cannot rely on particular technology or interface (technology-neutral)
BENEFITS • Code for open source software widely available (e.g., UNIX, Linux OS, Apache) • Open source software may be more reliable • Open source tools are FREE! • Flexibility and the ability to fix code yourself
RISKS • Quality control of independent programmers • Lack of IT knowledge
EXAMPLES • Operating system • Ubuntu www.Ubuntu.com • Internet browser • Firefox www.mozilla.org
EXAMPLES • Customer Relationship Management • Vtiger www.vtiger.com • Budgeting and Forecasting • Adaptive Planning www.adaptiveplanning.com
OPENOFFICE.ORG • Multi-platform office productivity suite • Includes: • Word processor (Writer) • Spreadsheet (Calc) • Presentation manager (Impress) • Drawing program (Draw) • Database (Base) • Equations (Math) • Written in C++ • Runs on Solaris, Linux, Windows, Mac OS X, and other platforms