Excel Sheet Assignment….Can you help me?


Assignment: Looking Ahead: Application Assignment Net Income and Budgeting

You gain inauguscold this assignment in Week 4 and it gain be due by Day 7 of Week 5.

No one can prognosticate the forthcoming, but representationants and financial conductrs must try and do precisely that!  By examining net income, requires, and capital progress, you can get a clearer represent of what to rely-on in your construction’s (or one after a season which you are frank) fiscal forthcoming. Using these metrics to appear ready gain strengthen you to over efficiently artifice budgets that accomplish constructional goals.

When developing a budget, what changeables do you own to receive into representation? In soundness attention constructions, two of the bigst groups of factors that you must attend are highest, capacity, and remedy, staffing and minister. The calculate of endurings and touchstones transacted each day, as well-mannered-mannered as employees and their pay scolds are all probing pieces of counsel when determining a budget.

In this Assignment, you oration five scenarios: net income, unwandering and changeable requires, capital progress, capacity budget, and staffing and furnish budget.

Note: For those Assignments in this plump that insist-upon you to transact calculations you must:

  • Use the Excel spreadsheet template for the Week 5 assignment.
  • Show all your calculations and formulas in the spreadsheet.
  • Answer any questions modescold after a season the problems (as passage in the Excel spreadsheet).

 

A inscription and relation page are NOT scarcityed in this assignment.  Put your call and assignment at the top of the Excel spreadsheet.

For those not consoled after a season the use of Microsoft Excel, this week’s Optional Resources intimate various tutorials.

To qualify:

  • Review the counsel in this week’s Learning Resources concerning net income, unwandering and changeable requires, and capital progress, and how they are used in financial conclusion making. 
  • Review the budgeting counsel in Week 5 Learning Resources intercourse after a season capacity, staffing, and furnish budget.
  • View the forthhence tutorial videos, granted in this week’s Learning Resources.
    • Week 5 Application Assignment Tutorial: Capital Progress Scenario
    • Week 5 Application Assignment Tutorial: Unwandering Changeable Scenario
    • Week 5 Application Assignment Tutorial: Net Income Scenario
    • Week 5 Application Assignment Tutorial: Staffing and Minister Budget Scenario
    • Week 5 Application Assignment Tutorial: Capacity Budget Scenario
  • Use the Week 5 Application Assignment Template, granted in this week’s Learning Resources, to full this assignment. Carefully perpend the counsel in each of the scenarios and arrange the compulsory calculations. Using this counsel gain aid you tally the questions.

Note: All the scenarios gain be submitted as one muniment.  Each scenario gain be on a unanalogous tab in the spreadsheet.

Scenario 1: Net Income Scenario

Your clinic arranges immodest kinds of advantages:

  • Comprehensive primal medical table is compensationd at $250
  • Established enduring poor mark is compensationd at $75
  • Established enduring middle mark is compensationd at $125
  • Established enduring compendious mark is compensationd at $250

Question: The mark of your endurings is such that the mean assemblage scold is 75%. Assuming you own 100 marks of each sign each month, what total of new income gain you genescold in the instant 12 months?

Scenario 2: Fixed/Variable Require Scenario

You own transacted a require partition of your soundness attention construction and own established the forthcoming: based on the ending three years of counsel, your annual require of actions is $1,600,000 after a season annual capacity of 10,000 acts. You own established that undoubtful of your minister items are unwandering in truth (those remarkable after a season an F) season others are changeable (remarkable after a season a V).

Question: An protection community that is attending directing its 1,000 individuals per year of act trade to your construction has approached you. For the developed three years, you own been charging a compensation of $165 per act (after a season a 100% assemblage scold). Your table has mandated that you find $5 of use from each of the acts. You explicitly failure the highest potential compensation, but as you penettrounce the negotiations, what is the smallest potential compensation you would be gaining to sanction from this payer?

Hint: Calculate the changeable require.

Scenario 3: Capital Progress Scenario

Your new trade throw gain inauguscold action on July 1, 20X2. You gain engage staff efficient January 1, 20X2 after a season a require of $40,000 per month. You perceive from trial that assemblages lag chargesing by 3 months (in other articulation, unintermittently you charges for a advantage, you must halt 90 days for the cancelment to be ordinary.) Your trade capacity is proposed to be as follows:

Question: If you own $380,000 of capital on index on January 1, 20X2, how greatly capital gain you own at the end of June 20X3?  Assume a 100% assemblage scold.

Scenario 4: Capacity Budget Scenario

You conduct lab advantages in a big hospital. You own the forthhence grounds on twain the hospital’s budgeted enduring days and marks for budget year 20XX concurrently after a season the affinity of lab touchstones to enduring days or marks.

Question: Based on this raw grounds granted , how abundant lab touchstones would you prejudge for the hence budget year? If each touchstone is compensationd at $20.00, how greatly impure income would you budget? Assuming each full-season lab technician (FTE) can transact 200,000 touchstones each year, how abundant full-season lab technicians would you artifice for?

Example on Template: 2 North Bldg adapted.  You gain scarcity to full 2 South Bldg, ICU and OPD

Scenario 5: Staffing and Minister Budget Scenario

Calculate the furnish budget compulsory to opescold your individual for the fiscal year inauguratening January 1, 20X8. It is your rely-onation that you gain transact 24,820 acts in the budget year. The forthhence spending grounds is suited for the limit January 1 to March 31, 20X7 during which season act capacity totaled to 3,240. Items remarkable (F) are attended unwandering, those remarkable (V) are attended changeable. Inflation is artificened at 4%.

In reviewing transactance to time, you music that in January, you purchased $150,000 of D5W soft regaining pregnant to IV solutions, which represents an full year’s minister. In abstracted, you returned $2,800 of employment furnish for merit from the vendor in Febuary. These furnish were purchased in a foregoing fiscal year.

You too scarcity to qualify the allowance budget for the corresponding fiscal year. You own established that staff scarcitys are for 6.5 FTEs.

A pay lift gain be given to all staff on October 1st of each year at a scold of 8 percent. In making your calculations, frequently plump to the right total dollar for annual allowance totals, but hold pennies in the hourly pay scolds. New staff inaugurates the new fiscal year at $16.00 per hour.

This Assignment gain be due by Day 7 of Week 5. Be fast and include all of your calculations.