Reference no: EM13360405

No one can predict the future, but accountants and financial managers must try and do exactly that!  By examining net revenue, costs, and cash flow, you can get a clearer picture of what to expect in your organization's (or one with which you are familiar) fiscal future. Using these metrics to look forward will enable you to more effectively plan budgets that accomplish organizational goals.

In this Assignment, you address three scenarios. One scenario focuses on net revenue, another revolves around fixed and variable costs, and the third presentsinformation on cash flow. You will use the information provided in the scenarios to answer questions and explore how net revenue, fixed and variable costs, and cash flow impact the ability of an organization to provide services. You will also consider the different categories of costs.  A Work template is provided as an example.  You will need to put create this template in Excel and add the necessary values and calculations.

Note: For those Assignments in this course that require you to perform calculations you must:

• Create an Excel spreadsheet containing the information provided.  Template in Word is provided.

• Show all your work.

• Answer any questions included with the problems (as text in the Excel spreadsheet).

For those not comfortable with the use of Microsoft Excel, this week's Optional Resources suggest several tutorials.

To prepare:

• Review the information in this week's Learning Resources (including the Media) dealing with net revenue, fixed and variable costs, and cash flow and how they are used in financial decision making.

• Carefully examine the information in each of the three scenarios below and consider how calculations using this information can be used to answer the questions asked.

Net RevenueScenario

Yourclinic provides fourkinds of services:

Comprehensiveinitialmedical consultationispricedat $250

• Establishedpatient limitedvisit is pricedat $75
• Establishedpatient intermediatevisit is pricedat$125
• Establishedpatient comprehensivevisit is pricedat $250

Question: The profile of your patients is such that the average collection rate is 75%. Assuming you have 100 visits of each type each month, what amount of new revenue will you generate in the next 12 months? 

Template for the Net Revenue Scenario

 

Ta

		Annual Volume	Gross Revenue
Type of Service	Price Each
Comprehensive initial medical consultant
Established patient limited visit
Established patient intermediate visit
Established patient comprehensive visit
Total Gross Revenue
Average Collection Rate
Total Net Revenue

 

Fixed/VariableCostScenario

 

Youhaveperformedacost analysis of yourhealthserviceorganizationandhave determinedthefollowing: basedonthelatestthreeyears ofinformation, yourannual cost ofoperations is $1,600,000withannual volumeof 10,000procedures. Youhave determinedthat certainofyoursupplyitems arefixedinnature(thosemarkedwithan F) whileothers arevariable(markedwithaV).

 

Cost Items	F/V	Average Annual Amount	Cost Items	F/V	Average Annual Amount
Supplyitem1	F	$220,000	Supplyitem6	F	50,000
Supplyitem2	F	180,000	Supplyitem7	V	500,000
Supplyitem3	F	75,000	Supplyitem8	V	300,000
Supplyitem4	F	50,000	Supplyitem9	V	200,000
Supplyitem5	F	25,000	Total		1,600,000

 Question: Aninsurance company that is considering directing its 1,000units peryearof procedurebusinesstoyourorganizationhasapproachedyou. Yourboardhasmandatedthatyoumake$5of profitfromeachof theprocedures. You obviously want thehighestpossibleprice,butas youenterthenegotiations, what isthe lowest possiblepriceyouwouldbewillingtoacceptfromthispayer? 

Hint: Calculate the variable cost.

Template for the Fixed/Variable Cost Scenario

SupplyItem	Total	Variable	Fixed
SupplyItem1
SupplyItem2
SupplyItem3
SupplyItem4
SupplyItem5
SupplyItem6
SupplyItem7
SupplyItem8
SupplyItem9
Total
AnnualVolume
VariableCost per Unit
ProfitTarget
Total

Cash Flow Scenario

Your new business venture will begin operationon July1,20X2. Youwill hirestaff effectiveJanuary1, 20X2withacost of $40,000per month. Youknowfromexperience that collectionslagbillingby3 months (inotherwords, onceyoubill foraservice, you must wait 90days forthepaymenttobereceived. Yourbusinessvolume is projectedto beasfollows:

Month	Volume	Billing
July, 20X2	1,000	$100,000
August	1,000	$100,000
September	1,000	$100,000
October	1,000	$100,000
November	1,000	$100,000
December	1,000	$100,000
January, 20X3	1,000	$100,000
February	1,000	$100,000
March	1,000	$100,000
April	1,000	$100,000
May	1,000	$100,000
June	1,000	$100,000

 

Question: If youhave$380,000of cashonhandJanuary1, 20X2,howmuchcashwill youhaveat theendof June20X3?ssumea100%collectionrate.

 

Template for the Cash Flow Scenario 

Month	Opening Cash Balance	Monthly Expense	Monthly Billing	Monthly Collections	Ending Cash Balance
January20X2	$380,000	$40,000			$340,000
February	$340,000
March
April
May
June
July
August
September
October
November
December
January20X3
February
March
April
May
June