You have been asked to take prior year financial statements and prepare next year’s budget by factoring in some changes expected in the next year, including lost revenue, opportunities for additional revenue, etc… To make the budget balance you will have to cut expenses in a thoughtful and strategic manner. My instructions will step you through three Excel worksheets starting with the prior year financial statements and ending with next year’s budget.
Use these Excel workbook “Worksheets for Chapter 8 Case Study” to complete this assignment. Look at the tabs at the bottom of the workbook for each of the three worksheets you need to complete.
Step-by-step instructions:
Worksheet 8.3 (prior year statement of activities by account categories):
Use the June 30, 2011 column of the trial balance under Table 8.14 on pages 177-8 to complete this worksheet. You’ll need to add together accounts for each category. For example, Government Grants = Acct. 4101 through 4103.
Enter amounts in thousands. For example, $79,451.25 will be $79.
Check Figures: Revenue less expenses = $35.
Worksheet 8.4 (prior year statement of activities allocated out to programs and support categories):
Using the trial balance from Table 8.14, allocate each line over its programs and support subcategories.
For example, Government Grants will be accounted 4101 through 4103, but only 4101 and 4102 go under the HOPE column. 4103 goes under CARE.
Add together the two subcategories (program and support) column totals to reach the Total column amounts at the far right. This column will tie to the 2011/10 revenues and expenses in Worksheet 8.3.
Check Figures: “Hope” revenue less expenses = $106, the “Support” subtotal revenue less expenses = $(151) and the Total Column revenue – expenses = $35.
Worksheet 8.5 (upcoming year statement of activities budget = prior year adjusted for changes in the upcoming year):
Input all the numbers from Worksheet 8.4.
As you make the following changes, take notes on what you are changing and why. This will help with the recommendations section at the end!
Then adjust (reduce) the revenues for the changes listed in instruction #5 at the bottom of page 180. These changes affect the HOPE program and Admin. support.
Then adjust the revenues in the CARE program to maximize the revenue possibilities listed in Table 8.16 on page 182.
Consider other realistic possibilities for increasing revenues through special events, etc… and reflect them in the worksheet.
Based on these changes, adjust the expenses in a reasonable manner to reflect the above changes in revenue.
You need to adjust the expenses in a thoughtful and strategic manner to get to a breakeven ($0) “revenues less expenses” in the Total column.
Recommendations will describe how you overcome the lost revenue. This is worth almost of a third of the assignment, so don’t forget this!
How were revenues decreased?
What other revenue sources did you plan to increase and how?
What expenses did you decide to adjust and what reasoning do you have for these changes?
Sample Solution
The post Prior year financial statements appeared first on homework handlers.
"Looking for a Similar Assignment? Get Expert Help at an Amazing Discount!"
