[SOLVED] Assignment: W8A5 Healthcare Budget Request – Ratio Analysis

Assignment: W8A5 Healthcare Budget Request – Ratio Analysis

Financial ratios are often used to show performance of an organization. Ratio analysis provides ways to compare past performance with current performance or to compare to similar organizations. Ratio analysis often considers variable and fixed costs.

Variable and Fixed Costs

Variable cost is an expense that changes with volume, or production. Consider if the cost of performing a service is $100 and you perform 5, the total variable cost is $500 (=$100*5). Yet, if the demand increased, or decreased, the total would change.

Conversely, fixed cost do not change with volume. An example of fixed cost is rent. Although fixed cost may change over time, it is not related to the production number.

Breakeven Analysis

This analysis is based on units of service and tells the level of production needed to cover the costs of production. The break-even point is calculated by dividing the total fixed costs of production by the price of a product per individual unit less the variable costs of production. Remember, Breakeven analysis is based on Units of service, not revenue.

Marginal Profit and Loss

Marginal cost of production is the change in total production cost that comes from making one more unit. Fixed cost do not change with volume, so the fixed costs are spread over more units leading to a decreased fixed cost per unit. Therefore, increasing production decreases the cost. Variable costs do change with volume, so producing more units will add more variable costs.

Assignment: W8A5 Healthcare Budget Request – Ratio Analysis

Complete the Ratio Analysis Worksheets

For this Assignment, you will practice the following financial analyses: Expense Forecasting, Breakeven Analysis, and Marginal Profit and Loss.  To do this:

• Review the Learning Resources.
• Complete the 3 Ratio Analysis Workbooks labeled W8A5a, b, c:

1. W8A5a Expense Forecasting
   1. Read and transfer the information (data) provided into the correct cell.
   2. Enter a formula to create totals.

• Enter a formula to calculate Annualized Amounts.
  1. Fixed costs are given for the Year to Date (6 months) and you are breaking that down to a monthly cost, then multiplying times 12 months to reach Annualized Amounts.

2. Variable costs are based on volume, therefore a formula that takes the total variable costs for the Year to date (6months) and divides it by the volume (units) completed in the same time period, to arrive a unit price that is then multiplied times total volume for 12 months.
3. Add back “one time expense or credits” if there are any.
4. Add back the amount budgeted for two months of the two new technicians cost.
5. Complete the totals.

2. W8A5b Break Even Analysis
   1. Read and transfer data into the correct cell.
   2. Enter a formula to calculate Average Collection per Service in B10

• Enter a formula to calculate Break-even Point in B15

1. Answer the question in the text box provided:

3. W8A5c Marginal Profit and Loss
   5. Line 14: Enter Units of Volume in Year 1, then enter formulas to calculate totals for year 2-5.
   6. Line 15: Enter Price Procedure from data given.

• Line 16: Enter Collection Rate from data given.

1. Line 17: Enter a formula to calculate Marginal Net Revenue for each year.
2. Line 20: Enter a formula for Units of Volume
3. Line 21: Enter the year one cost of Supplies per unit/procedure then enter a formula increasing the amount each year thereafter by 5%.

• Line 22: Enter a formula to calculate Marginal Variable Cost

• Lines 24:28- Complete these fixed Costs in the same manner as used for Variable cost.

1. Line 29: Total Marginal cost:

1. Line 30: Enter a formula to Calculate Annual Marginal Profit:

1. Calculate Cumulative Profit Margin.

1. B31- Enter the same amount as Year One Annual Marginal Profit.
2. C31-F31- Enter a formula to calculate Cumulative Profit Margin for each year:

*Answer the question below in the text box provided on the spreadsheet.

3. Save the workbook as W8A5_lastname_firstname.xlsx and submit the workbook to the appropriate Assignment 5 submission link by the due date specified in the classroom.

Note: You have completed Assignment 5.

Assignment: W10/11A6 Final Healthcare Budget Request