FM007 pre-Assessment 2

profileHoneybear17!
FM007_Assessment_JirannaFinancials.xlsx
Home>Business & Finance homework help>FM007 pre-Assessment 2

Net Sales Forecast

Timeline Values Forecast Lower Confidence Bound Upper Confidence Bound
2009 8609
2010 9233
2011 10079
2012 10417
2013 11430 11430 11430.00 11430.00
2014 11812.8728532664 11586.64 12039.11
2015 12603.3544266372 12370.05 12836.66
2016 13114.6753677 12874.40 13354.95
2017 13905.1569410708 13658.10 14152.21
Values 8609 9233 10079 10417 11430 Forecast 2009 2010 2011 2012 2013 2014 2015 2016 2017 11430 11812.872853266446 12603.354426637177 13114.675367700034 13905.156941070767 Lower Confidence Bound 2009 2010 2011 2012 2013 2014 2015 2016 2017 11430 11586.638871821253 12370.047623693723 12874.396133817909 13658.10197518207 Upper Confidence Bound 2009 2010 2011 2012 2013 2014 2015 2016 2017 11430 12039.10683471164 12836.661229580632 13354.95460158216 14152.211906959465

Operating Expense Forecast

Year Total Operating Expenses Forecast(Total Operating Expenses) Lower Confidence Bound(Total Operating Expenses) Upper Confidence Bound(Total Operating Expenses)
2009 8582
2010 9143
2011 9673
2012 9821
2013 10631 10631 10631.00 10631.00
2014 10830.8977258232 10494.57 11167.23
2015 11289.0712906964 10942.23 11635.91
2016 11690.7943290964 11333.59 12048.00
2017 12148.9678939696 11781.69 12516.25
Total Operating Expenses 8582 9143 9673 9821 10631 Forecast(Total Operating Expenses) 2009 2010 2011 2012 2013 2014 2015 2016 2017 10631 10830.897725823199 11289.0712906964 11690.794329096399 12148.967893969599 Lower Confidence Bound(Total Operating Expenses) 2009 2010 2011 2012 2013 2014 2015 2016 2017 10631 10494.569741763622 10942.228582982456 11333.586142126785 11781.686647808165 Upper Confidence Bound(Total Operating Expenses) 2009 2010 2011 2012 2013 2014 2015 2016 2017 10631 11167.225709882776 11635.913998410344 12048.002516066013 12516.249140131033

Operating Income Forecast

Year Operating income Forecast(Operating income) Lower Confidence Bound(Operating income) Upper Confidence Bound(Operating income)
2009 27
2010 90
2011 406
2012 596
2013 799 799 799.00 799.00
2014 998.4017874047 884.22 1112.59
2015 1330.6885508461 1215.59 1445.79
2016 1440.2634141786 1324.22 1556.30
2017 1772.55017762 1655.58 1889.52
Operating income 27 90 406 596 799 Forecast(Operating income) 2009 2010 2011 2012 2013 2014 2015 2016 2017 799 998.40178740469662 1330.6885508461121 1440.2634141786123 1772.5501776200274 Lower Confidence Bound(Operating income) 2009 2010 2011 2012 2013 2014 2015 2016 2017 799 884.21640279655344 1215.5860016475613 1324.2221835036758 1655.5777979695183 Upper Confidence Bound(Operating income) 2009 2010 2011 2012 2013 2014 2015 2016 2017 799 1112.5871720128398 1445.7911000446629 1556.3046448535488 1889.5225572705365

Net Income Forecast

Year Net Income Forecast(Net Income) Lower Confidence Bound(Net Income) Upper Confidence Bound(Net Income)
2009 181
2010 285
2011 651
2012 816
2013 1089 1089 1089.00 1089.00
2014 1281.7191111066 1214.74 1348.70
2015 1635.7708557763 1568.79 1702.75
2016 1770.1696104877 1702.63 1837.70
2017 2124.2213551573 2056.69 2191.76
Net Income 181 285 651 816 1089 Forecast(Net Income) 2009 2010 2011 2012 2013 2014 2015 2016 2017 1089 1281.7191111066402 1635.7708557762494 1770.169610487734 2124.2213551573432 Lower Confidence Bound(Net Income) 2009 2010 2011 2012 2013 2014 2015 2016 2017 1089 1214.7395788483527 1568.791022110745 1702.634775528762 2056.6856898435321 Upper Confidence Bound(Net Income) 2009 2010 2011 2012 2013 2014 2015 2016 2017 1089 1348.6986433649276 1702.7506894417538 1837.7044454467059 2191.7570204711542

