BUS 629 Week 1 Assignment
RANBABY
BUS629ForecastingEXAMPLE.xlsxProforma Income Statement
| PROFORMA INCOME STATEMENT | ||||
| Numbers in Billions | Q4 2019 | Q3 2019 | % change | Projected Q1 2020 |
| Sales Revenue | 26.6 | 24.7 | 7.1% | 28.5 |
| Cost of goods sold | 19 | 17.3 | 8.9% | 20.7 |
| Gross Profit | 7.4 | 7.3 | 1.4% | 7.5 |
| General and Anministrative Expense | 3.5 | 3.5 | 0.0% | 3.5 |
| Operating Profit | 3.9 | 3.8 | 2.6% | 4 |
| Interest Expense | 0.33 | 0.17 | 48.5% | 0.49 |
| Earnings Before Taxes | 4.4 | 5.7 | -29.5% | 3.1 |
| Taxes | 0.87 | 1.2 | -37.9% | 0.54 |
| Earnings After Taxes | 3.5 | 3.6 | -2.9% | 3.4 |
| Net Income | 3.6 | 4.5 | -25.0% | 2.7 |
Proforma Balance Sheet
| PROFORMA BALANCE SHEET | ||||
| Numbers in Billions | Q4 2019 | Q3 2019 | % change | Projected Q1 2020 |
| Current Assets | 5.8 | 5.6 | 3.4% | 6 |
| Non-Current Assets | 21.8 | 13.5 | 38.1% | 30.1 |
| Total Assets | 27.7 | 19.2 | 30.7% | 36.2 |
| Current liabilities | 8.6 | 6.1 | 29.1% | 11.1 |
| Non-Current Assets | 25.8 | 19.2 | 25.6% | 32.4 |
| Total Liabilities | 34.4 | 25.3 | 26.5% | 43.5 |
| Stockholder' Equity | -6.7 | -6.2 | 7.5% | -7.2 |
| Total Liabilities and Stockholder Equity | 27.7 | 19.1 | 31.0% | 36.3 |
Variance Analysis
| VARIANCE ANALYSIS | ||||||||
| Assignment | LINE ITEM | Q3 2019 Kevin Kuznia: This colum is the budget. In reality we know it is the previous quarter, but for this scenario we willl pretend it is the budgeted amount. | Q4 2019 Kevin Kuznia: This column are the actual results. In other words, it is the final amount that was reported. | AMOUNT | PERCENT VARIANCE Webster, Amanda: This is variance. It is how much in a percent you are over or under. | POTENTIAL RATIONALE FOR VARIANCE Webster, Amanda: Explain the plausiable reasons why there is a variance. |
||
| The highlighted cells have prepopulated formulas and will calculate automatically for you. | ||||||||
| Sales Revenue | 24.7 | 26.6 | 1.9 | 7.69% | Increase due to seasonality and holliday shopping increase | |||
| Cost of goods sold | 17.3 | 19 | 1.7 | 9.83% | Increase due to seasonality and holliday shopping increase | |||
| Gross Profit | 7.3 | 7.4 | 0.1 | 1.37% | Almost Flat since sales revenue went up, but so did COGS | |||
| General and Anministrative Expense | 3.5 | 3.5 | 0 | 0.00% | Almost Flat since sales revenue went up, but so did COGS | |||
| Operating Profit | 3.8 | 3.9 | 0.1 | 2.63% | Almost Flat since sales revenue went up, but so did COGS | |||
| Interest Expense | 0.17 | 0.33 | 0.16 | 94.12% | Interest expense went up since the company spent more in goods | |||
| Earnings Before Taxes | 5.7 | 4.4 | -1.3 | -22.81% | The company paid less is taxes from Q3 to Q4 | |||
| Taxes | 1.2 | 0.87 | -0.33 | -27.50% | The company paid less is taxes from Q3 to Q5 | |||
| Earnings After Taxes | 3.6 | 3.5 | -0.1 | -2.78% | Amost Flat quarter over quarter | |||
| Net Income | 4.5 | 3.6 | -0.9 | -20.00% | Earnings before taxes was much higher in Q3 so even with a bigger sales revenue the company had a higher new income in the previous quarter | |||
| Numbers in Billions |
