Accounting/Excel
Assignment:
It is September 1, 2011. The executive director of the nonprofit organization Youth Movement has just resigned to accept a position with another youth-serving organization. You are the director of programs for Youth Movement and have been named interim acting executive director. In June 2011, the prior executive director did not present a FY 2011/12 (July 1, 2011–June 30, 2012) proposed bud- get for approval by the board of directors because he had recently become aware of the following financial problems for FY 2011/12:
■■ The federal grant for the HOPE program was going to be reduced by 50 percent.
■■ The corporate grant for general support (unrestricted) was going to be eliminated.
■■ The individual contribution for general support (unrestricted) was going to be eliminated.
Given these large financial problems, the board of directors told the executive director that he could take “a few months” to determine how he would propose a balanced budget with cost reductions and/or revenue increases. He never completed the task. The board of directors has now instructed you to present a FY 2011/12 balanced budget for approval by the board no later than September 30, 2011.
Details:
Using YM-BS-IS tab on the excel spreadsheet.
The gist of this part is you will need to mildly reconstruct two years of financial statements, analyze them with ratio calculations and then break down the financials into budgets for the two programs and the support services section and make changes to the budgets to react to grant funds that have been cut.
I’ve attached the final template file to this post. Complete tabs- Ym8.1 through YM8.4A
Tab 1 has information to complete other tabs.
NOTE: To make things easier to work with, round all numbers to the nearest $1,000, dropping the zeros (i.e., all numbers expressed should be in thousands of dollars. $123,115 would be rounded down and entered as $123).
Tab 2 is Template 8.1, where you will calculate the Unrestricted balance sheet balances using the financial statement and the information in Table 8.15 in the book that describes the Temporarily Restricted Net Assets in each year. You’ll subtract the temp restricted amounts from the Total amounts under each year in the balance sheet (first tab).
Tab 3 is Template 8.2, where you will recap just the unrestricted amounts from the Trial Balance information on page 178. Compare to the Statement of Activities totals in the first tab.
Tab 4 is Template 8.3, where you recap 8.1 and 8.2 onto a single page, and calculate the ratios as we did in week 4.
Tab 5, Template 8.4, you’ll take the the information from Tab 4 plus information from the Trial Balance (8.14)on page 177-178(below) and Table 8.16 on page 183 (below)to fill in this worksheet.