Profit Margin Forecast

Year Total Profit Margin Forecast(Total Profit Margin) Lower Confidence Bound(Total Profit Margin) Upper Confidence Bound(Total Profit Margin)
2009 210%
2010 309%
2011 646%
2012 783%
2013 953% 953% 953% 953%
2014 1147% 1052% 1243%
2015 1446% 1350% 1541%
2016 1562% 1466% 1658%
2017 1860% 1764% 1956%
Total Profit Margin 2.1024509234522015 3.0867540344416766 6.4589741045738673 7.8333493328213502 9.5275590551181111 Forecast(Total Profit Margin) 2009 2010 2011 2012 2013 2014 2015 2016 2017 9.5275590551181111 11.474118152126724 14.456917528194262 15.620498774202209 18.603298150269747 Lower Confidence Bound(Total Profit Margin) 2009 2010 2011 2012 2013 2014 2015 2016 2017 9.5275590551181111 10.520635980535499 13.503431065942918 14.659111632135861 17.641899187748574 Upper Confidence Bound(Total Profit Margin) 2009 2010 2011 2012 2013 2014 2015 2016 2017 9.527 5590551181111 12.42760032371795 15.410403990445607 16.581885916268558 19.56469711279092

Calculations

Year 2009 2010 2011 2012 2013 Trend
Total Operating Revenues 8609 9233 10079 10417 11430
Total Operating Expenses 8582 9143 9673 9821 10631
Operating Income 27 90 406 596 799
Net Income 181 285 651 816 1089
Note: Numbers in Thousdands
Year 2009 2010 2011 2012 2013 Trend
Total Profit Margin 2% 3% 6% 8% 9%
Asset Turnover 1.19 1.15 1.22 1.16 1.19
Return on Assests 2.50% 3.60% 7.70% 8.90% 11.06%
Return on Net Worth 4.20% 6.20% 14.40% 16.40% 20.00%
Year 2009 2010 2011 2012 2013 Trend
Current Ratio 2.82 3.26 3.32 3.80 4.55
Day's cash on hand 27.33 43 51.5 52 65.1
Working Capital $ 118,000.00 $ 1,325,000.00 $ 1,531,000.00 $ 1,928,000.00 $ 2,503,000.00
Total Net Assets 4300 4585 4521 4978 5413

Income Statement

Jiranna Healthcare Income Statement December 31, 2013 (in thousands)
2009 2010 2011 2012 2013
Gross patient services revenues (non-GAAP) 8,870 9,490 10,400 11,200 12,050
Less deductions from revenues (non-GAAP) (780) (890) (1,000) (1,500) (1,600)
Net patient service revenues 8,090 8,600 9,400 9,700 10,450
Other operating revenues 519 633 679 717 980
Total operating revenues 8,609 9,233 10,079 10,417 11,430
Operating expenses
Salaries and wages 5,497 5,678 5,890 6,170 6,800
Supplies 823 850 855 890 905
Utilities 558 576 590 595 620
Insurance 44 46 49 54 70
Depreciation 168 173 175 178 188
Interest 142 146 154 179 198
Bad debts 363 375 400 455 500
Other operating expenses 987 1,299 1,560 1,300 1,350
Total operating expenses 8,582 9,143 9,673 9,821 10,631
Operating income 27 90 406 596 799
Nonoperating income 154 195 245 220 290
Excess of revenue over expenses $ 181 $ 285 $ 651 $ 816 $ 1,089
Change in net assets
Unrestricted $ 181 $ 285 $ 651 $ 816 $ 1,089
Temporarily restricted - - - - -
Permanently restricted - - - - -
Total change in net assets 181 285 651 816 1,089

Balance Sheet

