Financial Analytics for Managerial Decisions: Case Study

This case study covers the preparation of monthly cash budgets and cost volume profit analysis for PenHUB Coworking. It includes a detailed breakdown of cash inflows and outflows, fixed and variable costs, and break-even analysis. The subject is Financial Analytics for Managerial Decisions, and the course code is not mentioned. The content is relevant for students pursuing finance or management courses in any college or university.

Case study for Financial
Analytics for Managerial
Decisions

TABLE OF CONTENTS
Part A...........................................................................................................................................................................................................3
1. Monthly cash budget............................................................................................................................................................................3
2. Cost volume profit...............................................................................................................................................................................5
Part B.........................................................................................................................................................................................................13
1. Weighted average cost of capital.......................................................................................................................................................13
2. Net present value method...................................................................................................................................................................13
Part C.........................................................................................................................................................................................................15

Part A
1. Monthly cash budget
Part A
1. Preparation of monthly cash budgets For PenHUB Coworking for the period from 1st January 2022 to 31st December 2022
Particulars January February March April May June July August September October November December
Opening Cash Balance 40000 31368.6 82661.2 34255.8 25355.8 27285 35445.
1
27341.8 29193.3 34040.7 34534.4 57450.2
Cash inflow from operations
Revenue from flexible desk:
Membership fees 9000 10000 10500 11000 11500 12500 12500 12500 13000 13000 8000 8000
Priniting Passes 2025 2250 2362.5 2475 2587.5 2812.5 2812.5 2812.5 2925 2925 1800 1800
Meeting Room Hire 1800 2000 2100 2200 2300 2500 2500 2500 2600 2600 1600 1600
Revenue from Dedicated desk:
Membership fees 12100 13200 14300 14300 14300 14300 15400 15400 16500 16500 9900 8800
Priniting Passes 206.25 225 243.75 243.75 243.75 243.75 262.5 262.5 281.25 281.25 168.75 150
Meeting Room Hire 1100 1200 1300 1300 1300 1300 1400 1400 1500 1500 900 800
Revenue from special event 300 300 300 300 300 300
Total Cash Inflow 26231.25 29175 30806.3 31818.75 32231.25 33956 34875 35175 36806.25 37106.25 22368.75 21450
Cash Outflow from operations
Variable cost of Flexible Desk
Commission To Excelcard - Memberships 76.5 85 89.25 93.5 97.75 106.25 106.25 106.25 110.5 110.5 68 68
Commission To Excelcard - Printing
Passes
17.2125 19.125 20.0813 21.0375 21.99375 23.906 23.906 23.9063 24.8625 24.8625 15.3 15.3
Commission To Excelcard - Room Hire 15.3 17 17.85 18.7 19.55 21.25 21.25 21.25 22.1 22.1 13.6 13.6
Variable and Fixed cost of dedicated
desk:
Commission To Excelcard - Memberships 102.85 112.2 121.55 121.55 121.55 121.55 130.9 130.9 140.25 140.25 84.15 74.8
Commission To Excelcard - Printing
Passes
1.753125 1.9125 2.07188 2.071875 2.071875 2.0719 2.2313 2.23125 2.390625 2.390625 1.434375 1.275
Commission To Excelcard - Room Hire 9.35 10.2 11.05 11.05 11.05 11.05 11.9 11.9 12.75 12.75 7.65 6.8

Commission To Excelcard - Special event 2.55 2.55 2.55 2.55 2.55 2.55
Premises Rental 6000 6000 6000 6120
Local Council rates 6000
Furniture
cost
54000
Fitting expenses 2000
Kitchen equipment expenses 500
Rental office equipment 4000 4000 4000 4000 4000 4000 4000 4000 4000 4000 4000 4200
Air Filter cost 5000
Activation fees 100
Communications cost 600 600 600 600 600 600 600 600 600 600 600 609
Staffing
cost:
8 am to 11
am
2448 2448 2652 2244 2652 2550 2652 2809.08 2601 2705.04 2705.04 2601
11 am to 7
pm
13056 13056 14144 11968 14144 13600 14144 14981.8 13872 14426.88 14426.88 13872
7 pm to 8
pm
816 816 884 748 884 850 884 936.36 867 901.68 901.68 867
Cleaning
cost
800 800 800 800 800 800 800 800 800 800 800 800
Supplies expenses:
Refreshment 290 320 340 350 360 380 390 390 410 410 250 240
hand snaitizer, face mask etc. 8.64 43.2 43.92 45.36 41.04 46.44 45.36 47.16 47.88 45.36 46.44 45
Insurance
cost
450 450 450 450 450 450 450 450 450 450 450 460.125
Utility cost 197 200 200 200 200 200 200 200 200 200 200 200
Administration and Clerical service 180 180 180 180 180 180 180 180 180 180 180 180
Special Event cost:
Guest speaker expenses 250 250 250 250 250 250
Refreshment 500 500 500 500 500 500
Labour cost 136 136 136 136 136 136

