Financial statement for the Healthy

    Prepare a financial statement for the Healthy You program using Excel. (spreadsheet in the picture on excel) Your final excel spreadsheet that you submit must use formulas to add/subtract column totals. You also need to justifying your budget decisions. This can be a separate word document or written on the excel spreadsheet. You can complete this activity by yourself or with a partner. Scenario: You work at a health insurance company and have been asked to oversee one program which is an app designed to improve the health of their clients. Your supervisor gave you last years budget information and asked you to create a financial statement demonstrating the budget for the program for the upcoming calendar year. Each item below in the start with the basics section should be a row on your financial statement. The cost of supplies and other employee expenses (benefits, overhead, training, etc) are included the companys budget and dont need to be included on your financial statement. Start with the basics: These expenses and income are projected to stay the same for the upcoming year Expenses: Ten employees work on various parts of the app (technical support, building out new components, marketing, etc). The average salary last year was $49,200 per person. Income: Clients (organizations) who purchase health insurance from your company automatically pay a fee to cover the cost of the app for each of their employees. This equates to $5/month. Fees coming in monthly are for 7,000 employees. This program received a grant from the Department of Health & Human Services for $108,000 for the upcoming year. New items you want or need to add for the upcoming year: You recognize the staff has worked hard over the last year. You would like to offer them all a 3% raise. You would like to have an employee appreciation lunch to show your appreciation. Estimated cost is $30 per person The program could use an administrative assistant to help with logistics. The annual salary is $35,000. The administrative assistant would be at the lunch but not included in the raises (if you offer either) As a for-profit organization, you have to have this project end in the black (net has to be a positive number). What do you do? Explain/justify your budget decisions.
  • Administrative Assistant Salary
  • Total Expenses (using a formula to sum all expense line items)

Net Income (Loss):

  • Net Income (Loss) (using a formula to subtract Total Expenses from Total Income)

II. Calculations:

Here's how you'll calculate the amounts for each line item:

Income:

  • Client Fees: $5/month/employee * 7,000 employees * 12 months
  • Grant Income: $108,000 (as provided)
  • Total Income: Sum of Client Fees and Grant Income

Expenses:

  • Employee Salaries (Current Year): $49,200/employee * 10 employees
  • Salary Increase (3%): Employee Salaries (Current Year) * 0.03
  • Employee Salaries (Total - Including Increase): Employee Salaries (Current Year) + Salary Increase
  • Employee Appreciation Lunch: $30/person * (10 employees + 1 administrative assistant)
  • Administrative Assistant Salary: $35,000 (as provided)
  • Total Expenses: Sum of Employee Salaries (Total - Including Increase), Employee Appreciation Lunch, and Administrative Assistant Salary

Net Income (Loss):

  • Net Income (Loss): Total Income - Total Expenses

III. Justification of Budget Decisions:

Now, let's discuss and justify the budget decisions, keeping in mind the requirement to end in the black (positive net income).

Initial Calculation (Without Adjustments):

First, let's calculate the initial Net Income (Loss) based on the requested additions:

Income:

  • Client Fees: $5 * 7,000 * 12 = $420,000
  • Grant Income: $108,000
  • Total Income: $528,000

Expenses:

  • Employee Salaries (Current Year): $49,200 * 10 = $492,000
  • Salary Increase (3%): $492,000 * 0.03 = $14,760
  • Employee Salaries (Total - Including Increase): $492,000 + $14,760 = $506,760
  • Employee Appreciation Lunch: $30 * (10 + 1) = $330
  • Administrative Assistant Salary: $35,000
  • Total Expenses: $506,760 + $330 + $35,000 = $542,090

Net Income (Loss): $528,000 - $542,090 = -$14,090

As you can see, the initial calculation results in a loss of $14,090. To ensure the project ends in the black, we need to make some adjustments.

Budget Decisions and Justification:

  1. Addressing the Negative Net Income: To achieve a positive net income, we need to either increase income or decrease expenses. Given the constraints of the scenario, increasing the client fee or the number of employees covered might not be immediately feasible without further information or approval. Therefore, focusing on expense adjustments is the more controllable immediate strategy.

  2. Re-evaluating the Salary Increase: While recognizing the hard work of the staff and wanting to offer a raise is important for morale and retention, the current budget cannot fully support the 3% increase and the addition of an administrative assistant while staying in the black. A possible approach would be to:

    • Reduce the raise percentage: Consider a smaller raise, such as 1% or 2%, to partially acknowledge their efforts while minimizing the financial impact. A 1% raise would cost $4,920, and a 2% raise would cost $9,840, both significantly less than $14,760.
    • Phase in the raise: Implement the full 3% raise later in the year if financial performance allows.
    • Offer a one-time bonus instead: A one-time bonus could be a more flexible way to reward employees without increasing the permanent salary expense. The cost of a bonus could be adjusted to fit within the budget.

  3. Justifying the Administrative Assistant: The addition of an administrative assistant at a salary of $35,000 is a significant expense. The justification for this role needs to be strong. If the logistics and administrative burden on the ten existing employees are significantly impacting their core responsibilities (technical support, development, marketing), then the administrative assistant could lead to increased efficiency and productivity, potentially justifying the cost in the long run. However, if the administrative tasks are manageable by the current team, this might be an area to reconsider or delay.

  4. Maintaining the Employee Appreciation Lunch: The employee appreciation lunch is a relatively small expense ($330) compared to the overall budget. The positive impact on morale and team building could outweigh this cost, especially if the salary increase is reduced or a bonus is offered instead. It's a tangible way to show appreciation.

