The main purpose of using financial model is to analyse and understand the financial situation of business for decision-making. .Finance needs various calculations to get precise information. There are different types of user e.g. managers and owners need the financial model to evaluate the risk and return to make business decisions for the smooth operation, Individual investors make logical investment decisions – ‘’Risk aversion’’ and etc.(Wild, Subramanyam and Halsey 2009,34) Excel provided many functions and tools to analysis statistical data, it assists those users to make their financial decision such as evaluating the risk and return by mean, variance with using excel function((Packages 2006,1)) . In this report, it examines general concepts of model creation, then discusses the portfolio theory and efficient frontier applications
General Concepts of creating a model
• The model must be objectives driven as the model assisting decision-making; its allowed sensitivities and structure should be aligned with the decisions which will be taken with it. (DMUU Training Team 2009)
• For minimizing error, Error-checks system should be included in. For example, there are different ways to calculate the same quantity and the difference between those calculations that should always be zero could be formulated as an output. Excel DataTables can be applied to check whether the error is zero under a board range of input scenarios. when using DataTables, the cell with any non-zero values can be highlighted by using conditional formatting(DMUU Training Team 2009)
• Calculation should be related to the purpose.
• Sufficient documentation should be provided in the model, such as main restriction or limitation on the logic and key assumptions.
• In the model, logical flow should be clear (usually top-to-bottom ,left-to-right), with no “mixed” formulae, every numerical quantity should be either a number or a calculation . The time unit should
References: Wild,John J,K.R.Subramanyam,Robert F.Halsey.2009.Financial statement analysis.Boston: McGraw-Hill Irwin Benninga,Simon.2006.Principles of finance with Excel.NewYork:Oxford University press Packages.2006.Statistical Software. Stat 5969 Statistical Software Packages: 1–23.http://home.business.utah.edu/mgtdgw/stat5969/Analysis_Tools.pdf DMUU Training Team.2009. Some Best Practice Principles in Excel Modelling. http://www.excelgoodies.com/Finance_Excel_Guru.html Financial Modeling Guide.2007. Financial Modeling Discipline – Guiding Principles. http://www.financialmodelingguide.com/modeling-discipline/financial-modeling-discipline/ Spencer,Philip.1997. Applications of the Geometric Mean:1.University of Toronto. http://www.math.toronto.edu/mathnet/questionCorner/geomean.html