accounting 203

ACCT203 SPECIAL ASSIGNMENT – DUPONT MODEL

THERE ARE THREE KEY PHASES TO THIS ASSIGNMENT:

PART ONE - DUPONT MODEL READINGS

PART TWO - DUPONT MODEL DATA RESEARCH

PART THREE - DUPONT MODEL ANALYSIS AND UPLOAD

YOU MUST follow this presentation through all TWELVE STEPS!

YOU WILL ENTER A “DATA BLOCK” OF CORPORATE DATA INTO A SPREADSHEET GRID WHICH WILL COMPUTE THE FINAL DUPONT MODEL CALCULATIONS IN THE EXCEL SPREADSHEET FILE ENTITLED:

ACCT203 EMPLOYING – THE DUPONT MODEL.xls

YOU WILL COPY THE FINAL DUPONT MODEL GRID NUMBERS INTO, AND ANSWER ALL THE QUESTIONS IN, AN ANSWER SHEET WITH YOUR COMPLETED ANSWERS FILE ENTITLED:

ACCT203 FALL 18 ANSWER SHEET DUPONT MODEL LAST NAME.docx

PART ONE - DUPONT MODEL READINGS

STEP ONE: Read these resources about the DuPont Model:

Read this section of the textbook, where Return on Investment (ROI) is discussed in chapter 24: pages 1293 to 1302

Read this for a summary of the DuPont Model:

http://www.graduatetutor.com/accounting-tutors/ratio-analysis-using-the-dupont-model/

This article aggressively reviews the DuPont Model and explores the “Extended Model”, which enhances this model by addressing the impact of interest expense and taxes on the profit margin:

https://www.aaii.com/journal/article/breaking-down-roe-using-the-dupont-formula.touch

From Investopedia (this commercially sponsored site is very helpful, but is full of many distracting ads):

http://www.investopedia.com/terms/r/returnonequity.asp

http://www.investopedia.com/ask/answers/070914/are-companies-negative-return-equity-roe-always-bad-investment.asp

http://www.investopedia.com/articles/basics/05/052005.asp

PART TWO- DUPONT MODEL DATA RESEARCH

STEP TWO: Explore the web, on your own, to find more information about the DuPont Model

STEP THREE: SEEK AND FIND YOUR OWN FAVORITE WEB-BASED RESOURCES THAT DESCRIBE THE DUPONT MODEL. List at least one of the resources you found on the web.

Here’s the link to the DuPont Model information I found:

Copy the link here: _______________________

STEP FOUR: Choose and define a popular publicly-owned company competing in an industry that appeals to you.

THIS COMPANY IS THE COMPANY I CHOSE _______________________________ IN THIS INDUSTRY _____________________:

STEP FIVE: Explore the web to find financial data from at least one consistent source about your chosen company.

Start your search by exploring the SEC’s EDGAR DATABASE:

https://www.sec.gov/edgar/searchedgar/companysearch.html

WEB-REFERENCE I USED FOR FINANCIAL DATA ________________________________________________

STEP SIX: SELECT THREE CONSECUTIVE YEARS AND COMPILE THE DATA TO FILL IN THIS “BASIC DATA” TABLE:

LIST THE BASIC DATA HERE FOR EACH YEAR

THIS IS THE “DATA BLOCK” THAT WILL BE INPUT INTO THE EXCEL SPREADSHEET (Part Three)