Revised Budget Decisions to Achieve a Positive Net Income (Example):

Let's explore an example where we reduce the salary increase to 1% and keep the administrative assistant:

Revised Expenses:

  • Employee Salaries (Current Year): $492,000
  • Salary Increase (1%): $492,000 * 0.01 = $4,920
  • Employee Salaries (Total - Including Increase): $492,000 + $4,920 = $496,920
  • Employee Appreciation Lunch: $330
  • Administrative Assistant Salary: $35,000
  • Total Expenses (Revised): $496,920 + $330 + $35,000 = $532,250

Revised Net Income (Loss): $528,000 - $532,250 = -$4,250

We are closer to being in the black, but still slightly negative. To get into positive territory, we could:

  • Further reduce the raise: Consider no permanent raise this year and focus on a bonus if possible.
  • Re-evaluate the immediate need for the administrative assistant: Delay hiring if the current team can manage the workload in the short term.
  • Explore small reductions in other operational costs (if any exist and are not included).

Final Recommended Budget Decisions (Example - Aiming for Positive Net Income):

Let's assume we decide to postpone the administrative assistant hire for the first half of the year (saving $17,500 initially) and offer a smaller 1% raise with a commitment to review again mid-year.

Final Expenses (Example):

  • Employee Salaries (Current Year): $492,000
  • Salary Increase (1%): $4,920
  • Employee Salaries (Total - Including Increase): $496,920
  • Employee Appreciation Lunch: $330
  • Administrative Assistant Salary (First Half - $0): $0
  • Total Expenses (First Half): $497,250

Net Income (First Half): ($420,000 / 2 + $108,000 / 2) - $497,250 = $214,000 - $497,250 = -$283,250 (This is not accurate as income is monthly)

Let's recalculate monthly:

Monthly Income: $420,000 / 12 + $108,000 / 12 = $35,000 + $9,000 = $44,000

Monthly Expenses (without Admin Assistant for first 6 months): $496,920 / 12 + $330 / 12 = $41,410 + $27.50 = $41,437.50

Net Income (Monthly - First 6 Months): $44,000 - $41,437.50 = $2,562.50

Total Net Income (First 6 Months): $2,562.50 * 6 = $15,375

Now, adding the administrative assistant for the remaining 6 months:

Monthly Expenses (with Admin Assistant): ($496,920 + $35,000) / 12 + $27.50 = $531,920 / 12 + $27.50 = $44,326.67 + $27.50 = $44,354.17

Net Income (Monthly - Last 6 Months): $44,000 - $44,354.17 = -$354.17

Total Net Income (Last 6 Months): -$354.17 * 6 = -$2,125.02

Total Net Income (Full Year - Example): $15,375 - $2,125.02 = $13,249.98

This example demonstrates how phasing in the administrative assistant hire and offering a smaller initial raise can lead to a positive net income. The justification here is a phased approach to manage costs while still addressing employee needs and program requirements.

Final Justification Summary for the Presentation:

The initial budget projections, including a 3% raise for existing staff and the addition of a full-time administrative assistant, resulted in a negative net income. To ensure the Healthy You program operates in the black, as required for a for-profit organization, the following budget adjustments are recommended:

  • Phased Implementation of Staff Increases: While recognizing the value of employee contributions and the need for administrative support, a phased approach to increasing staff costs is financially prudent. We propose offering a smaller initial raise (e.g., 1%) with a commitment to re-evaluate based on program performance mid-year. The hiring of the administrative assistant could be delayed or implemented on a part-time basis initially, depending on the immediate logistical needs.
  • Prioritizing Employee Appreciation: The employee appreciation lunch is a relatively low-cost investment with potentially high returns in terms of morale and team cohesion. This should be maintained as a tangible way to acknowledge the staff's hard work.
  • Data-Driven Reassessment: The financial performance of the program should be closely monitored throughout the year. If income exceeds projections or if efficiencies can be found in other areas, the possibility of increasing the staff raises or accelerating the administrative support can be revisited.

This approach balances the desire to reward employees and enhance program operations with the fiscal responsibility of ensuring the program's financial viability. By implementing these measures, the Healthy You program can achieve a positive net income while strategically investing in its team and infrastructure.

Financial Statement Structure (for Excel)

Your Excel spreadsheet will have the following columns:

  • Line Item
  • Amount

Here are the rows you'll include, based on the information provided:

Income:

  • Client Fees
  • Grant Income
  • Total Income (using a formula to sum Client Fees and Grant Income)

Expenses:

  • Employee Salaries (Current Year)
  • Salary Increase (3%)
  • Employee Salaries (Total - Including Increase)
  • Employee Appreciation Lunch

Aced Essays

Free Resources