Successfully building a financial model can be a complex process. This article shows you the main steps you should follow.
What is Financial Modelling?
While it may seem self-explanatory, it is a good idea to clarify: What is "Financial Modelling"?
Christmas Special: The Temperature at the North Pole
Christmas is coming up, and millions of children around the world want to know: How cold is it really at the North Pole, where Santa Claus reportedly resides?
First off, where do we get the temperature from? The North Pole Environmental Observatory (NPEO) is operating floating bouys in the Arctic region and publishes temperature data on that page. The idea is to have a VBA macro that loads the page content and looks for temperature for a given buoy.
You can find the macro below. Here is a short description of the VBA code
- First, it creates a HTTP object. HTTP objects can be used to obtain content online, in this case the NPEO's web page. In fact, the whole page's HTML code will be downloaded and stored in a string that we can handle.
- The code then searches for the temperature information in that string. The NPEO publishes temperature data in Celsius; the macro also converts it to Fahrenheit, so that we have both. The conversion formula can be found on the Wikipedia page.
- A message box then shows the temperature to the user.
...and that's already all you need. The tricky part is that the code relies on having the buoys name/code as a hard value. For a long time, as still included in the VBA code, the buoy closest to the North Pole was EUMETNET ICEB Buoy 409520. Later, one had to go for IABP PAWS Buoy 975420. Check the page and adjust the code accordingly.
Happy Holidays!
Sub GetNorthPoleWeather() ' Retrieves the temperature of the EUMETNET ICEB Buoy 409520. ' This buoy is floating close to, but not necessarily directly at the actual North Pole. ' Data is retrieved from http://psc.apl.washington.edu/northpole/ Dim strURL As String, strHTTPResponse As String, strBufC As String, strBufF As String Dim iPos1 As Integer, iPos2 As Integer, iLength As Integer ' Create the HTTP object and send the request so we can retrieve the web page with the information we need. Set objHTTP = CreateObject("MSXML2.XMLHTTP") strURL = "http://psc.apl.washington.edu/northpole/" objHTTP.Open "GET", strURL, False objHTTP.Send strHTTPResponse = objHTTP.responseText ' store the HTTP response ' Use Mid and InStr to only look at the relevant part of the response, i.e. after the text "EUMETNET ICEB Buoy 409520". ' (A more elegant, but more complex way to solve this would be regular expressions. We will go for the simple way.) iPos1 = InStr(1, strHTTPResponse, "EUMETNET ICEB Buoy 409520") strBufC = Mid(strHTTPResponse, iPos1) ' In the remaining string, the temperature value is between "°C " on the right and the first ";" to the left of that. ' We will set iPos1 and iPos2 to cover that section. iPos2 = InStr(1, strBufC, "°C ") iPos1 = InStrRev(strBufC, ";", iPos2) + 1 strBufC = Mid(strBufC, iPos1, (iPos2 - iPos1)) ' strBuf is now only the temperature value ' Finally, convert the Celsius value to Fahrenheit, so we can show both strBufF = Format(CelsiusToFahrenheit(CDbl(strBufC)), "#.0") ' Set the text of the shape to show the current temperature Sheets("Calendar").Shapes("tb_Temperature").TextFrame.Characters.Text = strBufC & " °C" & vbNewLine & strBufF & " °F" End Sub Sub ShowWeatherInfo() frmNorthPole.Show End Sub Private Function CelsiusToFahrenheit(dCelsius As Double) As Double CelsiusToFahrenheit = (dCelsius * 1.8) + 32 End Function
Countdown in Excel
You want your Excel spreadsheet to count down the days, hours, minutes and even seconds up to a special point in time? All it takes is one formula, one short VBA macro and a button. Continue reading "Countdown in Excel"
Excel for Gaming? Why not!
To most people, Excel is the work tool that it is supposed to be. But not to everyone. Cary Walkin, a Chartered Accountant and Excel power-user, has built an Excel-based role-playing game: Arena.Xlsm. It is an impressive demonstration that entertainment is indeed possible with spreadsheets. Continue reading "Excel for Gaming? Why not!"
Applying for a Finance Job
To land a good job, professional application documents are necessary. A typical application consists of a cover letter and a CV (curriculum vitae, sometimes called résumé). Often, you also want to include reference letters or letters of recommendation.
Book: Mastering Financial Modelling in Microsoft Excel
One little miscalculation – and the Spanish Navy ends up with an overpriced submarine that is too heavy to be put in use. Excel can sometimes be more of a curse than a gift, but Alastair L. Day seeks to help with “Mastering Financial Modelling in Microsoft Excel” – with numerous field-tested methods and recipes for everyday problems in corporate finance.
Continue reading "Book: Mastering Financial Modelling in Microsoft Excel"
Book: Using Excel for Business Analysis by Danielle Stein Fairhurst
Excel is a useful tool for a variety of business problems. But sensible, structured and error-free solutions not only require patience and dedication, they also require solid Excel skills. In "Using Excel for Business Analysis", Danielle Stein Fairhurst lays the groundwork for a more professional use of the spreadsheet software.
Continue reading "Book: Using Excel for Business Analysis by Danielle Stein Fairhurst"
Book: Building Financial Models with Microsoft Excel by K. Scott Proctor
The term “Financial Modelling” usually means integrated planning of a company’s financial outlook – in practice, this is mostly done with Microsoft Excel. This book by K. Scott Proctor is a good introduction, but requires the reader to know quite a bit about accounting.
Continue reading "Book: Building Financial Models with Microsoft Excel by K. Scott Proctor"
Common Size Financial Statements
It is often hard to compare the financials of companies that are different in size. Common size financial statements are one way to make them comparable. This guest article explains how to common-size a P&L statement.
Book: Excel Modeling in Corporate Finance by Craig W. Holden
Excel, the tool of opportunity: It is the software of choice for most finance professionals, and used for countless every-day problems. In “Excel Modeling in Corporate Finance”, Craig W. Holden presents numerous typical issues from DCF calculations to options valuations.
Continue reading "Book: Excel Modeling in Corporate Finance by Craig W. Holden"
Revenue Forecasting for Social and Mobile Gaming Companies
The social and mobile gaming industry is a sector with strong growth that attracts more and more established companies as well as start-up firms. Reliable sales forecasts are necessary to find investors. However, forecasting in a rapid growth industry like gaming can be a tricky affair.
Continue reading "Revenue Forecasting for Social and Mobile Gaming Companies"