Excel Assignment #$1$

Assignment Instructions:

For this assignment, you will have to predict the future financial statements of the Body Shop. Utilize the provided template. Use the figures for $2023$ and forecasting assumptions on the template to forecast the income statement and balance sheet for the next three years. For some variables there are not assumptions provided since they are to 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 below.

Assignment Question: $(50$ points$)$

Forecast the Income Statement and the Balance Sheet for the next three years $(2024-2026).$ The Body Shop, Inc. aims to maintain a minimum cash balance of $$300$ in each year. Debt assumptions vary 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 debt does not have to increase the following assumptions hold: For $2024,$ the company does not want to retire any debt. For $2025,$ they can retire debt if possible, but not more than $75\%$ of last year's debt. Lastly, for $2026,$ the company plans to retire as much debt as possible.

$$table$[[$Body Shop Inc. Pro Forma Financial Statements,,,$],[$Put your name and your teammate's name:$],[,,,$Forecasts,$],[,{2023}_{},{2024}_{},{2025}_{},{2026}_{}$ 

Body Shop Inc. Pro Forma Financial Statements Put your name and your teammate's name: Income Statement 2023 2024 Sales Turnover $147,441 Cost of Sales $68,210 Gross Profit $79,231 Distribution Expense $32,021 Administrative Expense $19,335 Interest Expense $2,672 Profit Before Tax $25,203 Tax Expense $8,688 Profit After Tax Ordinary Dividends Profit Retained $16,515 $3,115 $13,400 Balance Sheet Cash Accounts Receivable Inventories $483 $26,485 $38,457 Other Current Assets Net Fixed Assets Total Assets Accounts Payable Taxes Payable Accruals Debt Other Current Liabilities Retained Earnings Total Liabilities and Equity Trial Assets Trial Liabilities Difference Forecasting Assumptions Sales are expected to grow by 28% per year COGS are expected to be 44.5% of sales $9,576 $58,657 $133,658 $10,112 $6,157 $3,356 $29,748 $9,815 $74,470 $133,658 28.00% 44.50% Distribution expenses are 23% of sales 23.00% Administrative Expenses are 13.2% of sales Interest Rate on Debt is 10% 13.20% 10.00% Taxes are 36% of profit before tax 36.00% Dividend Payout Ratio is 18% 18.00% Accounts receivable are expected to be 22% sales 22.00% Inventories are expected to be 28% of sales 28.00% Other current assets are expected to be 5.6% of sales 5.60% Net Fixed Assets are 39% of sales 39.00% Accounts payable are 5.1% of sales 5.10% Taxes payable are 5.5% of sales Accruals are 2.5% of sales Other current liabilities are 3.4% of sales Note: 5.50% 2.50% 3.40% Interest Payments on Debt are calculated on average debt Forecasts 2025 2026