Question
Part 1. Utilize Table 1 and Table 2 to answer the Part 1 questions. 1. Illuminations consultants have prepared a list of financial ratios (numbers)
Part 1. Utilize Table 1 and Table 2 to answer the Part 1 questions. 1. Illuminations consultants have prepared a list of financial ratios (numbers) for your analyses. For each of the ratios (numbers), first identify which of the following category each ratio (number) falls under: A) Liquidity, B) Solvency, C) Profitability, or D) Efficiency. Then use the formula given below and calculate these ratios from 2006 to 2018 in Microsoft Excel. Make sure to use the cell referencing feature in Excel. Administration Expense Ratio =Administration Expenses/Total Expenses Number indicates the percentage of total expenses that cover administrative expenses.
Current Ratio =(Cash+Marketable Securities+Receivables+Inventory)/(Accounts Payables+Grants Payables+Deferred Revenues) Number indicates whether the organization has sufficient current assets to meet its short-term obligations.
Debt to Asset Ratio =Total Liabilities/Total Assets Number indicates the extent to which the organization's assets are financed with debt
Debt to Net Asset Ratio =Total Liabilities/Total Unrestricted Net Assets Total debt in relation to unrestricted net assets. Number indicates percentage of unrestricted net assets that are being financed with debt.
Defensive Interval =(Cash+Marketable Securities+Receivables)/((Expenses-Depreciation)/12) Number indicates the number of months the organization could operate using cash, marketable securities, and receivables on hand at year end.
Liquid Funds Amount =Unrestricted Net Assets-Net Fixed Asset+Long-Term Debt Amount of unrestricted liquidity remaining after fixed assets and related debt are removed.
Liquid Funds Indicator =[(Total Net Assets-Permanently Restricted Net Assets-Fixed Assets)*12]/Total Expenses Number of months that an organization can operate based on net assets (reserves) that do not include fixed assets or endowments.
Program Service Ratio =Program Service Expenses/Total Expenses Number indicates the percentage of total expenses that go directly into program services.
Quick Ratio =(Cash+Marketable Securities+Receivables)/(Accounts Payables+Grants Payables+Deferred Revenues) Number indicates whether the organization has sufficient liquid current assets to meet its short-term obligations. It is a more stringent measure than current ratio.
Return on Investment =Investment Income/Average Investments Number indicates the gain or loss generated on an investment relative to the amount of money invested.
Savings Indicator =(Revenues-Expenses)/Revenues Number is the income (loss) as a percentage of total revenue. Positive number indicates income and negative number indicates loss.
Times Interest Earned Ratio =(Increases or Decreases in Net Assets + Interest Expenses)/Interest Expenses Number indicates whether the organization has sufficient increases in net assets to pay the current year's interest expenses.
Table 1 Statement of Financial Position Data for Walden Conservatory of Music, Inc. (Based on Form 990s) 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 $43,354 $1,852,392 $37,447 $1,296,880 $21,921 $1,166,252 $20,114 $739,518 $7,353 $474,555 $9,538 $534,532 $14,958 $486,320 $36,735 $220,779 $27,147 $101,352 $37,346 $117.716 $46,383 $60,578 $119,529 $69,705 $56,558 $46,596 $37,601 $3,624 $5,000 $19,222 $8,623 $90,632 $66,204 $143,805 $113,850 $233,147 $356,642 $261,600 $523,734 Assets Current Assets Cash & Equivalent (Investible Assets) Accounts Receivable Pledges & Grants Receivable Inventories for Sale or Use Noncurrent Assets Investment / Securities Fixed Assets (LBE - Depreciation) Other Total Assets SO $922,871 $0 $654,050 $8.274 $761.395 $0 $793,003 $0 $829,564 $223,566 $735,941 $787,237 $879,548 $920,183 $829,994 $911,385 $833,327 $4,178,804 $5,413 $4,164,357 $13,219 $6,235,942 $4.080,721 $6,254 $6,065,578 $3,963,259 $22,701 $5,631,601 $3.857.341 $13,771 $5,284,011 $3,715,742 $56,404 $5,242,262 $3,651,965 $3,920 $5,156,529 $3,626,634 $39,403 $4.980,639 $3,549,845 $11,178 $4,467,621 $3,420,787 $11,315 $4,599,017 $3,298,415 $26,139 $4,564,149 $3,171,353 $17,640 $4,460,264 $3,037,204 $16,050 $4,536,578 $6,335,223 $26,352 Liabilities Current Liabilities Accounts Payable Deferred Revenue Long Term Liabilities Loans and Notes Total Liabilities $27,213 $179,443 $50,645 $216,650 $60,317 $193,082 $98,921 $183,803 $105,055 $199,766 $38.990 $227,587 $84,909 $180,270 $78,646 $213,936 $28,374 $130,459 $25,732 $138,765 $29,049 $160,018 $18,779 $188,765 $135,126 $208,000 $40,000 $344,821 $195,000 $461,577 $350,000 $642,582 $400,000 $561,478 $505,000 $663,833 $540,000 $704,497 $625,000 $814,067 $595,000 $802,544 $206,656 $267,295 $253,399 $282,724 $473,179 $651,146 $814,779 Net Assets With Donor Restrictions - Temporary in Nature $642,111 $539,389 $470,677 $476,219 $356,585 $434,381 $418,059 $281,055 $269,217 $408.909 $522,254 $435,127 With Donor Restrictions - Permanent in Nature $780,243 $735,941 $787,237 $879,548 $920,183 $829.994 $911,385 $922,871 $654,551 $708.392 $750,347 $770,291 Without Donor Restrictions $4,706,213 $4,693,317 $4,554,265 $3,993,110 $3,662,422 $3,516,310 $3,353,906 $3,134,131 $2,982,375 $2,817,883 $2,587,051 $2,440,779 Total Net Assets $6,128,567 $5,968,647 $5,812,179 $5,348,877 $4.939,190 $4.780,685 $4,683,350 $4,338,057 $3,906,143 $3,935,184 $3,859,652 $3,646,197 Total Liabilities and Net Assets $6,335,223 $6,235,942 $6,065,578 $5,631,601 $5,284,011 $5,242,262 $5,156,529 $4.980.639 $4,467,621 $4,599,017 $4,564,149 $4,460,264 Note: The ending net assets will not equal the beginning net assets plus the year surplus/deficit do to items not included in the statement of activities of the form 990 (ex: unrealized gains and losses on investments). $2,268,109 $3,734,034 $4,536,578 Table 2 Statement of Activities Data for Walden Conservatory of Music, Inc. (Based on Form 990s) 2006 Revenue Contributions Program Services Investments Other Total Revenue 2007 $656,596 $646,132 $1,336,945 $1,365,461 $37,736 $26,009 $42,419 $63,438 $2,073,696 $2,101,040 2008 2009 $578,881 $520,828 $1,177,015 $1,292,539 $18.185 $24,968 $62,213 $71,743 $1,836,294 $1,910,078 2010 2011 2012 2013 2014 2015 2016 2017 $728,632 $799,115 $788,301 $655,948 $901,399 $786,478 $550,828 $870,619 $1,404,974 $1,481,789 $1,517,152 $1,649,773 $1,524,579 $1,442,949 $1,551,586 $1,509,723 $35,251 $87,660 $9,110 $31,055 $23.927 $43,637 $30,496 $53.847 $113,348 $178,059 $81,759 $183,791 $79,582 SO $0 SO $2,282,205 $2,546,623 $2,396,322 $2,520,567 $2,529,487 $2,273,064 $2,132,910 $2,434,189 2018 611,244 1,523,123 69,650 0 $2.204,017 Expenses Program Services Administration Fundraising/Other Total Expenses $1,830,850 $1,968,252 $195,787 $223.369 $158,379 $90,983 $2,185,016 $2,282,604 $1,871,336 $2,001,023 $2,018,289 $2,210,548 $340,471 $255,625 $284,143 $289,413 $93,105 $101,288 $144,471 $143.997 $2,304,912 $2,357,936 $2,446,903 $2,643,958 $2,247,694 $336,027 $157,894 $2,741,615 $2,424.489 $298,286 $197,963 $2,920,738 $1,976,119 $1,739,145 $1,851,467 $1,772,564 $365,667 $383,248 $404,445 $425,680 $180,988 $240,237 $125,940 $135,722 $2,522,774 $2,362,630 $2,381,852 $2,333,966 1,768,271 462,667 183,734 $2,414,672 ($468,618) ($447,858) ($164,698) ($97,335) ($345,293) ($400,171) $6,713 ($89,566) ($248,942) $100,223 ($210,655) Increase (or Decrease) in Net Assets ($111,320) ($181,564) Note: Some important functional expenses are included below. Functional Expenses 2006 2007 Interest Expense $0 $0 Depreciation Expense $164,144 $170,295 2008 $0 2009 $0 2010 $5,586 2011 $8,424 2012 $14,718 2013 $17,222 2014 $15,470 2015 $19,441 2016 $18,013 2017 $18,698 2018 $18,672 $169,253 $163,888 $163.138 $144,768 $145,613 $146,573 $165,447 $146,021 $146,187 $148,870 $143,123 Table 1 Statement of Financial Position Data for Walden Conservatory of Music, Inc. (Based on Form 990s) 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 $43,354 $1,852,392 $37,447 $1,296,880 $21,921 $1,166,252 $20,114 $739,518 $7,353 $474,555 $9,538 $534,532 $14,958 $486,320 $36,735 $220,779 $27,147 $101,352 $37,346 $117.716 $46,383 $60,578 $119,529 $69,705 $56,558 $46,596 $37,601 $3,624 $5,000 $19,222 $8,623 $90,632 $66,204 $143,805 $113,850 $233,147 $356,642 $261,600 $523,734 Assets Current Assets Cash & Equivalent (Investible Assets) Accounts Receivable Pledges & Grants Receivable Inventories for Sale or Use Noncurrent Assets Investment / Securities Fixed Assets (LBE - Depreciation) Other Total Assets SO $922,871 $0 $654,050 $8.274 $761.395 $0 $793,003 $0 $829,564 $223,566 $735,941 $787,237 $879,548 $920,183 $829,994 $911,385 $833,327 $4,178,804 $5,413 $4,164,357 $13,219 $6,235,942 $4.080,721 $6,254 $6,065,578 $3,963,259 $22,701 $5,631,601 $3.857.341 $13,771 $5,284,011 $3,715,742 $56,404 $5,242,262 $3,651,965 $3,920 $5,156,529 $3,626,634 $39,403 $4.980,639 $3,549,845 $11,178 $4,467,621 $3,420,787 $11,315 $4,599,017 $3,298,415 $26,139 $4,564,149 $3,171,353 $17,640 $4,460,264 $3,037,204 $16,050 $4,536,578 $6,335,223 $26,352 Liabilities Current Liabilities Accounts Payable Deferred Revenue Long Term Liabilities Loans and Notes Total Liabilities $27,213 $179,443 $50,645 $216,650 $60,317 $193,082 $98,921 $183,803 $105,055 $199,766 $38.990 $227,587 $84,909 $180,270 $78,646 $213,936 $28,374 $130,459 $25,732 $138,765 $29,049 $160,018 $18,779 $188,765 $135,126 $208,000 $40,000 $344,821 $195,000 $461,577 $350,000 $642,582 $400,000 $561,478 $505,000 $663,833 $540,000 $704,497 $625,000 $814,067 $595,000 $802,544 $206,656 $267,295 $253,399 $282,724 $473,179 $651,146 $814,779 Net Assets With Donor Restrictions - Temporary in Nature $642,111 $539,389 $470,677 $476,219 $356,585 $434,381 $418,059 $281,055 $269,217 $408.909 $522,254 $435,127 With Donor Restrictions - Permanent in Nature $780,243 $735,941 $787,237 $879,548 $920,183 $829.994 $911,385 $922,871 $654,551 $708.392 $750,347 $770,291 Without Donor Restrictions $4,706,213 $4,693,317 $4,554,265 $3,993,110 $3,662,422 $3,516,310 $3,353,906 $3,134,131 $2,982,375 $2,817,883 $2,587,051 $2,440,779 Total Net Assets $6,128,567 $5,968,647 $5,812,179 $5,348,877 $4.939,190 $4.780,685 $4,683,350 $4,338,057 $3,906,143 $3,935,184 $3,859,652 $3,646,197 Total Liabilities and Net Assets $6,335,223 $6,235,942 $6,065,578 $5,631,601 $5,284,011 $5,242,262 $5,156,529 $4.980.639 $4,467,621 $4,599,017 $4,564,149 $4,460,264 Note: The ending net assets will not equal the beginning net assets plus the year surplus/deficit do to items not included in the statement of activities of the form 990 (ex: unrealized gains and losses on investments). $2,268,109 $3,734,034 $4,536,578 Table 2 Statement of Activities Data for Walden Conservatory of Music, Inc. (Based on Form 990s) 2006 Revenue Contributions Program Services Investments Other Total Revenue 2007 $656,596 $646,132 $1,336,945 $1,365,461 $37,736 $26,009 $42,419 $63,438 $2,073,696 $2,101,040 2008 2009 $578,881 $520,828 $1,177,015 $1,292,539 $18.185 $24,968 $62,213 $71,743 $1,836,294 $1,910,078 2010 2011 2012 2013 2014 2015 2016 2017 $728,632 $799,115 $788,301 $655,948 $901,399 $786,478 $550,828 $870,619 $1,404,974 $1,481,789 $1,517,152 $1,649,773 $1,524,579 $1,442,949 $1,551,586 $1,509,723 $35,251 $87,660 $9,110 $31,055 $23.927 $43,637 $30,496 $53.847 $113,348 $178,059 $81,759 $183,791 $79,582 SO $0 SO $2,282,205 $2,546,623 $2,396,322 $2,520,567 $2,529,487 $2,273,064 $2,132,910 $2,434,189 2018 611,244 1,523,123 69,650 0 $2.204,017 Expenses Program Services Administration Fundraising/Other Total Expenses $1,830,850 $1,968,252 $195,787 $223.369 $158,379 $90,983 $2,185,016 $2,282,604 $1,871,336 $2,001,023 $2,018,289 $2,210,548 $340,471 $255,625 $284,143 $289,413 $93,105 $101,288 $144,471 $143.997 $2,304,912 $2,357,936 $2,446,903 $2,643,958 $2,247,694 $336,027 $157,894 $2,741,615 $2,424.489 $298,286 $197,963 $2,920,738 $1,976,119 $1,739,145 $1,851,467 $1,772,564 $365,667 $383,248 $404,445 $425,680 $180,988 $240,237 $125,940 $135,722 $2,522,774 $2,362,630 $2,381,852 $2,333,966 1,768,271 462,667 183,734 $2,414,672 ($468,618) ($447,858) ($164,698) ($97,335) ($345,293) ($400,171) $6,713 ($89,566) ($248,942) $100,223 ($210,655) Increase (or Decrease) in Net Assets ($111,320) ($181,564) Note: Some important functional expenses are included below. Functional Expenses 2006 2007 Interest Expense $0 $0 Depreciation Expense $164,144 $170,295 2008 $0 2009 $0 2010 $5,586 2011 $8,424 2012 $14,718 2013 $17,222 2014 $15,470 2015 $19,441 2016 $18,013 2017 $18,698 2018 $18,672 $169,253 $163,888 $163.138 $144,768 $145,613 $146,573 $165,447 $146,021 $146,187 $148,870 $143,123
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started