COLUMN =>	1	2	3	4
YEAR (insert “Year” (i.e., 2005) in each row	NET PROFIT (after payment of preferred dividends)	SALES (Net Sales: assume all sales are credit sales)	ASSETS (gross assets before depreciation; average for the year)	EQUITY (common stock: average for the year)
YEAR 1 ________________
YEAR 2 ________________
YEAR 3 ________________

PART THREE - DUPONT MODEL ANALYSIS AND UPLOAD

These steps (7, 8, & 9) are embodied in a very helpful Excel spreadsheet that you will find in the Files section of the Canvas shell. If you use the Accompanying Excel File, proceed directly to step Ten and answer the Questions.

PLEASE USE THIS ACCOMPANYING EXCEL FILE – FILE

This enables you to skip the mathematical calculations highlighted in these tables. By using that Excel File – all you need to do is “Insert the “years” you chose, and the twelve Financial Figures (four numbers (in $ million) for each of three years) in the “Data Block”, and all the computations will appear.

IF YOU USE THIS EXCEL SPREADSHEET: GO DIRECTLY TO STEP TEN

DO NOT BOTHER TO INSERT DATA IN THESE TABLES IN THIS DOCUMENT.

JUST MAKE SURE THAT YOU INSERT ALL THE INTO DATA INTO THE DATA BLOCK IN THE EXCEL FILE.

Steps 7, 8, & 9 describe the process and provide the format for entering and calculating the date needed to analyze the DuPont Model and to answer the Questions in Step 10.

STEP SEVEN: COMPUTE AND PRESENT THE “PERCENTAGE RATE OF CHANGE” OF THE DATA IN YEARS 2 AND 3

COLUMN =>	1	2	3	4
YEAR (insert “Year” (i.e., 2005) in each row	NET PROFIT (after payment of preferred dividends)	SALES (Net Sales: assume all sales are credit sales)	ASSETS (gross assets before depreciation; average for the year)	EQUITY (common stock: average for the year)
YEAR 1 ________________
YEAR 2 ________________
YEAR 3 ________________
PERCENTAGE CHANGE IN YEAR 2 = (YR 2- YR 1) / YR 1
PERCENTAGE CHANGE IN YEAR 3 = (YR 3- YR 2) / YR 2

STEP EIGHT: THE DUPONT MODEL: EMPLOY YOUR DATA TO COMPUTE THE RATIOS REQUIRED TO COMPUTE THE RETURN ON EQUITY

THIS IS THE ROE COMPUTED BY THE DUPONT MODEL WITHIN THIS TABLE

COLUMN =>	1	2	3	4	Col 5 = (1/2)	Col 6 = (2/3)	Col 7 = (3/4)	Col 8 = 5*6*7 same as: = (1/4)
YEAR (insert “Year” (i.e., 2005) in each row	NET PROFIT (after payment of preferred dividends)	SALES (Net Sales: assume all sales are credit sales)	ASSETS (gross assets before depreciation; average for the year)	EQUITY (common stock: average for the year)	PROFIT MARGIN RATIO	ASSET TURNOVER RATIO	LEVERAGE RATIO	RETURN ON EQUITY (ROE)
YEAR 1 ________________
YEAR 2 ________________
YEAR 3 ________________

STEP NINE: COMPUTE AND PRESENT THE “PERCENTAGE RATE OF CHANGE” OF THE “RATIOS” IN YEARS 2 AND 3

COLUMN =>	1	2	3	4	Col 5 = (1/2)	Col 6 = (2/3)	Col 7 = (3/4)	Col 8 = 5*6*7 same as: = (1/4)
YEAR (insert “Year” (i.e., 2005) in each row	NET PROFIT	SALES	ASSETS	EQUITY	PROFIT MARGIN RATIO	ASSET TURNOVER RATIO	LEVERAGE RATIO	RETURN ON EQUITY (ROE)
YEAR 1 ________________
YEAR 2 ________________
YEAR 3 ________________
PERCENTAGE CHANGE IN YEAR 2 = (YR 2- YR 1) / YR 1
PERCENTAGE CHANGE IN YEAR 3 = (YR 3- YR 2) / YR 2

Start Here => After the Data Block is confirmed and the Excel Spreadsheet has calculated your results, you are to answer these questions and upload your work.

STEP TEN: NOW THAT YOU HAVE ALL THE DATA IN THE TABLE, REFLECT ON THE DATA AND ANSWER THESE QUESTIONS

You must use the ANSWER SHEET BELOW to submit your answers to these Questions when you upload your answer sheet through the Canvas Website

QUESTIONS:

Section 1: Basic Questions:

Share your reflections on the DATA BLOCK information - before you did the calculations:

Did the numbers seem attractive or worrisome?

Given that Investment Rate of Return Expectations should be in the 10% range, did you think this company was doing well or not?

What trends from these three years impress you?

What’s good?

What’s bad?

Explore the ROE…

Is it consistent?

If not, why not?

If so, is it attractive?

How does the ROE compare to that of other companies in the same industry?

Section 2: Exploring the managerial performances…

Discuss the Profit Margin performance – Operational Management.

Discuss the Sales to Asset Ratio – Asset Management.

Discuss the Leverage Ratio (Assets to Equity Ratio) – Financing Management

Section 3: Beyond the Numbers

What business related factors seem to impact the changes in each ratio from year to year?

Characterize the relative percent changes in each ratio from year to year…

Did all the ratios change in a similar manner?

If not, why not?

If the changes are consistent, how could that be when these are three ratios are not inter-related?

Now that you’ve seen the data and the changes, what managerial issues require further exploration?

Are Profit Margins in trouble?

Do they have unproductive assets that are not supporting sales in a changing economy?

Are Sales growing or are they falling?

Do they have too much debt?

Do they not have enough debt?

Do they need more equity?

Can they attract more equity?

STEP ELEVEN: CONFIRM THAT YOUR PERSONAL ANSWER SHEET WORD DOCUMENT FILE CONTAINS YOUR COMPLETE ANSWERS. PLEASE USE THE “ACCT203 SPECIAL ASSIGNMENT ANSWER SHEET

FALL 2018” WORD DOCUMENT FILE – SAVED WITH YOUR LAST NAME AS DESCRIBED BELOW

BE SURE TO TRANSFER THE DATA AND CALCULATION RESULTS INTO THE COMPLETED DUPONT MODEL GRID.

STEP TWELVE: UPLOAD THE ANSWER SHEET WORD DOCUMENT FILE WITHIN THE CANVAS SITE.

ACCT203 SPECIAL ASSIGNMENT ANSWER SHEET FALL 2018

YOU MUST SUBMIT THIS FIVE –PAGE ANSWER SHEET AND ONLY THIS ANSWER SHEET

Insert your name and the date here before you save this file as:

ACCT203 FALL 18 ANSWER SHEET DUPONT MODEL LAST NAME.docx, where LAST NAME = YOUR LAST NAME

NAME ___________________________________________________________ DATE _________________

BEFORE UPLOADING YOUR ANSWERS, SAVE A COPY OF THIS WORD DOCUMENT AS A WORD DOCUMENT WITH THIS FILE NAME:

ACCT203 FALL 18 ANSWER SHEET DUPONT MODEL LAST NAME.docx where LAST NAME = YOUR LAST NAME

For example, for Ms. Sloane Stephens*, her file name would be:

ACCT203 FALL 18 ANSWER SHEET DUPONT MODEL STEPHENS.docx

.This answer sheet contains my Data Block and Calculations:

FROM STEP THREE: Here’s the BEST link to the DuPont Model information I found on my own:

Copy the link here: _______________________

FROM STEP FOUR: Choose and define a popular publicly-owned company competing in an industry that appeals to you.

THIS COMPANY IS THE COMPANY I CHOSE _______________________________ IN THIS INDUSTRY _____________________:

FROM STEP FIVE: Explore the web to find financial data from at least one consistent source about your chosen company. https://www.sec.gov/edgar/searchedgar/companysearch.html

WEB-REFERENCE I USED FOR FINANCIAL DATA ________________________________________________

Here’s my completed DuPont Model Grid – Insert the numbers form the Excel Spreadsheet here:

COLUMN =>	1	2	3	4	Col 5 = (1/2)	Col 6 = (2/3)	Col 7 = (3/4)	Col 8 = 5*6*7 same as: = (1/4)
YEAR (insert “Year” (i.e., 2005) in each row	NET PROFIT	SALES	ASSETS	EQUITY	PROFIT MARGIN RATIO	ASSET TURNOVER RATIO	LEVERAGE RATIO	RETURN ON EQUITY (ROE)
YEAR 1 ________________
YEAR 2 ________________
YEAR 3 ________________
PERCENTAGE CHANGE IN YEAR 2 = (YR 2- YR 1) / YR 1
PERCENTAGE CHANGE IN YEAR 3 = (YR 3- YR 2) / YR 2

Here are my Answers to the Questions:

Section 1: Basic Questions:

Share your reflections on the DATA BLOCK information - before you did the calculations:

Did the numbers seem attractive or worrisome?

Given that Investment Rate of Return Expectations should be in the 10% range, did you think this company was doing well or not?

What trends from these three years impress you?

What’s good?

What’s bad?

Explore the ROE…

Is it consistent?

If not, why not?

If so, is it attractive?

How does the ROE compare to that of other companies in the same industry?

Section 2: Exploring the managerial performances…

Discuss the Profit Margin performance – Operational Management.

Discuss the Sales to Asset Ratio – Asset Management.

Discuss the Leverage Ratio (Assets to Equity Ratio) – Financing Management

Section 3: Beyond the Numbers

What business related factors seem to impact the changes in each ratio from year to year?

Characterize the relative percent changes in each ratio from year to year…

Did all the ratios change in a similar manner?

If not, why not?

If the changes are consistent, how could that be when these are three ratios are not inter-related?

Now that you’ve seen the data and the changes, what managerial issues require further exploration?

Are Profit Margins in trouble?

Do they have unproductive assets that are not supporting sales in a changing economy?

Are Sales growing or are they falling?

Do they have too much debt?

Do they not have enough debt?

Do they need more equity?

Can they attract more equity?