代做Financial Modeling I - Assignment #6代写Web开发

日期：2024-10-28 05:46

Financial Modeling I - Assignment #6

Notes:

1. You should submit your assignment electronically using the drop-box on the D2L page for the course.

2. Every student should submit their own individual assignment.  However, feel free to work with other students in the class to construct your model.  You may work with students from other sections of the course. Make sure that you place your name and student number on the assignment.  Also, only submit one file, with a title such as: “Assignment #6 – Your Name.xlsx”

I would like you to construct an integrated 3-statement financial model for Blue Coua Paper Company.   This assignment is based on the 2015 Financial Modeling Case Competition.  See the attached pdf file for an overview of the company and the financial forecasting factors.  I have also attached an Excel file with 5 years of financial statements.  Just to make things really interesting we are going to add a couple of additional features to the model:

1) Assume that the firm’s bank has imposed an upper limit on your Revolver (i.e. line of credit) of $15 million.  At the end of every year your Revolver account cannot exceed $15 million.  If you need more than $15 million you will issue additional Long Term Debt to make up the difference.

2) In addition, the bank has imposed a commitment fee of 0.25% on your Revolver.  The bank will charge 0.25% on any unused portion of your line of credit.  This will be charged on the unused Revolver balance at the end of the current year.

Please use the Fairmont Moulding’s Inc. model developed in the Class #6 videos as a template for your model.  I would like you to include the following Worksheets in your model:

1. Documentation, Summary and Memo

2. Control Panel

3. Assumptions

4. Model

5. Common Size Financial Statements (Income Statement and Balance Sheet as a % of Sales)

6. Ratios

Questions management would like answered:

1. Calculate the firm’s Required External Financing (REF) for all 5 projected years (i.e. 2016 to 2020) for sales volume growth rates ranging from -10% to +15% in steps of 0.5% (a data table would be a good idea here).  Create a chart to report your results.  You can assume that the growth rates for both product lines are the same.  You will probably want to construct your data tables on the control panel worksheet.

2. How rapidly could BCC grow its sales without the requirement for any outside financing?  Although Goal Seek seems like the obvious way to answer this problem, it will likely not work because of the circular references.  Instead just look at the table you created above and report an estimate.

3. Go back to the base case.  Now assume the following:

i. BCC improves their Gross Profit Margin to 10%

ii. BCC reduces is Days Sales Outstanding to 80 days.

iii. BCC reduces its other long-term assets to 15% of sales.

iv. BCC pays out 30% of its net income in dividends each year.

v. Also assume the BCC commits to selling $5000 of underutilized Fixed Assets each year and use the money to pay down long-term debt (i.e. they will pay off $8,333.33 each year).

How would this change your answers to questions 1 and 2?

Which of the above factors contributes the greatest to the improvement of the company’s prospects?  Use the DuPont factors as of 2020 to justify your answer.  You will want to record these factors for the

Present your results of your analysis and the answers to the above questions in the form. of a 1-page memo to the management of Blue Coua.  You can include summary tables and charts within your memo.  Use a text box on the first worksheet to present your memo.

Note:  You can use the following market prices for Blue Coua for the years from 2011 to 2015:

$40.05

$39.90

$22.40

$16.75

$12.60