Solution
Ishmeet Singh answered on
Sep 19 2021
SUMMARY
SUMMARY LEGEND
Tab colou
Green Optimized study from previous macro deliverables
Blue Study submitted uptil now
Note: Please note Revenues & Financials have been optimised
You may delete this after reading (I advise to write them again in your own language after analyzing)
Difference between earlier & these financials:
Basically in earlier state the financial showed shareholders deficit under no circumstances can you expect
an already stresses business to be running a viable project.
That is a situation where your assets value is way under the liabilities.
No one entertains a cash credit under that circumstances even if you are an SPVs status.
What changes I have done is in operations making it more organised an quantitative in nature.
Where expenses are
ought under control.
Thus even a company making 18 million of revenues can be seen easily making 13 millions in credit and making
project viable.
As far as ques is concerned:
Yes, Based on Project Dashboard and relevant graphs of Net Worth Cash Balance one should go for buying equipments
Only concern is IRR if they get a better project its fine otherwise no.
Reason being why we are advocating is because depreciation is serving a means of blanket to taxes therefore it is turning viable
Otherwise, considering last cases one could not have gone as the company didn't have any tax liability as it was in loss.
Revenues & Net Income
Revenues 2018 2019 2020 2021 2022 2023 18244836.774277791 19540220.185251508 20927575.818404365 22413433.701511074 24004787.494318362 25709127.406414971 Net Income 2018 2019 2020 2021 2022 2023 13335859.575934462 14611752.861779917 15851662.879397113 17177762.996061672 18545572.201616783 19979009.807126053 Operting Cash Flows 2018 2019 2020 2021 2022 2023 14091578.470391396 15470542.236619692 16709892.966517178 18035394.086033929 19402561.765643738 20835312.29686559
Cash Balance and Net Worth
Cash Balance 2018 2019 2020 2021 2022 2023 12832431.495078897 25727962.79169859 39784523.398215771 55547262.204249695 74949823.969893426 95785136.266759008 Net Worth 2018 2019 2020 2021 2022 2023 17575714.020621963 32187466.88240188 48039129.761798993 65216892.757860661 83762464.95947744 103741474.76660348
Cove
Title: Due Diligence Financial Model Template
Filename: Community Health Systems Inc.xlsx
Date created: September 19, 2020
Last modified: September 19, 2020
Sheet Explanation Input Calculation Output
Input Main input / assumptions driving the financial model X
Revenues Revenues X
Manpower Manpower - Count & Compensation
Costs Costs forecasts X
CAPEX & Dep CAPEX and Depreciation Forecasts X
Working Capital Working Capital Forecasts X
Financial Statements Consolidated financial statements X
Charts Chart of Revenues, Cash Flows, Net Income, Cash Balance and Net Worth X
Color coding
Input
Calculations
Output
2018 2019 2020 2021 2022 2023
Revenues 18,244,837 19,540,220 20,927,576 22,413,434 24,004,787 25,709,127
Net Income 13,335,860 14,611,753 15,851,663 17,177,763 18,545,572 19,979,010
Operting Cash Flows 14,091,578 15,470,542 16,709,893 18,035,394 19,402,562 20,835,312
2018 2019 2020 2021 2022 2023
Cash Balance 12,832,431 25,727,963 39,784,523 55,547,262 74,949,824 95,785,136
Net Worth 17,575,714 32,187,467 48,039,130 65,216,893 83,762,465 103,741,475
Manpowe
Manpower - Count & Compensation
A Assumed
P Projected
A A P P P P
2018 2019 2020 2021 2022 2023
G & A
Housekeeping
Count 2 2 2 2 2 2
Compensation 22,973 24,122 25,328 26,594 27,924 29,320
Total Compensation 45,946 48,243 50,656 53,188 55,848 58,640
Administrative Staff
Count 3 3 3 3 3 3
Compensation 91,892 96,487 101,311 106,377 111,696 117,280
Total Compensation 275,677 289,461 303,934 319,130 335,087 351,841
Receptionist
Count 1 1 1 1 1 1
Compensation 45,946 48,243 50,656 53,188 55,848 58,640
Total Compensation 45,946 48,243 50,656 53,188 55,848 58,640
Call Center Staff
Count 6 6 6 6 6 6
Compensation 45,946 48,243 50,656 53,188 55,848 58,640
Total Compensation 275,677 289,461 303,934 319,130 335,087 351,841
Count 12 12 12 12 12 12
Compensation 643,246 675,408 709,179 744,638 781,869 820,963
Headcount increase (YoY) 12 0 0 0 0 0
Avg yearly wage 17,230 18,091 18,996 19,946 20,943 21,990
2018 2019 2020 2021 2022 2023
Vehicle Running & Maintenance
Drivers
Count 5 5 5 5 5 5
Compensation 45,946 48,243 50,656 53,188 55,848 58,640
Total Compensation 229,731 241,217 253,278 265,942 279,239 293,201
Count 5 5 5 5 5 5
Compensation 229,731 241,217 253,278 265,942 279,239 293,201
Headcount increase (YoY) 5 0 0 0 0 0
Avg yearly wage 45,946 48,243 50,656 53,188 55,848 58,640
2018 2019 2020 2021 2022 2023
Direct Staff
Nurses
Count 8 8 8 8 8 8
Compensation 76,577 80,406 84,426 88,647 93,080 97,734
Total Compensation 612,615 643,246 675,408 709,179 744,638 781,869
Count 8 8 8 8 8 8
Compensation 612,615 643,246 675,408 709,179 744,638 781,869
Headcount increase (YoY) 8 0 0 0 0 0
Avg yearly wage 76,577 80,406 84,426 88,647 93,080 97,734
Total Count 25 25 25 25 25 25
Total Compensation 1,485,592 1,559,871 1,637,865 1,719,758 1,805,746 1,896,033
Count for a given position is in the input
Cost per resource is driven by total annual compensation per employee and increased annually by a specified percentage
Summing up of the different t costs by type of employee
Revenues
Revenues
2018 2019 2020 2021 2022 2023
Diabetic Patients 12,682,062 13,582,488 14,546,845 15,579,671 16,685,827 17,870,521
Physiotherapy Patients 4,954,036 5,305,773 5,682,482 6,085,939 6,518,040 6,980,821
Rehabiliation Patients 608,739 651,959 698,249 747,824 800,920 857,785
Total sales revenues 18,244,837 19,540,220 20,927,576 22,413,434 24,004,787 25,709,127
Input
Input
Input cell
Start Year 2013
Calculation cell
Scenario Summary
Revenues
No. of Patients 2018 2019 2020 2021 2022 2023
Diabetic Patients 6624 6757 6892 7030 7171 7314
Physiotherapy Patients 12939 13197 13461 13731 14005 14285
Rehabiliation Patients 1590 1622 1654 1687 1721 1755
Cost of Services
in dollars 2018 2019 2020 2021 2022 2023
Diabetic Patients 1914 2010 2111 2216 2327 2443
Physiotherapy Patients 383 402 422 443 465 489
Rehabiliation Patients 383 402 422 443 465 489
Raw Material
volumes 2018 2019 2020 2021 2022 2023
Diabetic Patients 6624 6757 6892 7030 7171 7314
Physiotherapy Patients 12939 13197 13461 13731 14005 14285
Rehabiliation Patients 1590 1622 1654 1687 1721 1755
Raw Material Prices
in dollars 2018 2019 2020 2021 2022 2023
Diabetic Patients 64 67 70 74 78 81
Physiotherapy Patients 64 67 70 74 78 81
Rehabiliation Patients 64 67 70 74 78 81
General Input
Working capital 2018 2019 2020 2021 2022 2023
Days payable (General & Administrative) 0 0 0 0 0 0
Days payable (Vehicle Running & Maintenance)
Days payable (COGS)
Days receivable 0 0 0 0 0 0
Days inventory 30 30 30 30 30 30
Manpower Count 2018 2019 2020 2021 2022 2023
General & Administrative Staff
Housekeeping 2 2 2 2 2 2
Administrative Staff 3 3 3 3 3 3
Receptionist 1 1 1 1 1 1
Call Center Staff 6 6 6 6 6 6
Vechile Running & Maintenance Staff
Drivers 5 5 5 5 5 5
Assistants 0 0 0 0 0 0
Direct Staff
Junior Doctors 0 0 0 0 0 0
Consultants 0 0 0 0 0 0
Nurses 8 8 8 8 8 8
Attendants 0 0 0 0 0 0
Manpower Cost Base Comp. Benefits Total Comp
Yearly Comp (K SAR)
General & Administrative Staff
Housekeeping 18 - 0 18
Administrative Staff 72 - 0 72
Receptionist 36 - 0 36
Call Center Staff 36 - 0 36
Vechile Running & Maintenance Staff
Drivers 36 36
Direct Staff
Junior Doctors - 0
Consultants - 0
Nurses 60 60
Attendants - 0
Annual operating expenses assumptions (in SAR)
Fixed Annual Exp Variable Annual Exp Sources
G&A
Office Rent 50,000 0
Expenses on Office Utilities 24,000 0 Electricity, Water, Telephone, Insurance etc
Staff Accommodation Rent 35,000 0
Staff Accommodation Utilities 12,000 0%
Vehicle Running & Maintenance
Car Petrol Expenses 48,000 0
Car Repairs & Maintenance 60,000 0
Other Operating Expenses
CAPEX
Capital Expenditures ( SAR) 2018 2019 2020 2021 2022 2023
Furniture:
1 Director Office 37,000
3 Administrator Desks 39,000
1 Receptionist Desk 83,000
4 Nurse Desks and Chairs 166,000
Call Center for 6 Staff 12,000
Total 337,000
Computer (20 No.) 1,000,000
Medical Equipment 10,000,000
Car 553,000
Communication Phone 1,110,000
Total Capex 13,000,000 0 0 0 0 0
INITIAL PROJECT EXPENSES
Initial Project Expenses (SAR) 2018
License fees 15,000
Singboard and Printing 10,000
Marketing Expenses 15,000
Recuritment Cost 80,000
Total Initial Project Expenses 120,000
CASH FOR 3 MONTHS OPERATION
Total Amount No.of Months Expenses per month
Office Rent 12,500 3 4,167
Expenses on office utilities 6,000 3 2,000
Staff Accommodation Rent 8,750 3 2,917
Staff Accommodation Utilities 3,000 3 1,000
Car Petrol Expenses 12,000 3 4,000
Equip Instalment 691,207 3 230,402
Car Repair & Maintenance 15,000 3 5,000
Salaries 371,398 3 123,799
Total 1,119,854
Total Project Cost 14,239,854
Financing
Financing 2018 2019 2020 2021 2022 2023
Entrepreneur Contribution 4,239,854
Equip financing 10,000,000
Others
Other Assumptions
Annual Increase in Salaries & Benefits 5%
Depreciation Period (Years) 15
Interest Rate on Loan 3.00%
Repayment term in years 3.83
Management fees (per annum) 180,000
Zakat 2.5%
Inflation 5.0%
Number of days in a year 365
Scenario assumptions
Cost of Service (in SAR) 2018 Y to Y
Diabetic Patients 1,500 5.00%
Physiotherapy Patients 300 5.00%
Rehabiliation Patients (per visit fees) 300 5.00%
Number of Patients 2018 Y to Y
Diabetic Patients 6,000 2.00%
Physiotherapy Patients 11,719 2.00%
Rehabilitation Patients (No. of visits) 1,440 2.00%
Raw Material Prices (in SAR) 2018 Y to Y
Diabetic Patients 50 5.00%
Physiotherapy Patients 50 5.00%
Rehabilitation Patients 50 5.00%
Raw Material (in units) 2018 Y to Y
Diabetic Patients 6,000 2.00%
Physiotherapy Patients 11,719 2.00%
Rehabilitation Patients 1,440 2.00%
input the capital expenditure per year.
the headline can be adapted based on the venture
Costs
Opex
A A P P P P
2018 2019 2020 2021 2022 2023
General & Administrative Expenses
G&A Salaries 643,246 675,408 709,179 744,638 781,869 820,963
Office Rent 63,814 67,005 70,355 73,873 77,566 81,445
Expenses on office utilities 30,631 32,162 33,770 35,459 37,232 39,093
Staff Accommodation Rent 44,670 46,903 49,249 51,711 54,296 57,011
Staff Accommodation Utilities 15,315 16,081 16,885 17,729 18,616 19,547
Total G&A 797,676 837,560 879,438 923,410 969,580 1,018,059
Vehicle Running & Maintenance
Vechile Running Staff Salaries 229,731 241,217 253,278 265,942 279,239 293,201
Car Petrol Expenses 61,262 64,325 67,541 70,918 74,464 78,187
Car Repairs & Maintenance 76,577 80,406 84,426 88,647 93,080 97,734
Total S&M 367,569 385,948 405,245 425,507 446,783 469,122
Cost of material & medicines
Raw Material 1,349,865 1,445,705 1,548,350 1,658,283 1,776,021 1,902,118
Direct Staff Salaries 612,615 643,246 675,408 709,179 744,638 781,869
Total COGS 1,962,480 2,088,951 2,223,758 2,367,461 2,520,658 2,683,988
Total OPEX 3,127,725 3,312,458 3,508,441 3,716,378 3,937,021 4,171,169
Capex & Dep
Capex & Depreciation
Life of Assets 15
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
2018 2019 2020 2021 2022 2023 2024 2025 2026 2027 2028 2029 2030 2031 2032
Assets
Net addition to gross assets 13,000,000 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Gross Assets 13000000 13000000 13000000 13000000 13000000 13000000 13000000 13000000 13000000 13000000 13000000 13000000 13000000 13000000 13000000
Beginning of Yr Undepreciated Assets 13,000,000 12,133,333 11,266,667 10,400,000 9,533,333 8,666,667 7,800,000 6,933,333 6,066,667 5,200,000 4,333,333 3,466,667 2,600,000 1,733,333 866,667
1,300,000 1,300,000 1,300,000 1,300,000 1,300,000 1,300,000
Total Depreciation 866,667 866,667 866,667 866,667 866,667 866,667 866,667 866,667 866,667 866,667 866,667 866,667 866,667 866,667 866,667
Asset purchased in year 1 866,667 866,667 866,667 866,667 866,667 866,667 866,667 866,667 866,667 866,667 866,667 866,667 866,667 866,667 866,667
Asset purchased in year 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Asset purchased in year 3 0 0 0 0 0 0 0 0 0 0 0 0 0
Asset purchased in year 4 0 0 0 0 0 0 0 0 0 0 0 0
Asset purchased in year 5 0 0 0 0 0 0 0 0 0 0 0
Asset purchased in year 6 0 0 0 0 0 0 0 0 0 0
End of Yr Undepreciated Assets 12,133,333 11,266,667 10,400,000 9,533,333 8,666,667 7,800,000 6,933,333 6,066,667 5,200,000 4,333,333 3,466,667 2,600,000 1,733,333 866,667 0
Financial Statements
Financial Statements
Income Statement (SAR)
2018 2019 2020 2021 2022 2023
Sales revenues
Sales revenues 18,244,837 19,540,220 20,927,576 22,413,434 24,004,787 25,709,127
Net sales revenues 18,244,837 19,540,220 20,927,576 22,413,434 24,004,787 25,709,127
COGS 1,962,480 2,088,951 2,223,758 2,367,461 2,520,658 2,683,988
Gross Profit 16,282,357 17,451,269 18,703,818 20,045,972 21,484,129 23,025,140
Gross margin 89% 89% 89% 89% 89% 90%
G&A & Vechile Expenses
G&A Expenses 797,676 837,560 879,438 923,410 969,580 1,018,059
Vehicle Running & Maintenance Expenses 367,569 385,948 405,245 425,507 446,783 469,122
Management fee 180,000 180,000 180,000 180,000 180,000 180,000
EBITDA 14,937,112 16,047,762 17,239,135 18,517,055 19,887,766 21,357,959
EBITDA margin 82% 82% 82% 83% 83% 83%
Depreciation
Fixed assets depreciation 866,667 866,667 866,667 866,667 866,667 866,667
Total depreciation expenses 866,667 866,667 866,667 866,667 866,667 866,667
Initial Project Expenses:
License Fees 15,000
Signboard and Printing 10,000
Marketing Expenses 15,000
Recruitment Cost 80,000
EBIT 13,950,445 15,181,095 16,372,468 17,650,389 19,021,100 20,491,292
EBIT margin 76% 78% 78% 79% 79% 80%
Interest on car financing 265,825 189,815 111,494 31,366 0 0
Zakah 348,761 379,527 409,312 441,260 475,527 512,282
Dividends 0 0 0 0 0 0
Net income 13,335,860 14,611,753 15,851,663 17,177,763 18,545,572 19,979,010
Net margin 73% 75% 76% 77% 77% 78%
Cash flow statement (SAR) 2/19/18 2/19/19 2/19/20 2/19/21 2/19/22 2/19/23
2018 2019 2020 2021 2022 2023
Cash Flow from operations
Net income 13,335,860 14,611,753 15,851,663 17,177,763 18,545,572 19,979,010
Depreciation 866,667 866,667 866,667 866,667 866,667 866,667
Change in Net Working Capital 110,948 7,877 8,437 9,036 9,677 10,364
Total cash flow from operations 14,091,578 15,470,542 16,709,893 18,035,394 19,402,562 20,835,312
Cash Flow from investing
Capex/maintenance capex 13,000,000 0 0 0 0 0
Total cash flow from investment -13,000,000 0 0 0 0 0
Total cash from from financing
Entrepreneur...