We can Help on: Model of Company Financial Statements

SOLVED BY  Expert Writers

Guidelines: Model of Company Financial Statements
Target length:
Saturday, March 28, 2020
Spreadsheets Plus Assumptions (no set length)
1 copy, single-sided, PRINTED and uploaded to Canvas
(both Excel and Word docs)
This assignment asks you to 1) complete your model in Excel, and 2) write up a
companion document that walks the reader through your work. You can use
whatever layout you wish in Excel. Just remember that you will have to eventually
copy and paste the data into your final Freeman Report. To submit this assignment,
your team should submit two (2) copies of the companion document in class and
upload the Excel spreadsheet to our class Blackboard site.
The Model:
The financial model for your Company should integrate both historical “actual”
numbers for 2017 through 2019 and forecasted figures for 2020 through 2022. You
must forecast the income statement, statement of cash flows (which builds on
discretionary cash flow), and balance sheet for your Company.
We have had numerous discussions about how your Company’s Press Releases may
consolidate the line items given in the SEC filings. These Press Releases often come
out several days (or more) before the SEC filings are available. For this reason, I
have stressed that your model needs to be flexible enough to allow for updates of
future estimates to be easily based off of the consolidated items given in the Press
Release. The “down side” to this is that the Press Release may give you data that is
very coarse, in that there may be little detail as to what balance sheet or income
statement line items will eventually be reported to the SEC in a 10Q or 10K. Your
clients, however, will want you to give an opinion on the earnings announcement
before the detailed SEC filings are available.
So, you have a decision to make. Should your model be based off of the detailed SEC
data or based of the Press Releases?
For this exercise, you CAN use the historical SEC data from the 10Q and 10Ks to
build your model. This will let you analyze the detailed line items of each statement.
Remember, however, that there is also a debate about what type of models are more
successful… those that get “caught in the weeds” with a lot of detail or those that
attempt to use broader cuts at the data. Your model will only be useful if you can
easily explain it to someone else – namely a client who is not as versed in equity
In the end, you will probably get more out of this exercise if you start with the SEC
data and build on that. Just remember that if you get a job as an analyst, your model
will need to be “update-able” using the press release until the SEC fillings come out…
When complete, you should include the following periods in your model:
 2017 and 2018 Annual Summary (you may want to look at the quarterly
information to look for trends, but only the annual numbers will be displayed
in your Freeman Report)
 2019 Actual Quarterly Data and an Annual Summary
 2020 Estimated Quarterly Data and an Annual Summary
 2021 & 2022 Estimated Annual Data only
The Layout:
In class, we have built a model for Stone Energy using a specific layout to allow our
revenue model to be a function of both production and price assumptions. Much of
this keyed off of “per BOE” elements.
If your team wants to change the layout in any way, you are allowed to do this.
There are an infinite number of ways that these models can be constructed. The key
is that you should be able to easily update the information when the “actual”
numbers are released. As well, your spreadsheet should be conceptually easy to
understand by someone who wasn’t involved in its creation. That is, you should
design your model with the reader in mind.
Some tips… 1) Don’t hard code numbers into your formulas when possible; 2) insert
comments explaining your work and assumptions; 3) add tabs for worksheets as
reference tools and then link to them as needed (like we did with hedging and
production forecasting). Be creative in your work!
The Assumptions Page:
This document will NOT go in your final Freeman Report, however, it will allow your
FRTM and professor to evaluate your model using a document in which you spell
out what assumptions you used to forecast every line item in your model. Be explicit
and do not assume that we will be able to get inside your teams’ heads and
understand why you calculated an average, or used a fixed number, or used
numbers from Press Releases / SEC filings. Tell us what you did each step of the
way and why. The assumptions page should be like a road map that leads us to the
conclusions you made.
The Assumptions Page should be broken down into sections that correspond to each
financial statement (income statement, statement of cash flow, and balance sheet). If
you need to include assumptions that don’t fit nicely into one of these categories,
you can use a heading called “other.”