Transcript ANNUAL UPDATE FOR FINANCE MANAGERS - PITI-VITI

ANNUAL UPDATE
FOR
FINANCE MANAGERS
Jeanne H. Yamamura
CPA, MIM, PhD
APIPA 2010
1
TOO BUSY TO KEEP CURRENT?
Topics:
• The Economy
• Excel Tips
• Open Source
Software
• Cloud Computing
• Ethics
APIPA 2010
2
THE ECONOMY
APIPA 2010
3
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
APIPA 2010
4
OVERALL RANKING
(0UT OF 183)
Country
Ranking
2010
Ranking
2009
Change
FSM
127
125
(2)
Marshall
Islands
(RMI)
Palau (ROP)
98
93
(5)
97
92
(5)
APIPA 2010
5
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
APIPA 2010
6
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.
APIPA 2010
7
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)
APIPA 2010
8
4. REGISTERING PROPERTY
The ease with which
businesses can
secure rights to
property:
• Number of steps
• Time required
• Cost involved
APIPA 2010
9
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
APIPA 2010
10
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)
APIPA 2010
11
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
APIPA 2010
12
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
APIPA 2010
13
9. ENFORCING CONTRACTS
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
APIPA 2010
14
10. CLOSING A BUSINESS
The time and cost
required to resolve
bankruptcies:
• Time
• Cost
• Recovery rate
(claimants’
recovery rate from
insolvent company)
APIPA 2010
15
OVERALL RANKING
(0UT OF 183)
Country
United
States
Philippines
China
APIPA 2010
Ranking
2010
4
Ranking
2009
4
Change
144
141
(3)
89
86
(3)
-
16
COMPARISON 2010
FSM
RMI
ROP
Starting a business
79
39
103
Dealing with
construction permits
Employing workers
11
5
54
14
4
8
Registering property
183
183
18
Getting credit
113
150
183
APIPA 2010
17
COMPARISON 2010
FSM
RMI
ROP
Protecting investors
172
154
172
Paying taxes
86
94
91
Trading across
borders
Enforcing contracts
98
64
124
149
63
144
Closing a business
154
128
59
APIPA 2010
18
TOP THREE COUNTRIES FOR
DOING BUSINESS
1. Singapore
2. New Zealand
3. Hong Kong
APIPA 2010
19
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
APIPA 2010
20
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
APIPA 2010
21
IMPACT ON LOCAL
GOVERNMENTS
• 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
APIPA 2010
22
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
APIPA 2010
23
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 auctionrate bonds
• Now planning to refinance bonds again
APIPA 2010
24
EXCEL TIPS
APIPA 2010
25
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!!
APIPA 2010
26
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
APIPA 2010
27
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."
APIPA 2010
28
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
APIPA 2010
29
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.
APIPA 2010
30
SPREADSHEET ANALYSIS
1. Inventory ALL of your Excel
spreadsheets
2. Audit existing spreadsheets
3. Create and use spreadsheet
templates
APIPA 2010
31
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
APIPA 2010
32
CORRECTED COMMAND TO LIST
SPREADSHEETS IN SUBFOLDERS
From C:\Documents and Settings\Jeanne>
dir_/s_*.xls>C\ExcelFiles.txt
Note: The spaces are important!
APIPA 2010
33
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
APIPA 2010
34
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
APIPA 2010
35
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
APIPA 2010
36
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
APIPA 2010
37
2. AUDIT EXISTING
SPREADSHEETS
• Formula Auditing Tool Bar
– Evaluate Formula – will walk you through
each of the steps in a calculation
APIPA 2010
38
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”
APIPA 2010
39
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
APIPA 2010
40
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
APIPA 2010
41
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
APIPA 2010
42
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
APIPA 2010
43
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
APIPA 2010
44
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 (technologyneutral)
APIPA 2010
45
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
APIPA 2010
46
RISKS
• Quality control of independent
programmers
• Lack of IT knowledge
APIPA 2010
47
EXAMPLES
• Operating system
– Ubuntu www.Ubuntu.com
• Internet browser
– Firefox www.mozilla.org
APIPA 2010
48
EXAMPLES
• Customer Relationship Management
– Vtiger www.vtiger.com
• Budgeting and Forecasting
– Adaptive Planning www.adaptiveplanning.com
APIPA 2010
49
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
APIPA 2010
50
WRITER
APIPA 2010
51
WRITER
• Create memos, books, and other
documents
• Built-in wizard for standard
documents, e.g., letters, faxes, minutes
• Create personalized templates
• Change styles and formats
• Spell check, AutoComplete,
AutoFormat
APIPA 2010
52
WRITER
• Email documents
• Save in HTML, PDF,
OpenDocument
format (XML), or
Word format
• Read Microsoft Word
documents
APIPA 2010
53
CALC
APIPA 2010
54
CALC
• Create formulas
• Use Wizards for advanced
spreadsheet functions
• Pull in data from corporate databases
• Create “what if” scenarios
• Apply flexible cell formatting options
• Save in Open Document, PDF, or Excel
format
APIPA 2010
55
Let’s
take
a look
APIPA 2010
56
CLOUD COMPUTING
Source: http://www.ekinsystems.com/Portals/0/cloud%20computing.png
APIPA 2010
57
CLOUD COMPUTING
• Internet-based computing
– Delivery of hosted services over the
Internet
– All you need is a personal computer and
internet access
– Sold on demand, use a little, use a lot
– Service fully managed by provider
• Save investment in hardware and software
plus computer staff
APIPA 2010
58
CLOUD COMPUTING
• Three categories of hosted services
– Infrastructure-as-a-Service (IaaS)
• Provides virtual servers with unique IP addresses
and blocks of storage on demand
• Pay only for as much capacity as you need
• Amazon Web Services
– Platform-as-a-Service (PaaS)
• Provides software and product development tools
• May access through application programming
intefaces (APIs), website portals, or gateway
software
• Force.com, GoogleApps
APIPA 2010
59
CLOUD COMPUTING
• Three categories of hosted services
– Software-as-a-Service (Saas)
• Provider hosts application and data
• Customer accesses via front-end portal
• Services range from Web-based email to
inventory control and data processing
APIPA 2010
60
CLOUD COMPUTING
•
•
•
•
•
•
•
•
Benefits
Easy implementation
Frees up resources
Possible higher service
quality
Easy to
increase/decrease
Reduced capital outlay
Automatic software
updating
Easier data access
Reduced floor space
APIPA 2010
Risks
• Loss of control
• Internet transmission of
information
• Privacy and security
concerns
61
ONLINE FRAUD
• JM Test Systems, an electronics testing
firm in Louisiana, lost $97,000 in two
separate fraudulent bank transfers
• 2/20/09 $45,640 unauthorized wire
transfer identified and bank notified
• 2/25/09 bank issued new user name and
password
• 3/2/09 $51,556 fraudulently added to
payroll
• JM suing bank to recover funds lost
APIPA 2010
62
ONLINE BANK ACCESS
• European cyber gangs targeting small
businesses (and governments!!)
• Private security alert sent to largest
banks (e.g., Bank of America,
Citigroup)
– “significant increase in funds transfer
fraud involving the exploitation of valid
banking credentials belonging to small
and medium sized businesses”
APIPA 2010
63
ONLINE BANK ACCESS
• Scammers send targeted email to
controller or treasurer
• Message contains virus in attachment
or link that secretly installs malicious
software designed to steal passwords
• Series of wire transfers then initiated,
usually in amounts less than $10,000
APIPA 2010
64
ONLINE BANK ACCESS
• “Carry out all online banking activity from a
standalone, hardened, and locked-down
computer from which email and Web
browsing is not possible”
– Virgin install of Windows with all proper updates,
using Microsoft steady state
– Even better, a Mac or Linux
• Anti-virus software may not detect the
increasingly sophisticated malware
APIPA 2010
65
ONLINE BANK ACCESS
• Business protection from online fraud
less than for consumers
– Consumers typically have up to 60 days
from receipt of monthly statement
– Companies have two business days to
identify and dispute unauthorized activity
• Commercial banks may not have
strong controls in place to protect
companies as they do not cover losses
APIPA 2010
66
APIPA 2010
67
ETHICS IN THE NEWS
“Ethics office asks for investigation of
lobby firm PMA Group’s clients” Friday, May 28, 2010
• Review of documents suggesting
companies are providing campaign
cash to lawmakers for no-bid contracts
• One example – attendance at a winetasting fundraiser
APIPA 2010
68
ETHICS IN THE NEWS
“A culture of ethical failure” seen at Interior –
Thursday, Sept. 11, 2008
• Three reports by Office of Inspector General
released
• Minerals Management Service
– Drug use and sexual activity by top management
– Nearly 1/3 of employees in royalty-in-kind
program accepted gifts from oil companies
– “Pervasive culture of exclusivity, exempt from
rules that govern other employees of the federal
government”
APIPA 2010
69
FAILURE SO FAR
• Four decades of good faith effort
– To teach ethics in business schools
– To require additional ethics training for
employees
– To increase ethical awareness
• Still see headlines about egregious
excess and scandal on a regular basis
APIPA 2010
70
NEW APPROACH – GIVING
VOICE TO VALUES
• Most ethics courses focus on
hypothetical decision-making and
determining what is the right thing to
do
– Theories of ethical reasoning
– Analysis of complicated dilemmas
• But “knowing” does not lead to “doing”
APIPA 2010
71
NEW APPROACH – GIVING
VOICE TO VALUES
• Often the issue isn’t distinguishing
between right or wrong
• It is knowing how to act on your values
despite opposing pressure
• What is needed: Skills in figuring out
what to do
APIPA 2010
72
GIVINGVOICETO VALUES.ORG
• www.givingvoicetovalues.org
• Funded and hosted by Babson College
• Developed new action-oriented
approach for values-based
leadership
APIPA 2010
73
SKILL SET
When an
ethical
question
arises
• What to say
• Whom to say
it to
• How to say it
APIPA 2010
74
THE STORY
• George, college
student
• John, night manager
for car parts store and
good friend
• George got ideal PT
job at store
–
–
–
–
Close to school
Flexible hours
Work fun
Work with John
APIPA 2010
75
THE STORY
• George worked at night
with John
• After one month,
George noticed that
– John disappeared for
30 minutes at a time
– John constantly in
back room checking
inventory
– John the only manager
who does this
APIPA 2010
76
THE STORY
What George saw or heard
• John would bring back list
with different part
numbers and then print
invoices from the
computer
• John would remove
money from the register
• John said it was a routine
procedure for sales
returns
APIPA 2010
77
THE STORY
APIPA 2010
• John agreed to tell
George the secret if he
promised not to tell
• George agreed
• John looked up
inventory on computer
and then checked to see
if number was correct
• If not and extra part in
storeroom, John used
sales info to give a cash
refund that he would
keep.
• As manager, John could
approve refunds
78
THE STORY
• Obvious to George
that this is wrong –
both dishonest and
illegal
• But he has been
friends with John for
more than 5 years
• And he doesn’t want
to be a “rat”
• He needs to talk to
John
APIPA 2010
79
DISCUSSION QUESTION #1
What are the main
arguments John is likely to
raise? That is, what are the
reasons and rationalizations
George needs to address?
APIPA 2010
80
DISCUSSION QUESTION #2
Who are the key
parties and what’s at
stake for them?
APIPA 2010
81
DISCUSSION QUESTION #3
What levers /
arguments can George
use to influence John?
APIPA 2010
82
DISCUSSION QUESTION #4
What is the most powerful
and persuasive response to
the reasons and
rationalizations George
needs to address?
APIPA 2010
83
DISCUSSION QUESTION #5
What should George say to
John, and when, and where?
APIPA 2010
84
THE END!!
Questions/comments: [email protected]
APIPA 2010
85