Advertisement 50 50 50 50 50 50
Ongoing marketing 200 200 200 200 200 200 200 200 200 200 200 200
Membership cost 1000 1000 1000 1000
Professional membership 1000 1000
Contribution to Mr Lopez 2500 2500 2500 2500 2500 2500 2500 2500 2500 2500 2500 2500
Drawing by Mr Lopez 30000
Total Cash Outflow 31368.606 82661.188 34255.8 25355.82 27285.01 35445.
1
27342.
8
29193.3 34040.733 34534.36 57450.174 35876.45
Closing Cash Balance 34862.644 -22118 79211.7 40718.7 30302 25796.
2
42978.
3
33323.5 31958.8 36612.6 -547.02 43023.8
2. Cost volume profit
Break even numbers of monthly membership
Particular January February March April May June July August September October November December
membership 90 100 105 110 115 125 125 125 130 130 80 80
fees 100 100 100 100 100 100 100 100 100 100 100 100
total members
revenue 9000 10000 10500 11000 11500 12500 12500 12500 13000 13000 8000 8000
printing pass 2025 2250 2362.5 2475 2587.5 2812.5 2812.5 2812.5 2925 2925 1800 1800
hire 1800 2000 2100 2200 2300 2500 2500 2500 2600 2600 1600 1600
total revenue 12825 14250 14962.5 15675 16387.5 17812.5 17812.5 17812.5 18525 18525 11400 11400
Less: variable
cost
membership 76.5 85 89.25 93.5 97.75 106.25 106.25 106.25 110.5 110.5 68 68
printing pass 17.2125 19.125 20.0813 21.0375 21.9938 23.9063 23.9063 23.9063 24.8625 24.8625 15.3 15.3
room hire 15.3 17 17.85 18.7 19.55 21.25 21.25 21.25 22.1 22.1 13.6 13.6
Total variable 109.013 121.125 127.181 133.238 139.294 151.406 151.406 151.406 157.463 157.463 96.9 96.9

cost
Contribution 12716 14128.9 14835.3 15541.8 16248.2 17661.1 17661.1 17661.1 18367.5 18367.5 11303.1 11303.1
Less: fixed cost
Premises Rental 6000 6000 6000 6120
Local Council
rates 6000
Furniture cost 54000
Fitting expenses 2000
Kitchen
equipment
expenses 500
Rental office
equipment 4000 4000 4000 4000 4000 4000 4000 4000 4000 4000 4000 4200
Air Filter cost 5000
Activation fees 100
Communications
cost 600 600 600 600 600 600 600 600 600 600 600 609
Staffing cost:
8 am to 11 am 2448 2448 2652 2244 2652 2550 2652 2809.08 2601 2705.04 2705.04 2601
11 am to 7 pm 13056 13056 14144 11968 14144 13600 14144
14981.7
6 13872
14426.8
8 14426.88 13872
7 pm to 8 pm 816 816 884 748 884 850 884 936.36 867 901.68 901.68 867
Cleaning cost 800 800 800 800 800 800 800 800 800 800 800 800

Supplies
expenses:
Refreshment 290 320 340 350 360 380 390 390 410 410 250 240
hand sanitizer,
face mask etc. 8.64 43.2 43.92 45.36 41.04 46.44 45.36 47.16 47.88 45.36 46.44 45
Insurance cost 450 450 450 450 450 450 450 450 450 450 450 460.125
Utility cost 197 200 200 200 200 200 200 200 200 200 200 200
Administration
and Clerical
service 180 180 180 180 180 180 180 180 180 180 180 180
Special Event
cost:
Guest speaker
expenses 250 250 250 250 250 250
Refreshment 500 500 500 500 500 500
Advertisement 50 50 50 50 50 50
Ongoing
marketing 200 200 200 200 200 200 200 200 200 200 200 200
Membership
cost 1000 1000 1000 1000
Professional
membership 1000 1000
Contribution to
Mr Lopez 2500 2500 2500 2500 2500 2500 2500 2500 2500 2500 2500 2500
Drawing by Mr
Lopez 30000
Depreciation 833.33 833.33 833.33 833.33 833.33 833.33 833.33 833.33 833.33 833.33 833.33 833.33
tax 108.94 108.94 108.94 108.94 108.94 108.94 108.94 108.94 108.94 108.94 108.94 108.94

