IF function resulted wrong and imbalance answer

[neylameys]

indv assignment.xlsx
	A	B	C	D	E	F	G	H	I
29
30		FGV HOLDINGS BERHAD
31		Proforma Consolidated Statements of Profit or Loss and Others Comprehensive Income
32		For the Financial Year Ended 31 December
33			2023	2024	2025	2026	2027	2028
34			RM	RM	RM	RM	RM	RM
35		Non-Current Assets
36		Investment property	66,074	67,292	68,792	70,235	71,793	73,291
37		Property, Plant & Equipment	7,908,289	8,054,106	8,233,622	8,406,295	8,592,762	8,772,112
38		Intagible Assets	889,593	905,996	926,189	945,613	966,588	986,763
39		Interest in associates	651,683	663,699	678,492	692,721	708,087	722,866
40		Other non-current assets	218,560	222,590	227,551	232,323	237,477	242,433
41		Receivables & Loans -long term	159,511	162,452	166,073	169,556	173,317	176,934
42		Deferred tax assets	242,859	247,337	252,850	258,152	263,879	269,387
43		Right of use assets	2,195,949	2,236,439	2,286,286	2,334,234	2,386,011	2,435,813
44		Total non-current assets	12,332,518	12,559,911	12,839,855	13,109,130	13,399,913	13,679,600
45
46		Current Assets
47		Inventories	1,691,998	1,723,196	1,761,604	1,798,548	1,838,443	1,876,815
48		Cash & Short Term Investment	1,613,091	1,613,091	1,613,091	1,613,091	1,613,091	1,613,091
49		Derivatives Financial Instrument-Hedging	11,935	12,155	12,426	12,687	12,968	13,239
50		Prepaid Expenses	48,409	49,302	50,400	51,457	52,599	53,697
51		Loans & receivables	1,540,114	1,568,511	1,603,471	1,637,099	1,673,413	1,708,341
52		Other current assets	44,688	45,512	46,526	47,502	48,556	49,569
53		Total current assets	4,950,235	5,011,767	5,087,519	5,160,384	5,239,069	5,314,752
54		Total assets	17,282,753	17,571,678	17,927,375	18,269,514	18,638,983	18,994,352
55
56		Current liabilities
57		Trade account payables & Accruals	265,328	270,220	276,243	282,036	288,292	294,310
58		Borrowings	2,269,445	295,939	2,534,367	506,726	2,825,038	725,789
59		Lease liabilities	30,637	3,995	34,213	6,841	38,137	9,798
60		Derivative financial liabilities	403	410	420	428	438	447
61		Income tax payable	12,570	12,802	13,087	13,362	13,658	13,943
62		Other current liabilities	1,663,023	1,693,687	1,731,437	1,767,748	1,806,960	1,844,675
63		Total current liabilities	4,241,406	2,277,053	4,589,767	2,577,141	4,972,524	2,888,962
64
65		Non-current Liabilities
66		Borrowing	1,163,357	151,703	1,299,161	259,757	1,448,164	372,052
67		lease liabilities	351,888	45,887	392,965	78,570	438,035	112,537
68		Derivative financial liabilities	11	11	11	12	12	12
69		Deferred tax & investment tax credits	591,523	602,430	615,857	628,773	642,720	656,135
70		Other non-current liabilities	3,352,588	3,414,405	3,490,507	3,563,709	3,642,759	3,718,791
71		Total non-current liabilities	5,459,367	4,214,436	5,798,502	4,530,821	6,171,690	4,859,528
72		Total liabilities	9,700,773	6,491,488	10,388,269	7,107,962	11,144,213	7,748,490
73
74		Equity
75		Common equity	7,029,889	7,029,889	7,029,889	7,029,889	7,029,889	7,029,889
76		Retained earnings	-1,196,629	2,301,580	(1,239,504)	2,382,943	(1,283,840)	2,467,252
77		Comprehensive Income	116,727	116,727	116,727	116,727	116,727	116,727
78		Other reserves/equity	21,928	21,928	21,928	21,928	21,928	21,928
79		Minority interest	1,610,065	1,610,065	1,610,065	1,610,065	1,610,065	1,610,065
80		Total equity	7,581,980	11,080,189	7,539,105	11,161,552	7,494,769	11,245,861
81		Total liabilities & equity	17,282,753	17,571,678	17,927,375	18,269,514	18,638,983	18,994,352
82
83		#1 Minimum cash balance policy	1,613,091
84
85			2022	2023	2024	2025	2026	2027
86		ROA	7.54%	9.08%	8.56%	9.04%	8.52%	-5370.86%
87
88		Internal growth rate (IGR)	1.84%	2.23%	2.10%	2.22%	2.09%	-92.80%
89
90
91
92
IGR

