Historical income statements and balance sheets for Carl's Soup Co$.$ are provided in the above referenced Excel file. All dollar figures and share values are expressed in millions of dollars.

In the Worksheet named "Historical & Pro Forma," identify all the numbers in Tables $1$ and $2$ that should be calculated based on other numbers and replace these numbers with the appropriate formulas. Note that the values that should be replaced with formulas are highlighted in yellow. $(10$ points$)$

Additionally, calculate the historical earnings per share $($EPS$)$ and Dividends per share $($DPS$)$ in rows $31$ and $32.(5$ points$)2.$ Pro Forma Statements $($Unbalanced$).$You have been asked to prepare projected financial statements for Carl's Soup Co$.$ for $20192020$ starting with the historical statements for $2016-2018.$ For your forecast use the assumptions given in the worksheet entitled "Forecast Assumptions," but make your model flexible enough so that a user can easily play "what if$"$ games. $(40$ points$)$

Be sure to enter the forecast factors in the space provided in column G$.$

As indicated in the assumptions, hold the levels of both short$-$term and long$-$term debts constant. This will result in an unbalanced balance sheet. Show the difference as the discretionary funding needed $($DFN$)$ in row $74.$

Calculate the pro forma EPS and DPS in rows $31$ and $32.$

You should have forecasts for both the income statement and balance sheet

Format all of the pro forma numbers so that they match the formatting of the corresponding historical numbers. $3.$ Balanced Pro Forma Statements $($Pro Frrma Balanced$).$ We saw with the previous pro forma model that most of the time projected balance sheets will be out of bafance and will indicate a need for discretionary financing. Modify the model so that it will automatically balance the balance sheet based on the following requirements. $(10$ points$)$

Ensure that Excel's iterative calculation is turned on$!$

Create a copy of the "Historical & Pro Forma" worksheet and name it "Pro Forma Balanced."

Use short$-$term debt $($STD$)$ as the plug to balance the entire balance sheet.

What you should observe is that for one or more years, STD becomes negative.

Pro Forma $1.$ Now, modify the balance sheet in the previous model so that Cash & Marketable Securities $($C&MS$)$ maintains a minimum $$250($million$)$ balance and that any cash in excess of this is used to pay off STD$,$ and if all STD is paid off, then excess cash is deposited in the C&MS account. $(15$ points$)$

Create a copy of the "Pro Forma Balanced" worksheet and name it "Pro Forma $1"$ to complete this requirement.

Financial Statement Sensitivity Analysis $($Sensitivity$).$ First, make a copy of the "Pro Forma $1"$ worksheet and name it "Sensitivity."

Your management wants to see how Net Income, EPS, and dividends per share $($DPS$)$ will change for $2020$ for sales growth rates from $1\%$ to $10\%($changing by $1\%$ each time$)$ per year over $2019$ and $2020.$ Create a one$-$input data table to show this information. $(10$ points$)$

Be sure to format this table so that it is easy to read. Make use of borders and shading. Also be sure to label the columns for Net Income, EPS and DPS$.$ The table should look similar to what we completed in class.

Ensure that the numbers produced are formatted using the Currency number format. :

Second, create a two$-$input data table to show how EBIT for $2020$ will depend on the cost of goods sold$-$to$-$sales ratio and sales growth rate in a reasonable range. $(10$ points$)$

Use the sales growth rates from the sensitivity table you just created.

For the cost of goods sold$-$to$-$sales ratio, use $53\%$ to $57\%$

INCLUDE FORMULAS USED