Total fixed cost
32087.9
1
83355.4
7
34936.1
9
26027.6
3
27953.3
1
36098.7
1
27987.6
3
29836.6
3 34670.15
35161.2
3 58202.31 36636.4
BEP 0.40 0.17 0.42 0.60 0.58 0.49 0.63 0.59 0.53 0.52 0.19 0.31
Dedicated desks
Particular January February March April May June July August September October November December
membership 55 60 65 65 65 65 70 70 75 75 45 40
fees 220 220 220 220 220 220 220 220 220 220 220 220
total members
revenue 12100 13200 14300 14300 14300 14300 15400 15400 16500 16500 9900 8800
printing pass 206.25 225 243.75 243.75 243.75 243.75 262.5 262.5 281.25 281.25 168.75 150
hire 1100 1200 1300 1300 1300 1300 1400 1400 1500 1500 900 800
total revenue 13406.3 14625 15843.8 15843.8 15843.8 15843.8 17062.5 17062.5 18281.3 18281.3 10968.8 9750
Less: variable
cost
membership 102.85 112.2 121.55 121.55 121.55 121.55 130.9 130.9 140.25 140.25 84.15 74.8
printing pass 1.75313 1.9125 2.07188 2.07188 2.07188 2.07188 2.23125 2.23125 2.39063 2.39063 1.43438 1.275
room hire 9.35 10.2 11.05 11.05 11.05 11.05 11.9 11.9 12.75 12.75 7.65 6.8
Special event 2.55 2.55 2.55 2.55 2.55 2.55
Total variable
cost 113.953 126.863 134.672 137.222 134.672 137.222 145.031 147.581 155.391 157.941 93.2344 85.425
Contribution 13292.3 14498.1 15709.1 15706.5 15709.1 15706.5 16917.5 16914.9 18125.9 18123.3 10875.5 9664.58

Less: fixed cost
Premises Rental 6000 6000 6000 6120
Local Council
rates 6000
Furniture cost 54000
Fitting expenses 2000
Kitchen
equipment
expenses 500
Rental office
equipment 4000 4000 4000 4000 4000 4000 4000 4000 4000 4000 4000 4200
Air Filter cost 5000
Activation fees 100
Communications
cost 600 600 600 600 600 600 600 600 600 600 600 609
Staffing cost:
8 am to 11 am 2448 2448 2652 2244 2652 2550 2652 2809.08 2601 2705.04 2705.04 2601
11 am to 7 pm 13056 13056 14144 11968 14144 13600 14144
14981.7
6 13872
14426.8
8 14426.88 13872
7 pm to 8 pm 816 816 884 748 884 850 884 936.36 867 901.68 901.68 867
Cleaning cost 800 800 800 800 800 800 800 800 800 800 800 800
Supplies
expenses:
Refreshment 290 320 340 350 360 380 390 390 410 410 250 240
hand sanitizer,
face mask etc. 8.64 43.2 43.92 45.36 41.04 46.44 45.36 47.16 47.88 45.36 46.44 45
Insurance cost 450 450 450 450 450 450 450 450 450 450 450 460.125

Utility cost 197 200 200 200 200 200 200 200 200 200 200 200
Administration
and Clerical
service 180 180 180 180 180 180 180 180 180 180 180 180
Special Event
cost:
Guest speaker
expenses 250 250 250 250 250 250
Refreshment 500 500 500 500 500 500
Advertisement 50 50 50 50 50 50
Ongoing
marketing 200 200 200 200 200 200 200 200 200 200 200 200
Membership
cost 1000 1000 1000 1000
Professional
membership 1000 1000
Contribution to
Mr Lopez 2500 2500 2500 2500 2500 2500 2500 2500 2500 2500 2500 2500
Drawing by Mr
Lopez 30000
Depreciation 833.33 833.33 833.33 833.33 833.33 833.33 833.33 833.33 833.33 833.33 833.33 833.33
tax 108.94 108.94 108.94 108.94 108.94 108.94 108.94 108.94 108.94 108.94 108.94 108.94
total fixed cost
32087.9
1
83355.4
7
34936.1
9
26027.6
3
27953.3
1
36098.7
1
27987.6
3
29836.6
3 34670.15
35161.2
3 58202.31 36636.4
BEP 0.41 0.17 0.45 0.60 0.56 0.44 0.60 0.57 0.52 0.52 0.19 0.26