Cell Formulas
Range	Formula
D36:H36	D36	=D7*$L$23
D37:H37	D37	=D7*$L$24
D38:H38	D38	=D7*$L$25
D39:H39	D39	=D7*$L$26
D40:H40	D40	=D7*$L$27
D41:H41	D41	=D7*$L$28
D42:H42	D42	=D7*$L$29
C43	C43	=2195949
D43:H43	D43	=D7*$L$30
C44:H44	C44	=SUM(C36:C43)
D47:H47	D47	=D7*$L$31
D48:H48	D48	=$C$48
D49:H49	D49	=D7*$L$32
D50:H50	D50	=D7*$L$33
D51:H51	D51	=D7*$L$34
D52:H52	D52	=D7*$L$35
C53:H53,C63:H63	C53	=SUM(C47:C52)
C54:H54	C54	=C44+C53
D57:H57	D57	=D7*$L$36
D58:H58	D58	=IF((($C$83+D44+D47+D49+D50+D51+D52-D57-D60-D61-D62-D68-D69-D70-D80)*($C$58/($C$58+$C$59+$C$66+$C$67)))>0,(($C$83+D44+D47+D49+D50+D51+D52-D57-D60-D61-D62-D68-D69-D70-D80)*($C$58/($C$58+$C$59+$C$66+$C$67))),0)
D59:H59	D59	=IF((($C$83+D44+D47+D49+D50+D51+D52-D57-D60-D61-D62-D68-D69-D70-D80)*($C$59/($C$58+$C$59+$C$66+$C$67)))>0,(($C$83+D44+D47+D49+D50+D51+D52-D57-D60-D61-D62-D68-D69-D70-D80)*($C$59/($C$58+$C$59+$C$66+$C$67))),0)
D60:H60	D60	=D7*$L$37
D61:H61	D61	=D7*$L$38
D62:H62	D62	=D7*$L$39
D66:H66	D66	=IF((($C$83+D44+D47+D49+D50+D51+D52-D57-D60-D61-D62-D68-D69-D70-D80)*($C$66/($C$58+$C$59+$C$66+$C$67)))>0,(($C$83+D44+D47+D49+D50+D51+D52-D57-D60-D61-D62-D68-D69-D70-D80)*($C$66/($C$58+$C$59+$C$66+$C$67))),0)
D67:H67	D67	=IF((($C$83+D44+D47+D49+D50+D51+D52-D57-D60-D61-D62-D68-D69-D70-D80)*($C$67/($C$58+$C$59+$C$66+$C$67)))>0,(($C$83+D44+D47+D49+D50+D51+D52-D57-D60-D61-D62-D68-D69-D70-D80)*($C$67/($C$58+$C$59+$C$66+$C$67))),0)
D68:H68	D68	=D7*$L$40
D69:H69	D69	=D7*$L$41
D70:H70	D70	=D7*$L$42
C71:H71,C80:H80	C71	=SUM(C66:C70)
C72:H72,C81:H81	C72	=C63+C71
D75:H75	D75	=$C$75
D76:H76	D76	=-C76+D25
D77:H77	D77	=$C$77
D78:H78	D78	=$C$78
D79:H79	D79	=$C$79
C86:G86	C86	=C23/C54
H86	H86	=H24/H59
C88:H88	C88	=(C86*$L$15)/(1-(C86*$L$15))

 

[Scott Huish]

Can you tell us what is wrong and what you expected and why?

 

[neylameys]

Can you tell us what is wrong and what you expected and why?

I need to do proforma financial position for 5 years. I have calculated all the ratio and plug in the ratio in the amount. Im using the IF function it resulted fluctuating answer , when i compared it to the sample and my friends sheet, should be it decreasing gradually for borrowing and lease liabilities.

 

[neylameys]

I need to do proforma financial position for 5 years. I have calculated all the ratio and plug in the ratio in the amount. Im using the IF function it resulted fluctuating answer , when i compared it to the sample and my friends sheet, should be it decreasing gradually for borrowing and lease liabilities.

What i meant here is all the calculation that using simple ratios doesnt show any problem maybe because it's not complicated as compared to IF function