Jiranna Healthcare Balance Sheet December 31, 2013 (in thousands)
2009 2010 2011 2012 2013
Current Assets $ 280 $ 124 $ 136 $ 295 $ 355
Cash 30 45 50 75 88
Receivables, net 1,340 1,536 1,700 1,896 2,400
Inventory 140 175 250 276 266
Prepaid expenses 40 32 40 53 78
Total Current Assets 1,830 1,912 2,176 2,595 3,187
Total Assets
Short-term investments 600 1,010 1,200 1,300 1,510
Plant and equipment 6,580 6,780 6,900 7,200 7,500
Less accumulated depreciation -1,660 -1750 -1,800 -1,950 -2,350
Plants and equipment, net 4,920 5,030 5,100 5,250 5,150
Total Assets $ 7,350 $ 7,952 $ 8,476 $ 9,145 $ 9,847
Current liabilities
Accounts payable 370 302 356 370 375
Accrued expenses payable 220 208 212 210 215
Deferred revenues 60 77 87 87 94
Total Current liabilities 650 587 655 667 684
Long-term liabilities 2,400 3,000 3,300 3,500 3,750
Total Liabilities 3,050 3,587 3,955 4,167 4,434
Net assets
Unrestricted 3,000 3,285 3,221 3,678 4,113
Temporarily restricted 700 700 700 700 700
Permanently restricted 600 600 600 600 600
Total Net Assets 4,300 4,585 4,521 4,978 5,413
Total Liabilities and Net Assets 7,350 8,172 8,476 9,145 9,847

Cash Flows

Jiranna Healthcare Cash Flows, 2013 (in thousands)
Cash Flows from Operating Activities
Cash received from patient and third-party payers $ 10,671
Cash received from operating revenue sources 800
Cash received from nonoperating revenue sources 270
Cash payments to employees (5,600)
Cash payments to suppliers of goods and services (4,800)
Net cash flow from operating activities $ 1,341
Cash flows from Investing Activities
Cash payments for purchase of plant assets (1,200)
Cash payments for purchase of long-term investments (670)
Proceeds from sales of plant assets 80
Proceeds from sale of long-term investments 60
Net cash flow from investing activities (1,730)
Cash flows from Financing Activities
Proceeds from issuance of 6% bonds payable 4,000
Principal payments on long-term debt (300)
Cash payments to retire 7% bonds payable (3,200)
Net cash flow from financing activities 500
Net increase/(decrease) in cash $ 111
Overall Net Cash Flow $ 222

Captial Project

