Body Shop Pro Forma Financial Statements
FIN 641- Summer 2013 – Excel Assignment
This assignment is due 10 am on Thursday, August 8. You need to use the Excel Template I posted. Your assignment will be graded on a scale of 0 to 25. The assignment should be submitted on Blackboard. No late assignment will be accepted. Enjoy the problems!
Assignment Instructions:
For this homework you will have to predict the future financial statements of the Body Shop. There is a template provided. Use the figures for 2011 and the forecasting assumptions on the template to forecast the income statement and balance sheet for the next three years. For some variables there are no assumptions provided since they should be calculated rather than predicted (e.g. retained earnings). Cash and Debt are the two plugs. Assumptions for the cash and debt plugs are described in the question.
Assignment Question:
Question 1 (15 points)
For debt the assumptions are different for each year. For all years, if there is need for additional debt (compared to the year before) the company will have to increase debt. However, in the case where Forecast the Income Statement and the Balance Sheet for the next three years (2012 -2014). The Body Shop, Inc. wants to maintain at least $300 of cash in all years. debt does not have to increase the following assumptions hold. For 2012 the company does not want to retire any debt. For 2013 they can retire debt if possible, but not more than 75% of last year’s debt. Finally for 2014, they are allowed to retire as much debt as possible.
Question 2 (10 points)
Create data tables (using the data table tool) that show what happens to debt in all three years when sales growth, accounts receivable % of sales and accounts payable % of sales change. For sales growth rate try the following growth rates: 28%, 29%, 30%, 31% and 32%. For accounts receivable % of sales check 10%, 15%, 20% or 25%. Finally, for accounts payable % try 3%, 6%, 9% and
This assignment is due 10 am on Thursday, August 8. You need to use the Excel Template I posted. Your assignment will be graded on a scale of 0 to 25. The assignment should be submitted on Blackboard. No late assignment will be accepted. Enjoy the problems!
Assignment Instructions:
For this homework you will have to predict the future financial statements of the Body Shop. There is a template provided. Use the figures for 2011 and the forecasting assumptions on the template to forecast the income statement and balance sheet for the next three years. For some variables there are no assumptions provided since they should be calculated rather than predicted (e.g. retained earnings). Cash and Debt are the two plugs. Assumptions for the cash and debt plugs are described in the question.
Assignment Question:
Question 1 (15 points)
For debt the assumptions are different for each year. For all years, if there is need for additional debt (compared to the year before) the company will have to increase debt. However, in the case where Forecast the Income Statement and the Balance Sheet for the next three years (2012 -2014). The Body Shop, Inc. wants to maintain at least $300 of cash in all years. debt does not have to increase the following assumptions hold. For 2012 the company does not want to retire any debt. For 2013 they can retire debt if possible, but not more than 75% of last year’s debt. Finally for 2014, they are allowed to retire as much debt as possible.
Question 2 (10 points)
Create data tables (using the data table tool) that show what happens to debt in all three years when sales growth, accounts receivable % of sales and accounts payable % of sales change. For sales growth rate try the following growth rates: 28%, 29%, 30%, 31% and 32%. For accounts receivable % of sales check 10%, 15%, 20% or 25%. Finally, for accounts payable % try 3%, 6%, 9% and