MGT 2205 – Business Finance
Fall 2015
Professors: Stephanie Dovigi & Jeff Threader
Excel Spreadsheet – Case Study

Rationale:

“Spreadsheet models have become the dominant method for finance professionals in the business world to implement their financial knowledge.
As a result, it is important that students learn how to build financial models in EXCEL.     ”

Specific Objectives:

1    To stimulate interest in the discipline of corporate finance.
2    To gain familiarity with building financial models in EXCEL.

Mark Deadlines:

1    The project is DUE ON:  your 2nd class of Week 9 (Wednesday Nov 4, Thursday Nov 5)
2    The assignment is worth 15% of the course grade.
3    Late assignments will be NOT BE ACCEPTED
4    All assignments must have a covering page (APPENDIX A).
5    Pages must be stapled ONLY.
6.        This assignment is to be handed in, not submitted electronically. If you can not be there that class, have one of your group members bring it in.

Criteria of Evaluation:

This assignment will be graded on the following:

1    Financial Planning Exercise
2    Ratio Calculations
3    Summary Analysis
4    Presentation and Format
5    Peer Evaluation

Group Work:

Students are required to work in groups of two (2) or three (3) to complete this case study.  A 10% deduction will be assessed to any submission that is not by a group.

Computing and Analyzing Financial Ratios

Requirements for Pro Forma Portion:   (Financial Planning)     [30 marks]

1    “Using the financial statements for 2014 as your ‘base’, assume that XYZ’s sales will be 25% higher in 2015.
Use this projection to prepare the pro forma statements following the requirements listed below.  ”

2    For the Income Statement:
§  Cost of Goods Sold rate (the % relative to Sales) is expected to remain constant (in otherwords, COGS will increase with Sales);
§  ‘Admin Expenses” will change in direct proportion to Sales;
§  ‘Depreciation’ and ‘Interest paid’ expenses are expected not to change;
§  The Tax rate is expected to increase to 28%; and
§  Management is expected to increase the rate of dividends paid by 20% (therefore, the Dividend payout ratio will increase by 20% from 2014 levels).
(— in other words, if the Payout Ratio in the Base Year is 25%, it becomes 45% in the Proforma year ( 25 + 20),….which of course, changes the Retention Ratio )

3    For the Balance Sheet:
§  ‘Current assets’ change in direct proportion to sales;
§  ‘Fixed assets’ are being operated at 100% of capacity;
§  ‘Accounts payable’ changes in direct proportion to sales;
§  ‘Notes payable’ and ‘Other’ current liabilities do not change;
§  ‘Common stock’ remains unchanged
§  Use LTD as your “plug”

4    Determine the amount of External Financing Needed (EFN) under the pro forma assumptions.  (How much did you have to “plug” to balance?)  Explain where this money is going.

Requirements for Ratio Calculation Portion:
1    Using the exact formulas to compute all the ratios (to 2 decimal places) listed in Appendix B using year-end figures.

Summary Analysis:     [30 marks]

1    “Based entirely on your ratio analysis explain in detail the strengths and weaknesses of the company to a potential common equity investor or a potential lender.
(Pay particular attention to short and long-term solvency and profitability).”

2    “With specific reference to the computed financial ratios, explain the impact of an increase in sales on the financial position of the company.
(i.e.; look at the changes in the financial ratios.)”

3    What (if any) additional information would be needed in order to perform a more complete financial analysis of XYZ Corporation?

Hard Copy Requirements for Excel:

1    Questions must be done in the right order and format in Excel.
2    Place Appendix A (Assignment Evaluation Sheet) as page 1.
3    Place the Income Statement on page 2.
4    Place the Balance Sheet on page 3.
5    Place Appendix B on page 4.
6    Place the summary analysis on page 5. (Do in MS Word)
7    Place Appendix C (Group Assessment Sheet) on page 6.
7    “Show all cell formulas for ratio computations and financial statements at the end of the report.
(In Excel go File/Options/Advanced…2/3 of the way down…Display Options for this worksheet – click off second box to show formulas – print – then unclick to return results)”
8    “(Optional) Make gridlines and row and column headings visible for all pages printed from Excel.
(Page Layout/Print Titles/Sheet…click on Gridlines and Row and column heading boxes)”
9    Round all numbers to two decimal places.
10    Format all ratios to the proper symbol: ie, ($), (%) etc.

