Financial Simulation Modeling In Excel A Step-By-Step: Fill & Download for Free

Open a trading account (this could take a while, so start with this first)Pick a sector of your interest.Pick 4 firms in that sector, print their annual reports and read everything from A to Z.Whatever you don't understand, mark it with a coloured pen, and write down in a notepad.After you finished reviewing all 4 firms, go on the internet and look up definitions for all bits you didn't understand. Given you have 4 similar sector firms, there should be overlap.Then go in Microsoft excel, and write down a bunch of their metrics you found in their annual report. Given they are same sector firms, you expect similar metrics. Earnings per share, debt, assets, market cap, goodwill, profit margin, turnover. Whatever is also listed in the annual report. Nothing from the internet. When you finished this, you have a bunch of financial metrics on your left, and from B to E in excel your four firms. You've done a comparable company analysis (CCA), often used in firms.Pick a few metrics you have an interest in for all 4 firms. Get historical data of these metrics.Go to Yahoo or Bloomberg, and download historical data of the share price of the 4 firms.If you picked the airline sector, get additional variables related to that, like amount of passengers flown in Europe or US or even worldwide. Or if it is a mining firm, add the history of the goldprice.Pick a statistical tool, MATLAB, R, Eviews, excel, and setup a simplistic regression. Y being share price, your X's being the fundamental metrics you found in the annual report as well as the additional variables.Run the regression and investigate the results. You are checking how much your X's explain Y. You'll probably see a bunch of definitions you've never heard of, R-squared, adjusted R-squared and so forth.Pick up a econometrics book, and understand what these things mean. R-squared, heteroscedasticity, auto correlation, adjusting for biases in data, and so forth.Follow the lessons you've learned, and do another regression analysis, you'll find you'll be able to increase your adjusted R-squared. You think you found a better result. You’re wrong, even though the results indicate a better result, all you did was data dredging. Making data fit better. That's how a “sales” career starts.Setup a automatic trading model (your broker probably has one or try ninja trader or create something yourself) which buys or sells the stock (one of the 4 you picked) based on when your variables are introduced/published again. These are quarterly or monthly announcements. Run a simulation.You'll realise your initial regression didn't include for volume nor for fees. You'll also realise when you automate, timing is of the essence because your bid/ask changes constantly.Most likely your results will be shit.I assume you want more frequent signals. The previous signals were only monthly or quarterly. Check how RSI (a shit technical indicator) is calculated. Add this formula in the API trading model, and set a signal of buying at RSI 30 and sell at RSI 70. You'll get shitloads of buy sell signals, and once again, your results will be shit. With some regression analysis, you'll find out that 30–70 are not the ideal fit, but it's more something like 25–80. However you've done this based on historical regression (which just tells the past, so you have no statistical ground to use it for the future…).So pick up another Econometrics book and look at how you can create distributions so you can sample data out of it. Create a normal and a non-normal distribution. Learn how to do a bootstrap (calculate extra data points if you don't have much historical data). For both the non normal and normal distribution, you sample years of data. Now you have “future” data to validate your model.So far, you've tested a “trading model with fundamental and technical indicators”, based on historical data, now test it on sampled data for a proper forecast. You'll realise the result of the normal vs the non-normal distribution will be significantly different. Think.. what do you think fits better?These 19 steps are BASIC understanding of financial analysis, comparable company analysis, simple fundamental and technical analysis, as well as regression analysis and how to manipulate data.These steps do not exceed first year BSc level, unfortunately many students or grads I see skip all this and straight from blank want to put machine learning algorithms together without basic understanding of economics, finance and econometrics.Start with the basics, regardless of how boring this is. You'll never forget your foundation.You want to be good in Finance? You must be able to fully understand an annual report of a firm, its macro and micro impact, as well as its maths behind it. Being able to actively challenge an accountant on its figures and pure quant on its models.This is how you stand out in a crowd, and how you'll be different than everyone else.

You are a graduate, so you are relatively ‘fresh’. I’ll pen down a few steps what I did before I was a graduate. Starting from the beginning is the most important, as most wannabe quants or traders, want to go straight to the action and develop the most advanced models without having a proper basis.The below applies mostly to market risk FO quant. On top of that I believe focus on underlying, as to, realiziation of what it is you are doing matters most to become “good”I’ve given answers like these before, but I’ll try to be more specific this time. These are the basics, slowly building up to more advanced work.Setup a trading account, one with API trading abilities.(1) Annual reports: In my view it all starts with the annual report of a firm. Pick a firm of your interest. Within this same industry, pick another 4. So if you pick Delta Air Lines, pick easyJet, Lufthansa and so forth.(2) Financial Metrics: Read all the annual reports from beginning to end. Whatever you don’t understand, mark it with a yellow marker and write it down in a notepad. When you finished, you must have found a lot of definitions or ‘business jargon’ you’ve never heard of. Go to Google, and look it all up, write down the definitions of these metrics in your notepad. Write down ALL!(2 - A) So for airlines, you wrote down specific metrics such as CASK (cost per available seat kilometer)(2 - B) While for mortgage related companies you’ll learn about LTV, DSCR and so forth.(2 - C) In the beginning you’ll probably won’t understand fuck all at most. That’s fine. You have to start somewhere. I remember the first time I’ve read an annual report, I looked up nearly everything and felt like a complete fucktard that I didn’t understand what was written.. but that was exciting! Much to learn!(2-D) You should dial in to some of the conference calls where investors grill the CFO who read the results during the conference call. You develop a nose for bullshit when investors ask good questions and a CFO provides a usual bull-shit answer.. You’ll develop a nose for bull-shit. This is an important skill you’ll build further over the years.(3) - CCA Pick a bunch of metrics and statistics (like region and market cap) which are commonly shared among all these firms. Open Microsoft Excel and put all the metrics in the columns and the 5 firms on rows. Write it all down.(3- A) You’ve done your first Comparable Company Analysis (CCA). When you start as investment banker graduate, you’ll end up doing a whole bunch of CCA’s. From there you’ll have to put it in PowerPoint and make pitch books for your boss (and so forth…..)(3- B) The metrics you’ve chosen for your CCA, try to get historical information of this. Also obtain share price history of the 5 firms.(3- C) Use Bloomberg, Reuters, Yahoo Finance and so forth.(4) Eviews/Matbab Open a tool like Eviews, Matlab or even excel, and setup a regression. A regression is nothing else but looking at X-variables, so called independent variables, trying to explain Y-variables, so called dependent variables. Like Y = a = bX, where X is your ‘explaining variable’ - like revenue or net income and Y is the historical share price data.(4- A) Run the regression, you’ll get results in Eviews like ‘R-Squared, Adjusted R-Squared and so forth’ like the below screenshot. What you seek is how much a variable like revenue (x) explains share price movements (y).(4-B). Get an Econometrics book like “Applied Econometrics” by Hall and repeat the initial exercise you’ve done for the annual report. Look up the definitions (like in table above) and read/understand what it means. R-Squared, T-statistic, Adjusted R-Squared and so forth.(4-C) When you’ve read up on this, re-do the regression. You’ll be able, with tweaking, to up your ‘adjusted R-squared’ score. You think your results are better, given it seems you have found better explanatory variables. Maybe a R-squared of 80%. Wow, your variable explains 80% of your Y. You Rock! Wrong, you were data dredging All you did was make data ‘fit’ better.(This is how a sales career starts… if you hate all of this, with minimal mathematics skills you can make a lot of money in pre-sales)Plus - all you’ve done so far is comparing historical data with historical variables - and with mathematical ways to fit the data better and better. If you start university, you’ll get a lot of these exercises which ultimately end up in just ‘data fitting’. Absolutely fucking useless. Thus back to the drawing board.(5-C) EconometricsPick up another econometrics book. You were fitting data to get a better R-Squared figure. Now look up definitions such as heteroscedasticity, auto-correlation, data-biases and so forth.does it make sense to use 25 variables to explain 1 variable, or would you prefer 5? Think…can you just use 2 data sources in 1 model? Would you need to ‘adjust’ your data for a certain bias to make it applicable? Think - look it up! Hedge fund data often has biases for example.(6) Setup a fundamental trading modelTry through your broker, or through tools like Ninjatrader and setup a trading model. You’ve had a bunch of ‘economic’ variables in your regression which got high R-squared scores. Use these, maybe like a revenue variable and setup a signal to buy one of the 5 stocks if a new revenue number of the firm get’s announced during the quarterly conference call. And if this grows, go long, if not, go short, given you found that revenue was explaining share price movements - run the simulation and see how it performs.You’re results will suck dick, ha. Good. You’ve realized by now that regression analysis was a good way for you to understand fundamental analysis and it’s potential impact on share price movement, but there is so much more involved. Data biases you have to correct for, as well as impact of volume, transaction costs, bid-ask spreads, and so forth. Pick up another book and read up about this.(7) Setup a technical trading modelMany macro or fundamental models don’t have many buy/sell signals. You might find it boring. So look at some technical indicators. Like MACD or RSI. Model RSI in an excel spreadsheet. InvestExcel has all this shit for free. Model this RSI in your trading model and put a buy signal when it hits 30, and a sell signal when it hits 70 - run the simulation.Your results will suck. Awesome. You’ll realize that standard technical analysis is rubbish. Every firm has a ‘ideal RSI’ setup, some have 25 - 85, some have 40 - 50. Utter fucking rubbish, but that realization is good for understanding. You’ll find the same with all other technical indicators.Think again, if you do a regression with Y = share price, and your X’s are a bunch of technical indicators, would you use 10 technical indicators to explain your share price or just 1? What’s the impact of using 10? These are all solid interview questions.(8A) BootstrapYou’ve worked with ‘historical’ data, and you were simulating based on historical data. Now let’s focus on obtaining/sampling much more data, you can do that through a bootstrap. I’ve got a simple Matlab script below who does something like that for you..%Bootstrap Ross %First read your stock data Data=xlsread('yourdatafilewithstockinfo.xls','sheet1','B1:H300');   %Initialize (change Sample Size) Samples = 10; Percentage_equities = Data(:,1); Percentage_bonds = Data(:,2); Percentage_yields = Data(:,3);   %Distribution Prob AmountRandom = round(1+(size(Percentage_equities,1)-1)*rand(Samples,1));   For i = 1:1  AmountRandomN=round(1+size(percentage_equities,1)-1*rand(Samples,1));  for J = 1:Samples  Bootstrap_equities(j,i)= Percentage_equities(AmountRandomN(j,1),1);  Bootstrap_bonds(j,i)= Percentage_bonds(AmountRandomN(j,1),1);  Bootstrap_highyield(j,i)= Percentage_yields(AmountRandomN(j,1),1); End End (8B) DistributionsNow, when you were doing your regression, you were assuming multivariate normality. Shit like that ain’t really how it works unfortunately .Pick up another econometrics and Bayesian Mathematics book and start reading about distributions. Start with the simple onesnormal distributionsand not normal distributionsRead how you can sample out of distributions (like MCMC and so forth).Delve a bit deeper. Look up an inverted wishart distribution for example. See how you can use such distributions in your advantage. Then formulae like the below (pure basics) will become your bread and butterI know too many students who get worried when they see formula’s which seemingly make no sense - don’t let that stop you. Disect something like that 1 by 1. Financial mathematics is truly easy. I like the book: Financial Calculus: An Introduction to Derivative Pricing: This will teach you the SDE pricing of derivatives. Brownion, Euler, CMG. In case you are becoming a pricing quant.(9) Asset AllocationYou want to be able to balance ‘risk’ and ‘reward’. Asset allocation is such a strategy. You want 10% in risky assets, 25% in non-risky, 40% in bank deposits and so forth.Read Garlappi’s academic paper (optimal versus naive diversification) - link below:http://faculty.london.edu/avmiguel/DeMiguel-Garlappi-Uppal-RFS.pdfThis is the best BASIC paper on introducing a whole bunch of asset allocation models based on a variety of mathematical theories.In university you’ll probably end up having to learn fucktards of utility functions. This is fucking garbage. I don’t give a fuck what others say when it comes to this. I’ve had to deal often with universities who wanted their students to do their master thesis during an internship - while programming an asset allocation yet having to link it to utility functions….You’ve read by now also upon Bayesian Mathematics, read about the black-litterman model. See what Bayesian maths can learn you. Think, what is the advantage of using Bayesian mathematics in quantitative finance? It’s a interview questions i’ve often seen.(10) Risk adjusted PerformanceYou’ve done fundamental and technical analysis, as well as how to use it in a trading model. You’ve now also looked how to balance risk and reward in portfolio’s.So how can we measure such performance?At university you’ll learn fucktard metrics like sharpe ratio.The core of true risk management is that it’s much more than obtaining a figure out a of formula, it’s about interpreting such a figure. Risk is not one or two-dimension. Risk has much more dimensions. Mean, variance, skewness, kurtosis and more.Understand the basiscs of VaR, Conditional VaR, Expected Shortfall, as well as metrics who focus on the first, second, third and fourth moment of distribution. Write down their formula in MATLAB. Play with it.Like a polynomial goal programming algorithm on the 4 moments of distribution. It’s a very solid easy to start with trading model when you start with building algorithms. Easy to model and great results. When you rank it on performance..Think about metrics such a Stutzer, Omega Ratio, DrawdownForget about dogshit like Sortino and Sharpe Ratio’s. Not worth the paper it's written on.Create your OWN risk-adjusted performance.Go back to one of the trading models you’ve created, and get (sample or approximate) their P&L data.Model a VaR framework on 99% and see if your trading model was applicable to your VaR model (were P&L losses greater than your VaR). If your VaR model at 99% said you could lose 5,000 dollars every day, yet, you lost 25,000 every day, you’re VaR model sucks dick, and who knows, your trading model might make no fucking sense at all either. It means, you had an “back testing exception” as we say it in regulatory terms.(11) Regulatory workWhen you work in England for example, you’ll ultimately have to deal with regulatory affairs. Such as the PRA. When you work as a market risk quant or analyst, you’ll deal with how the regulator checks your firm’s VaR model. This is specified in a so called “IMA WAIVER” - see below an example for HSBChttps://register.fca.org.uk/servlet/servlet.FileDownload?file=00Pb000000DldPBEAZread article CRR 363 of the EBA book.read supervisory statement SS13 (below)https://www.bankofengland.co.uk/-/media/boe/files/prudential-regulation/supervisory-statement/2017/ss1313update.pdf?la=en&hash=70B1FC1D93C97A9F7B88FF51E35517C7CBD4CAF2Such articles give you already an idea of what kind of work or requests might come your way as graduate. This is very solid information. 99 out of a 100 graduates don't do this due diligence before an interview. These documents are freely available:The above tells you, that these large UK banks provide this sort of information to the PRA every quarter.Changes to the VaR model, VaR model performance and so forth. You can prepare for this already!When you model your trading algorithm - and link a risk adjusted performance algorithm on it, you can already start with approximating whether or not YOUR VaR model is actually sufficient. Because your trading model will poop out P&L figures every day. Your VaR model, at let’s say 99%, will mention a specific value you’d expect to loose. Consider your model an “entity”, with a VaR and approximate P&L. If you have a greater p&l loss than your VaR 10 times out of 250 business days a year, your model sucks dick. If this was a real model for a bank, you could end up with huge fines.Going back to the entities for HSBCwhat that means is that such a entity has a VaR value as well as a P&L value. If you have an ‘exception - aka P&L is fucking worse than your VaR’, you get punished by the PRA. If you get like 10 or 15 exceptions annually, you’ll have the fucking CEO and CRO of the bank (!) grilling your testicles, and having to come up with a proper explanations why we fucked up? (was it the model? was it the trader?)…..You’ll find it in the pillar 3 disclosures (below is a HSBC example)Where you have VaR and P&L depicted.This goes back to the first assignment - read annual reports (given if you would have read the report of HSBC, you would have seen the above). In other words, when you’ve worked on Trading Models, Risk Adjusted Performance Measurement Indicators, and its’ VaR models, you should be able to simply automate something like the above chart. A good test as high school or university student before you start your graduate placement. But how do we do that? Hence we go to the last topic (12)(12*) Business logic - data managementwhatever you do in work as quant, when you are requested to perform some ‘data analysis’ - like VaR impact analysis of a currencies desk, do the following1. Do the analysis as requested..2. Write a simple piece of VBA code who links all this shit together (obtain data from a database - do the analysis)3. Write a macro which is linked to outlook. If you seek on the below, you’ll find scripts on the internet everywhere Set OutApp = CreateObject("Outlook.Application")  Set OutMail = OutApp.CreateItem(0) 4. Open a notepad and write some code which links the macro. Search the internet on the below and you’ll find free scriptsxlApp.Run "TestYouRFuckingMacro" 5. Save your notepad file with 10–15 lines as a .vbs, and put it in a scheduler. Run it at 8AM and use in your email distribution the guy you did the work for. Tell him, he’ll receive that analysis daily going forward at 8AM. He’ll love ya’.The above steps do not exceed first year BSc level at university, and mostly follows ‘common sense’. If you are in high school, you should be able to pick this all up. Step by step. You grow from basics, an amateur, to an expert.The above steps also should help you if you are in university to prepare yourself for an interview. When I ask a student who applies for a risk graduate role in a bank in the UK, i assume he knows what the Greek options are, theta, gamma, and so forth, but also what the regulatory implications of the IMA waiver can be for the bank as a whole.I believe this path makes you extremely versatile, because the skills you require, need to be varied. From technical, to social (conference calls can be funny) and everything in between.For all the steps above, you do not require 1 step inside a university.By learning all of this, you’ll be able to challenge an accountant on it’s figures, an equity analyst focused on airlines on its metrics as well as a currency trader on how he deals with his VaR. You’ll be able to challenge a quant on it’s formulas. This gives you an edge as employee over other employees.

Financial Forecasting, Analysis, and Modelling: A Framework for Long-Term Forecasting (The Wiley Finance Series)Risk analysis has become critical to modern financial planningFinancial Forecasting, Analysis and Modelling provides a complete framework of long-term financial forecasts in a practical and accessible way, helping finance professionals include uncertainty in their planning and budgeting process. With thorough coverage of financial statement simulation models and clear, concise implementation instruction, this book guides readers step-by-step through the entire projection plan development process. Readers learn the tools, techniques, and special considerations that increase accuracy and smooth the workflow, and develop a more robust analysis process that improves financial strategy. The companion website provides a complete operational model that can be customised to develop financial projections or a range of other key financial measures, giving readers an immediately-applicable tool to facilitate effective decision-making.In the aftermath of the recent financial crisis, the need for experienced financial modelling professionals has steadily increased as organisations rush to adjust to economic volatility and uncertainty. This book provides the deeper level of understanding needed to develop stronger financial planning, with techniques tailored to real-life situations.Develop long-term projection plans using ExcelUse appropriate models to develop a more proactive strategyApply risk and uncertainty projections more accuratelyMaster the Excel Scenario Manager, Sensitivity Analysis, Monte Carlo Simulation, and moreRisk plays a larger role in financial planning than ever before, and possible outcomes must be measured before decisions are made. Uncertainty has become a critical component in financial planning, and accuracy demands it be used appropriately. With special focus on uncertainty in modelling and planning, Financial Forecasting, Analysis and Modelling is a comprehensive guide to the mechanics of modern finance.