Year 0 Year 1 Year 2 Year 3 Year 4 Year 5
Nurse Triage Salaries $ 523,800 $ 549,990 $ 577,490 $ 606,364 $ 636,682 $ 668,516
Forecasted ER Cost Reductions $ 400,000 $ 800,000 $ 848,000 $ 900,577 $ 955,512 $ 1,013,798
New IT Specialist's Salary $ 150,000 $ 154,500 $ 159,135 $ 163,909 $ 168,826 $ 173,891
Costs of Facility Renovations $ 30,000 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0
Necessary Capital Equipment Purchases $ 117,000 $ 3,510 $ 3,510 $ 3,510 $ 3,510 $ 3,510
Net Cash Flow:
Present Values of Net Cash Flows:
Net Present Value:
Internal Rate of Return (IRR):
Modified Internal Rate of Return (MIRR):
Payback Period (# years):
Discounted Payback Period (# years):

Variance Analysis Case Study

Jiranna Healthcare Expenses Worksheet, Mid-Year Review
Outpatient
Outpatient Product Line Unit Expense (FY10) Actual Units (FY10) Total OP Expenses (FY10) Unit Forecast (FY11) Unit Expence (Budget FY11) FY11 Budget (FY10+6% Inflation) Acutal Mid Year Units (FY11) Actual OP Expenses (FY11 YTD)
CARDIOLOGY $ 5,478.81 645 $ 3,533,832 677 $5,807.54 $ 3,931,704 197 $ 1,144,085
ENDOCRINOLOYGY $ 5,478.81 217 $ 1,188,902 228 $5,807.54 $ 1,324,119 112 $ 650,444
FAMILY MED $ 5,476.81 1,105 $ 6,051,875 1,161 $5,802.92 $ 6,737,188 636 $ 3,690,656
GYN $ 5,478.81 208 $ 1,139,592 219 $5,794.28 $ 1,268,947 116 $ 672,136
ENT $ 5,475.81 690 $ 3,778,309 724 $5,804.36 $ 4,202,356 421 $ 2,443,635
MENTAL HEALTH $ 498.78 1,287 $ 641,930 1,351 $528.71 $ 714,283 736 $ 389,128
NEWBORN $ 5,478.81 698 $ 3,824,209 733 $5,807.54 $ 4,256,926 433 $ 2,514,664
OB $ 5,478.81 1,430 $ 7,834,698 1,501 $5,807.54 $ 8,717,115 868 $ 5,040,943
ORTHO $ 5,478.81 601 $ 3,292,765 631 $5,807.54 $ 3,664,557 274 $ 1,591,266
PEDIATRICS $ 5,477.81 124 $ 679,248 130 $5,806.48 $ 754,842 72 $ 418,066
PODIATRY $ 5,478.81 2,050 $ 11,231,561 2,152 $5,807.54 $ 12,497,823 800 $ 4,646,031
Total Expenses $55,281 9,055 $43,196,921 9,507 $58,582 $48,069,860 4,665 $23,201,056
Jiranna Healthcare Revenues Worksheet, Mid-Year Review
Outpatient
Outpatient Product Line Unit Price (FY10) Actual Units (FY10) Total OP Revenue (FY11) Unit Forecasted For (FY11) Unit Price (Budget FY11) Projected OP Revenue For FY11 Actual (FY11 YTD) Actual OP Revenue (FY11 YTD)
CARDIOLOGY $ 6,118.73 645 $ 3,946,580.85 677 $6,118.73 $ 4,142,380 197 $ 1,205,390
ENDOCRINOLOYGY $ 6,103.65 217 $ 1,324,492.05 228 $6,103.65 $ 1,391,632 112 $ 683,609
FAMILY MED $ 6,021.65 1,105 $ 6,653,923.25 1,161 $6,019.06 $ 6,988,125 636 $ 3,828,120
GYN $ 6,013.65 208 $ 1,250,839.20 219 $5,999.92 $ 1,313,983 116 $ 695,991
ENT $ 6,010.65 690 $ 4,147,348.50 724 $6,010.65 $ 4,351,711 421 $ 2,530,484
MENTAL HEALTH $ 502.99 1,287 $ 647,348.13 1,351 $502.99 $ 679,539 736 $ 370,200
NEWBORN $ 7,014.71 698 $ 4,896,267.58 733 $7,014.71 $ 5,141,782 433 $ 3,037,369
OB $ 5,713.65 1,430 $ 8,170,519.50 1,501 $5,713.65 $ 8,576,189 868 $ 4,959,448
ORTHO $ 6,084.18 601 $ 3,656,592.18 631 $6,084.18 $ 3,839,118 274 $ 1,667,066
PEDIATRICS $ 6,001.65 124 $ 744,204.60 130 $6,001.65 $ 780,215 72 $ 432,119
PODIATRY $ 6,018.14 2,050 $ 12,337,187.00 2,152 $6,018.14 $ 12,951,037 800 $ 4,814,512
Total Revenue $61,604 9,055 $47,775,303 9,507 $61,587 $50,155,711 4,665 $24,224,308
Product Line MDC DescrOPtion
CARDIOLOGY Diseases and Disorders of the Circulatory System (Hypertension, Heart Disease, etc.)
ENDOCRINOLOYGY Diseases and Disorders of the Endocrin System (Diabetes Type 1 & 2, Thyroid Disorders, etc)
FAMILY MED Family Primary Care and Disease Preventative Services
GYN Diseases and Disorders of Women's Reproductive Health
ENT Diseases and Disorders of the Ear, Nose, Mouth, and Throat
MENTAL HEALTH Mental Diseases and Disorders
NEWBORN Newborns and Other Neonates with Conditions Originating in Perinatal Period
OB Pregnancy, Childbirth, and the Puerperium
ORTHO Diseases and Disorders of the Musculoskeletal System and Connective Tissue
PEDIATRICS Pediatric Primary Care and Disease Prevention Services (0 - 18 years)
PODIATRY Diseases and Disorders of the Feet and Lower Limbs