Margin of safety
Formula
[(current sales level - BEP)/ Current sales level] * 100
Particular January February March April May June July August September October November December
flexible
Margin
of safety 99.994 99.999 99.994 99.992 99.991 99.995 99.991 99.992 99.994 99.994 99.998 99.995
current
sales
level 12825 14250 14962.5 15675 16387.5 17812.5 17812.5 17812.5 18525 18525 11400 11400
BEP 0.77 0.21 0.83 1.32 1.48 0.89 1.61 1.50 1.02 1.03 0.28 0.57
dedicated
Margin
of safety 99.994 99.999 99.994 99.992 99.991 99.995 99.991 99.992 99.994 99.994 99.998 99.995
current
sales
level 13406.3 14625 15843.8 15843.8 15843.8 15843.8 17062.5 17062.5 18281.3 18281.3 10968.8 9750
BEP 0.81 0.21 0.88 1.34 1.43 0.79 1.54 1.43 1.01 1.02 0.27 0.48
Total number of monthly membership
Flexible
desk
Particular January February March April May June July August September October November December
desired
profit 1166.67 1166.67 1166.67 1166.67 1166.67 1166.67 1166.67 1166.67 1166.67 1166.67 1166.67 1166.67
Fixed cost 16447.9 67715.5 17936.2 11747.6 10953.3 19778.7 10987.6 11803 18023.8 17821.2 40862.3 19990

Contribution 12716 14128.9 14835.3 15541.8 16248.2 17661.1 17661.1 17661.1 18367.5 18367.5 11303.1 11303.1
Units to
earn profit
of 14000
1.39 4.88 1.29 0.83 0.75 1.19 0.69 0.73 1.04 1.03 3.72 1.87
Dedicated
desk
desired
profit 1166.67 1166.67 1166.67 1166.67 1166.67 1166.67 1166.67 1166.67 1166.67 1166.67 1166.67 1166.67
Fixed cost 16447.9 67715.5 17936.2 11747.6 10953.3 19778.7 10987.6 11803 18023.8 17821.2 40862.3 19990
Contribution 13292.3 14498.1 15709.1 15706.5 15709.1 15706.5 16917.5 16914.9 18125.9 18123.3 10875.5 9664.58
Units to
earn profit
of 14000
1.33 4.75 1.22 0.82 0.77 1.33 0.72 0.77 1.06 1.05 3.86 2.19
Part B
1. Weighted average cost of capital
Sources of funds Funds Proportiuon of
funds
Cost of capital WACC
Bank loan 3000000 0.07 2.45 0.17

5% Coupon bond 14060000 0.33 4.09 1.34
Common stock 25800000 0.6 8.74 5.26
Total 42860000 1 6.77
2. Net present value method
Details JD ZP
Small Large Small Large
Cost 664950 1041950 516615 515250
Consumables 800 1250 270 600
Training 3000 2000 2100 1400
Rental 3900 7200 2160 3960
Salaries 2142420 1428280 2142420 1428280
Present value of annual basis
cost JD ZP
Consumable and rental Small Large Small Large
1
0.936504963
5
880.314665
6677
1582.69338
8275
758.569020
4158
1423.48754
4484

2
0.876877306
6
862.671894
2584
1551.63439
40743
744.205770
1333
1372.83911
12533
3 0.82119995
811.956523
3774
1521.53979
73861
730.277512
7491
1369.33449
26506
4
0.769057829
2
791.894232
3754
1492.09658
37428
5
0.720226474
2
745.277535
5141
1217.18274
14685
4092.11485
11931
7365.14690
49466
2233.05230
32982
4165.66114
83879
Total cost of the project as per
NPV
Details JD ZP
Small Large Small Large
Cost 664950 1041950 516615 515250
Training 3000 2000 2100 1400
Consumable and rental 4092.12 7365.15 2233.05 4165.66
Salaries 2142420 1428280 2142420 1428280
Total cost of the project as per 2814462.12 1051315.15 2663368.05 1949095.66

NPV
Part C