financial management

Hmmm .. that sounds interesting right.  I like it.  Great idea Rich   Each student will be given \$100,000 of virtual dollars.  At the end of each presentation, students will be asked if they want to invest in your company.  Top 3 companies (with largest capital contributions) get bonus points.  Top 3 students that generate the highest IRRs will get bonus points.  If FINANCE is the art and science of managing money — then LEARNING should be one part experiential and one part entertainment.  I wish I was in this class!  Oh right – I am the teacher ….

• STEP #1: Complete Benchmarks (25%). ONLY LOAD PDF DOCS.

Attached Files:

YOU ARE EXPECTED TO BUILD THIS SPREADSHEET FROM SCRATCH.  IF IT COMES TO MY ATTENTION THAT YOU HAVE USED A SOFTWARE PROGRAM (OR ANY OTHER MEANS) TO CONVERT MY PDF DOCUMENT INTO AN EXCEL DOCUMENT, YOU WILL RECEIVE A ZERO FOR THE ENTIRE ASSIGNMENT.  IT IS JUST NOT WORTH IT.

Step by step guidance as follows:

2.  Select PRIMARY competitive benchmark.  Should be US public company.
3.  Go to Yahoo Finance.

4.  Download Income Statement and Balance Sheet Data for current 3 years into Excel worksheet.
5.  Click on COMPETETORS link on Yahoo Finance to see the competitors to your PRIMARY company benchmark.

7.  Now start BUILDING your excel shell.
8.  Work on Income Statement first.  Ratios here are a function (denominator) of Total Revenue (Sales).
9.  Next work on Balance Sheet.  Ratios should be a function of Total Assets.

10.  Make sure that your NET INCOME totals and TOT ASSETS totals match totals on Yahoo Finance.
11.  Now start building your RATIO equations.
12.  Review my EXAMPLE.  Make sure you can figure out the Numerator and Denominator for EACH metric.

13.  Now – complete your RATIO equations.
14.  Once you have completed ONE YEAR, just copy and paste to the other 2 years.
15.  You should REPLICATE Quad Graphics worksheet FIRST.  This is perhaps the best idea here!

16.  When you complete your first company, cut and paste this shell into 2 more tabs.
17.  REMEMBER – it is a clear VIOLATION of academic policy for sharing your excel spreadsheet with students.  The penalty is ZERO for both parties for this assignment.

• STEP #3: Complete 5 Year Projections (25%). ONLY LOAD PDF DOCS.

Attached Files:

YOU ARE EXPECTED TO BUILD THIS SPREADSHEET FROM SCRATCH.  IF IT COMES TO MY ATTENTION THAT YOU HAVE USED A SOFTWARE PROGRAM (OR ANY OTHER MEANS) TO CONVERT MY PDF DOCUMENT INTO AN EXCEL DOCUMENT, YOU WILL RECEIVE A ZERO FOR THE ENTIRE ASSIGNMENT.  IT IS JUST NOT WORTH IT.

Step-by-step guidance:

1.  Build excel shell.  Create all line item descriptions first.
2.  Import (manually) key ratios from your benchmarks for your two best companies.
3.  You select 2 best companies by seeing which ratios align best with each other.

4.  CAUTION: make sure when you are dealing with numbers in THOUSANDS vs WHOLE DOLLARS.
5.  INCOME STATEMENT SECTION.  You will have 2 deviations from your Benchmarks.  Your Interest Expense Ratio will come from an estimate you create from the CURRENT DEBT section.  And you are to use a FIXED 30% for taxes.
6.  Revenue per Employee will allow you to estimate the number of people you can afford to hire.

7.  DIVIDEND SECTION.  You are to use the FIXED dividend payout ratios that I have in my spreadsheet.  Starts at 30% and ends at 70% in year 5.  The residual (CUM RETAINED EARNINGS) becomes the number that goes into the Equity section of your Balance Sheet.
8.  REMEMBER: your company ratios for the entire spreadsheet are a SIMPLE AVERAGE of your 2 benchmark companies.
9.  ASSETS SECTION.  Now you can build your Assets using the benchmark ratios. Cash becomes the “plug” so that A = L+E.  See calculation section right below total assets.

10.  LIABS+EQUITY SECTION.  Continue to build your Liabs/Equity accounts from your benchmark ratios.  Your Capital Ratio which is the % of total equity will determine how much your investors will need to contribute to start your business.

11.  CURRENT DEBT SECTION.  You will need to use the PMT excel function to calculate your Loan Payment.  It is easy to use.
12.  TARGET RETURN SECTION.  Keep your target return at 20%.  You calculate your DISCOUNT RATES for each year just like we did when we were playing “Shark Tank.”  Your equity stake % becomes the % of company that will be owned by your investors.  You take the invested capital / total PV of the company.

13.  IRR SECTION.  Remember to ONLY use the Net Income of the 5 years BEFORE any present value calculations.  You should be able to see that on my model.  Use the IRR function in excel.  You should be able to validate the calculation with your HP12c using the CFo and CFj functions.

EFFICIENCY IS ALL ABOUT DOING THINGS FAST.  EFFECTIVE IS ALL ABOUT DOING THINGS RIGHT.  I HIGHLY RECOMMEND TO STUDENTS WHO ARE EXCEL NOVICES TO FIRST BUILD YOUR WORKSHEET USING MY NUMBERS IN THE EXAMPLE.  WHEN YOU HAVE REPLICATED MY NUMBERS, YOU CAN JUST DROP YOUR NUMBERS IN WITH CONFIDENCE.

• STEP #4: Prepare your Class Presentation (25%).

Dear Class – I am NOT providing a template for your Class Presentation.  The primary reason is to remove a project burden from you.  However – you are expected to provide a professional presentation of your company.  Pretend that you are on SHARK TANK and we are your investors.  You decide if you want to have talking notes.  It is NOT acceptable for you to READ aloud your talking notes.  You would be kicked off Shark Tank if you did that, right?

Here is my presentation guidance:

1.  Total presentation should be 3-5 minutes.  It will be timed.
2.  You will need to confidently and credibly answer questions from the “investors.”

In your presentation, you must discuss (include) the following:

1.   Earnings per Share.  Assume that you have 50,000 shares outstanding.
2.   Dividend Yield.
3.   Dividend Payout Ratio.
4.   IRR.
5.   Payback — how many months does it take to recoup your capital contribution through dividends.
6.   Net Income Margin and how that compares to large companies in general.
7.   Revenue Growth.
8.   Use Dividend Growth Rate Model to estimate your STOCK PRICE in Year 1 and in Year 5.  Talk about its potential for capital appreciation.  Use your revenue growth for the dividend growth.  Assume the Required Rate of Return is 25%.
10. How many employees can you hire in year 1 and in year 5.
11. What is your Debt %.  Equity %.  How does that compare to large companies.
12. Return on Assets?  How will you improve it?
13. Return on Equity?
14. What is your Working Capital in Years 1, 3 and 5?  Working Capital = Current Assets – Current Liabs.  How will you improve that over time?
15. Total Asset Turnover.  What is it in years 1,3 and 5?  How will you improve it over time?

• February 24, 2018