MGT 2205 – Business Finance
Fall 2015
Professors: Stephanie Dovigi & Jeff Threader
Excel Spreadsheet – Case Study

Rationale:

“Spreadsheet models have become the dominant method for finance professionals in the business world to implement their financial knowledge.
As a result, it is important that students learn how to build financial models in EXCEL.     ”

Specific Objectives:

1    To stimulate interest in the discipline of corporate finance.
2    To gain familiarity with building financial models in EXCEL.

Mark Deadlines:

1    The project is DUE ON:  your 2nd class of Week 9 (Wednesday Nov 4, Thursday Nov 5)
2    The assignment is worth 15% of the course grade.
3    Late assignments will be NOT BE ACCEPTED
4    All assignments must have a covering page (APPENDIX A).
5    Pages must be stapled ONLY.
6.        This assignment is to be handed in, not submitted electronically. If you can not be there that class, have one of your group members bring it in.

Criteria of Evaluation:

This assignment will be graded on the following:

1    Financial Planning Exercise
2    Ratio Calculations
3    Summary Analysis
4    Presentation and Format
5    Peer Evaluation

Group Work:

Students are required to work in groups of two (2) or three (3) to complete this case study.  A 10% deduction will be assessed to any submission that is not by a group.

Computing and Analyzing Financial Ratios

Requirements for Pro Forma Portion:   (Financial Planning)     [30 marks]

1    “Using the financial statements for 2014 as your ‘base’, assume that XYZ’s sales will be 25% higher in 2015.
Use this projection to prepare the pro forma statements following the requirements listed below.  ”

2    For the Income Statement:
§  Cost of Goods Sold rate (the % relative to Sales) is expected to remain constant (in otherwords, COGS will increase with Sales);
§  ‘Admin Expenses” will change in direct proportion to Sales;
§  ‘Depreciation’ and ‘Interest paid’ expenses are expected not to change;
§  The Tax rate is expected to increase to 28%; and
§  Management is expected to increase the rate of dividends paid by 20% (therefore, the Dividend payout ratio will increase by 20% from 2014 levels).
(— in other words, if the Payout Ratio in the Base Year is 25%, it becomes 45% in the Proforma year ( 25 + 20),….which of course, changes the Retention Ratio )

3    For the Balance Sheet:
§  ‘Current assets’ change in direct proportion to sales;
§  ‘Fixed assets’ are being operated at 100% of capacity;
§  ‘Accounts payable’ changes in direct proportion to sales;
§  ‘Notes payable’ and ‘Other’ current liabilities do not change;
§  ‘Common stock’ remains unchanged
§  Use LTD as your “plug”

4    Determine the amount of External Financing Needed (EFN) under the pro forma assumptions.  (How much did you have to “plug” to balance?)  Explain where this money is going.

Requirements for Ratio Calculation Portion:
1    Using the exact formulas to compute all the ratios (to 2 decimal places) listed in Appendix B using year-end figures.

Summary Analysis:     [30 marks]

1    “Based entirely on your ratio analysis explain in detail the strengths and weaknesses of the company to a potential common equity investor or a potential lender.
(Pay particular attention to short and long-term solvency and profitability).”

2    “With specific reference to the computed financial ratios, explain the impact of an increase in sales on the financial position of the company.
(i.e.; look at the changes in the financial ratios.)”

3    What (if any) additional information would be needed in order to perform a more complete financial analysis of XYZ Corporation?

Hard Copy Requirements for Excel:

1    Questions must be done in the right order and format in Excel.
2    Place Appendix A (Assignment Evaluation Sheet) as page 1.
3    Place the Income Statement on page 2.
4    Place the Balance Sheet on page 3.
5    Place Appendix B on page 4.
6    Place the summary analysis on page 5. (Do in MS Word)
7    Place Appendix C (Group Assessment Sheet) on page 6.
7    “Show all cell formulas for ratio computations and financial statements at the end of the report.
(In Excel go File/Options/Advanced…2/3 of the way down…Display Options for this worksheet – click off second box to show formulas – print – then unclick to return results)”
8    “(Optional) Make gridlines and row and column headings visible for all pages printed from Excel.
(Page Layout/Print Titles/Sheet…click on Gridlines and Row and column heading boxes)”
9    Round all numbers to two decimal places.
10    Format all ratios to the proper symbol: ie, ($), (%) etc.