Exercise #1
The first exercise is a financial condition analysis. The material needed to complete the exercise is contained in Ch. 3, Financial Statement Analysis, from the Kioko and Marlowe text. You will be using the data from the Case: The Safe House found on pages103 – 112.
Instructions:
Review the financial statements for The Safe House including the Statement of Activities, Statement of Financial Position, Statement of Functional Expenses, the Statement of Cash Flows, and the Notes to the Financial Statements. Focus on the major account groups of assets, liabilities, net assets, revenue, expenses, and the major elements from the Notes to the Financial Statements. Use the questions posed by the authors on pages 80-81 to help guide your review.
Identify the ratio indicators that are used for assessing Liquidity, Profitability, and Solvency that are associated with non-profit entities. They are found on pages 83 – 85. Further, include the Nonprofit Effectiveness Ratios as noted on page 84.
Using Excel create a data table at the top of a worksheet or as a separate tab in the same Excel file. The data entered should include each numerical element used in the ratios for 2015 and 2016 taken from the financial statements.
In the same Excel file create a table similar to that seen on page 88. Group the indicators by Liquidity, Profitability, and Solvency and label the spreadsheet accordingly. Also make sure you include the title, computation formula, and Rule of Thumb for each ratio.
Once you have entered all the information, calculate the ratios by using an Excel formula that refers to the cell locations of the data. (This will provide you with a flexible spreadsheet)
Using the results, the Rules of Thumb and the two-year trend, interpret each ratio relative to Liquidity, Profitability, and Solvency.
Assess the overall financial condition of the entity based on your review of the financial statements, and your interpretations of the ratios.
Based on your assessment, what strategies can or should the entity take to address any areas of concern?
Identify the limits and weaknesses of this work. What could or should be done to insure a better-quality assessment?
Write a memo to the Executive Director and Board of Directors of The Safe House organization. Your role is that of a financial analyst. The subject of the memo is the financial condition analysis of The Safe House organization. Use all the work that you conducted in the steps above to prepare your memo. Think carefully about how you organize the memo. The purpose of the memo is to inform the Executive Director and Board about the overall financial condition of the entity, identify the problem areas, and offer some suggestions for addressing the areas of concern with respect the results of the analysis and the approach taken to assess the financial condition of the entity.
Here is the text where the information is on: Just click on the link below
The is a free, open-source text and can be accessed at https://press.rebus.community/financialstrategy/
Here is an example of how i have to do it….. and then i have to write a memo explaining my findings
Financial Ratios for Treehouse in FY 2015
Ratio
Computation
Computation for Treehouse
Liquidity Ratios
Current Ratio
(Current Assets)/(Current Liabilities)
($6,997,996)/($467,461) = 14.97
Days of Liquid Net Assets
((Unrestricted Net Assets-Fixed Asset, net depreciation))/(((Total Expenses-Depreciation-Bad Debt))⁄365)
(($5,255,411-$0))/((($9,381,598-$198,775-$0)/365) = 208.89
Quick Ratio
(Cash & Cash Equivalents+Receivables )/(Current Liabilities)
(($2,713,337+($2,056,445+$193,357+$252,784))/($467,461) = 11.16
Days of Cash on Hand
(Cash & Cash Equivalents )/(((Total Expenses-Depreciation-Bad Debt))⁄365)
($2,713,337)/(($9,381,598-$198,775-$0)/365) = 107.85
Profitability Ratios
Operating Margin
(Change in Unrestricted Net Assets)/(Unrestricted Revenue)
($706,007)/($10,153,840) = 0.07
Net Asset Growth
(Change in Net Assets)/(Change in Unrestricted Revenue)
($1,057,657)/($706,007) = 1.50
Return on Assets
(Change in Net Assets)/(Total Assets)
($1,057,657)/($12,614,410) = 0.08
Solvency Ratios
Debt to Assets
(Total Debt)/(Total Assets)
($0)/($12,614,410) = 0
Contributions Ratio
(Contributions Revenue/Total Revenue)
($7,484,460)/($10,625,911) = 0.70
Government Revenue Ratio
(Government Revenue/Total Revenue)
($1,261,618)/($10,625,911) = 0.12
Effectiveness Ratios
Fundraising Efficiency
(Total Contributions/Fund raising Expenses)
($7,484,460)/($1,438,030) = 5.20
Program Service Ratio
(Program Expenses/Total Expenses)
($7,447,627)/($9,381,598) = 0.79
Example: 2
Ten Point Test for Overland Park
Ratio
Computation
Computation for Overland Park, KS
Liquidity
Short-Run Financial Position
(Unassigned General Fund Balance)/
(General Fund Revenues)
($41,202,961)/($142,624,791) = 0.29 = 29%
Liquidity
(General Fund Cash + General Fund Investments)/
(General Fund Liabilities)
($60,313,574)/($7,064,270) = 8.54 = 854%
Profitability
Net Asset Growth
Change in Governmental Activities Net Position /Beginning Governmental Activities Net Position
$14,616,936/$960,524,629 = 0.02 = 2%
Operating Margin
(Net (Expense) Revenue for Governmental Activities/Total Governmental Activities Expenses) X -1
(-$125,846,359/$205,896,739)(-1) = 0.61 = 61%
Own-Source Revenues
Primary Government Operating Grants / (Total Primary Government Revenues)
$14,115,047/($60,993,230+$14,115,057+$34,752,883+$143,728,996) = 0.06 = 6%
Solvency
Near-Term Solvency
(Primary Government Liabilities)/
(Total Primary Government Revenues)
$284,967,097/($60,993,230+$14,115,057+$34,752,883+$143,728,996) = 1.12 = 112%
Debt Burden
(Primary Government Non-Current Liabilities)/
Population
($284,967,097)/(187,730) = $1,518
Coverage 1
(Governmental Funds Principal and Interest on Long-Term Debt/General Fund Expenditures)
$22,596,369/$101,752,631 = 0.22 = 22%
Coverage 2
Enterprise Funds Operating Revenue/
Enterprise Funds Interest Expense
$30,081,558/$5,797,658 = 5.19
Capital Asset Condition
(Ending Net Value of Primary Government Capital Assets – Beginning Net Value) / (Beginning Net Value)
($871,940,863-$863,435,252)/($863,435,252) = .0098 = 0.98%
Comparative Benchmarks
Overland Park Ten Point Test Score
Ratio
Ratio Computed
Score
Liquidity
Short-Run Financial Position
29%
+2
Liquidity
854%
+2
Profitability
Net Asset Growth
2%
0
Operating Margin
61%
-1
Own-Source Revenues
6%
+1
Solvency
Near-Term Solvency
112%
+2
Debt Burden
$1,518
0
Coverage 1
22%
-1
Coverage 2
5.19
+2
Capital Asset Condition
.98%
0
Total
7
