Expert answer:500 words summary and filling excel using the info
Solved by verified expert:Expense Forecasting and Benchmarking
Note: For those Assignments in this course that require you to perform
calculations you must:
• Use the Excel spreadsheet template for the Week 3 assignment
• Show all your calculations and formulas in the spreadsheet.
• Answer any questions included with the problems (as text in the Excel
spreadsheet).
To prepare:
• Review the information in the Week 9 and 10 Learning Resources dealing
with expense forecasting, profit and loss, break-even analysis, and
benefit and cost ratio analysis. Focus on how they are calculated and
how they can be used in decision making.
Carefully examine the information in each of the scenarios and provide
the necessary calculations. Using this information will help you answer
the questions. Note: All the scenarios will be submitted as one
document. Each scenario will be on a different tab in the spreadsheet.
Expense Forecasting
In this Application Assignment you calculate scenarios focusing on
benefit/cost ratio analysis, marginal profit and loss statements, and
break-even analysis. For these scenarios, you will utilize the provided
figures to perform calculations and then make recommendations about the
viability of the investment opportunities
Expense Forecasting Scenario
Your department has performed 20,000 procedures during the first six
months (January–June) of 20X1. Spending during that period of time was
$210,000 for fixed expense items and $1,200,000 for variable expense
items. Of those amounts, $50,000 of fixed expense money was spent on
preparing for a Joint Commission survey. Volume is anticipated to be 10%
higher in the second half of the year. On November 1st, two new
procedure technicians will begin work. The salary and fringe benefit
costs for each are $96,000/year. Based on the information provided,
prepare an expense forecast for 20X1.
Annualization for Fixed: (Adjusted Total for Year to Date Expense/6) *
12 =Total Annualized Amounts
Annualization for Variable (Adjusted Total for Year to Date Expense/
20,000) * 40,000 =Total Annualized Amounts.
Financial Analysis Cycle
Marginal Profit and Loss Statement Scenario
You are examining a proposal for a new business opportunity – a new
procedure for which demand is expected to be 1,400 units the first year,
growing by 600 units a year thereafter. The price charged per procedure
is $1,000. The collection rate is anticipated to be 80%. Each procedure
consumes $300 of supplies. Salary cost is estimated to cost $540,000
each year, fringe benefits are 25% of salaries, rent for the facility is
$55,000/yr and operating cost are $120,000/yr.
Questions:
1. Develop a marginal profit and loss statement for this business
opportunity.
Based on that analysis, should this opportunity be pursued?
Break-Even Analysis Scenario
You can charge $1,075 for a new service. Demand is anticipated to be
8,000 units a year. Your business is able to handle up to 16,500 units
annually, so capacity should not be a problem. The average collection
rate is 80%. The new service has annual fixed costs of $4,700,000.
Variable cost per unit of service is $420.
Question: Use break-even analysis to determine if this new service is
financially viable. If the business is not financially viable, what
steps could you take to make a case to proceed with implementation?
Explain your decision.
Benefit/Cost Ratio Analysis Scenario
You are considering the acquisition of a new piece of equipment with a
useful life of five years. This new technology will make your clinical
operation more efficient and allow for a reduction of 10 FTEs. The
equipment purchase price is $4,500,000 plus 10% installation fee. The
purchase price includes service for the first year, an item that has an
annual cost of $10,000. There is a potential for additional volume of
150,000 units in the first year, growing by 30,000 each year thereafter.
The price charged per unit is $15.00 with a 50% collection rate. The
staff being eliminated are paid $12.50 per hour. The fringe benefits
rate is 20%. The hurdle rate is 7.5%.
Questions: After reviewing Dr. Ward’s Video and the calculations below,
please answer the following questions:
• What is meant by benefit/cost ratio, average payback period and ROI
and why are the all-important to understand when purchasing new
equipment?
• Based on this information, would you pursue this opportunity?
• Explain your decision in 250-500 words in the text box below.
nursing_questions.xlsx
Unformatted Attachment Preview
Name
Assignment
Expense Forecasting
Based on the information provided, prepare an expense forecast for 20X1 using the template belo
Spending during January- June 20X1 (6 months)
Fixed expense items: $210,000
Variable expense items: $1,200,000
One time expense: $50,000 of fixed expense money was spent on preparing for a Joint Commiss
Procedures preformed during January- June 20X1 (6 months)
Your department has performed 20,000 procedures during the first six months
On November 1,20X1, two new procedure technicians will begin work. The salary and fringe benef
Description
Year to Date Expense
Adjustments
Add back “One Time” credits
Deduct “one Time” expenses
Adjusted total for year to date
expense
Annualization
Divide by months (fixed)
Multiple by months (fixed)
Divide by volume
Multiply by volume
Annualized Amounts
Adjustments
Add back “One Time” expenses
Deduct “One Time” credits
Expense two new technicians
Expense Forecast as of 12/31/X1
Calculations:
Fixed
6
12
Annualization for Fixed: (Adjusted Total for Year to Date Expense/6) * 12 =Total Annualized Amounts
Annualization for Variable (Adjusted Total for Year to Date Expense/ 20,000) * 40,000 =Total Annualize
expense forecast for 20X1 using the template below:
se money was spent on preparing for a Joint Commission survey
X1 (6 months)
ocedures during the first six months
nicians will begin work. The salary and fringe benefit costs for each is:
Variable
20,000
40,000
TOTAL
$96,000 yearly
Marginal Profit and Loss Statement Scenario
You are examining a proposal for a new business opportunity – a new procedure for which deman
is expected to be 1,400 units the first year, growing by 600 units each year thereafter. The price
charged per procedure is $1,000. The collection rate is anticipated to be 80%. Each procedure
consumes $300 of supplies. Salary cost is estimated to cost $540,000 each year, fringe benefits ar
25% of salaries, rent for the facility is $55,000/yr and operating cost are $120,000/yr.
Year One
Year Two
Year Three
Year Four
Marginal Revenue
Units of Volume
Price Procedure
Collection Rate
Marginal Net Revenue
Marginal Costs
Variable Costs
Units of Volume
Variable Cost Supplies
per Unit/procedure
Marginal Variable Cost
Fixed Costs
Salary Costs
Fringe Benefits
Rent
Operating Cost
Marginal Fixed Costs
Total Marginal Costs
Annual Marginal Profit
Cumulative Profit Margin
Question: Below is a marginal P&L for this business opportunity. Based on that analysis, should
this opportunity be pursued. Explain your decision.
Answer:
Answer:
edure for which demand
thereafter. The price
%. Each procedure
year, fringe benefits are
0,000/yr.
Year Five
that analysis, should
Break-Even Analysis Scenario
You can charge $1,075 for a new service. Demand is anticipated to be 8,000 units a year. Your bus
to 16,500 units annually, so capacity should not be a problem. The average collection rate is 80%.
fixed costs of $4,700,000. Variable cost per unit of service is $420.
Price to be Charged
Collection Rate
Average Collection per Service
Variable cost per unit of service
Fixed Operating Costs
Break-Even Point =
Fixed Cost/(Net Revenue per Unit-Variable Cost per Unit)
Capacity:
Demand:
Breakeven:
Question: Use break-even analysis to determine if this new service is financially viable. If the busi
viable, what steps could you take to make a case to proceed with implementation? Explain your d
Answer:
is anticipated to be 8,000 units a year. Your business is able to handle up
e a problem. The average collection rate is 80%. The new service has annual
service is $420.
this new service is financially viable. If the business is not financially
to proceed with implementation? Explain your decision.
Benefit/Cost Ratio Analysis Scenario
You are considering the acquisition of a new piece of equipment with a useful life of five years. Th
10 FTEs. The equipment purchase price is $4,500,000 plus 10% installation fee. The purchase pric
potential for additional volume of 150,000 units in the first year, growing by 30,000 each year there
eliminated are paid $12.50 per hour. The fringe benefits rate is 20%. The hurdle rate is 7.5%.
Question: After reviewing Dr. Ward’s Video and the calculations below, please answer the follow
why are the all important to understand when purchasing new equipment? Based on this informa
box below.
Investment Pre
Equipment
Construction
Year 0
Year 1
Year 2
Year 3
Year 4
Total
Installation
$
4,500,000 $
$
4,500,000 $
Benefit Present Value
Revenue Increases
Year 1
Year 2
Year 3
Year 4
Year 5
Total
1,125,000
1,350,000
1,575,000
1,800,000
2,025,000
7,875,000
Revenue
Decreases
Expense Decreases
312,000
312,000
312,000
312,000
312,000
1,560,000
Expense
Increases
10,000
10,000
10,000
10,000
40,000
Net Present Value
Benefit/Cost Ratio
Total Cash
Inflow
Average
annual cash
inflow
Average
payback
period (in
Years)
Return on
investment
=
=
=
=
=
Answer:
2,522,055
1.51
9,395,000
1,879,000
2.6
Average Annual Return /
Average Investment
( Total Benefit / Total Years ) /
(Investment / 2)
( $9,395,000 / 5 ) / (
$4,950,000 / 2 )
$1,879,000 / $2,470,000
76%
ful life of five years. This new technology will make your clinical operation more efficient and allow for a red
fee. The purchase price includes service for the first year, an item that has an annual cost of $10,000. There
30,000 each year thereafter. The price charged per unit is $15.00 with a 50% collection rate. The staff being
rdle rate is 7.5%.
ease answer the following questions: 1. What is meant by benefit/cost ratio, average payback period and R
Based on this information, would you pursue this opportunity? Explain your decision in 250-500 words in t
Investment Present Value
Present
Value
Factors
Total Investment
Installation
Other
450,000
$
4,950,000
450,000
$
4,950,000
nt Value
Total Benefit
1,437,000
1,652,000
1,877,000
2,102,000
2,327,000
9,395,000
Present
Value
Factors
0.93
0.865
0.805
0.749
0.697
Present Value
1,336,744
1,429,529
1,510,911
1,573,979
1,620,892
7,472,055
1
cient and allow for a reduction of
al cost of $10,000. There is a
on rate. The staff being
ge payback period and ROI and
on in 250-500 words in the text
Present Value
$
4,950,000
$
4,950,000
…
Purchase answer to see full
attachment
How it works
- Paste your instructions in the instructions box. You can also attach an instructions file
- Select the writer category, deadline, education level and review the instructions
- Make a payment for the order to be assignment to a writer
- Download the paper after the writer uploads it
Will the writer plagiarize my essay?
You will get a plagiarism-free paper and you can get an originality report upon request.
Is this service safe?
All the personal information is confidential and we have 100% safe payment methods. We also guarantee good grades