Question

I am struggling on finding the formulas and answers for the areas highlighted in pink and blue. Please don't just give the answers, but the formulas so I can see how you are coming up with the answer.

Thank you so much!

CORPORATE FINANCIAL PLANNING Cutting Edge B2B Inc. 2011 20122013 2014 2015 20162017 ActualActual Actua Actua Forecast Forecast Forecast Key Assumptions Sales Growth Rate Tax Rate Dividend Payout Rate 16.0% 40.0% 35.0%| 13.0%| 40.0% 35.0%| 11.0% 40.0% 35.0% Additional assumptions: 1) Sales grow as a percent of the previous year, 2) CoGS, Selling, Gen & Adm Expenses, CA and CL grow at the same percentage as Sales, 3) No additional debt is incurred so forecast Interest Expense and Long-term Liabilities will remain the same as 2014, 4) No additional PPE are purchased, and 5) Paid-in Capital remains constant at the 2014 level. Construct formulas to calculate the values in the pink and blue cells. Income Statement (Mil.S) Sales Cost of Goods Sold Gross Profit $73.84 $93.28$115.93 $138.84 $41.83 $58.39 $75.49 $89.83 32.01$34.89 $40.44 $49.0:1 Selling, Gen & Adm Expenses6.58 $7.28 $8.56 $10.21 Depreciation $5.91 $6.37 $7.31 $9.86 $9.86 $9.86$9.86

Answer 1

I am attaching two pictures of the formula screen. Please go through them line by line. That will help you establish the formula.

After that I am pasting the entire output so that you know the values.

	2011	2012	2013	2014	2015	2016	2017
	Actual	Actual	Actual	Actual	Forecast	Forecast	Forecast
Key Assumptions
Sales growth rate					16.00%	13.00%	11.00%
tax rate					40.00%	40.00%	40.00%
Dividend payout rate					35.00%	35.00%	35.00%
Income Statement
Sales	73.84	93.28	115.93	138.84	161.05	181.99	202.01
COGS	41.83	58.39	75.49	89.83	104.20	117.75	130.70
Gross Profit	32.01	34.89	40.44	49.01	56.85	64.24	71.31
Selling, General & Admin Expenses	6.58	7.28	8.56	10.21	11.84	13.38	14.86
Depreciation	5.91	6.37	7.31	9.86	9.86	9.86	9.86
EBIT	19.52	21.24	24.57	28.94	35.15	41.00	46.59
Interest expenses	4.76	5.23	6.69	8.88	8.88	8.88	8.88
Taxes	6.21	6.96	7.52	7.60	10.51	12.85	15.09
Net income	8.55	9.05	10.36	12.46	15.76	19.27	22.63
Allocation of net income
Dividends	2.90	3.17	3.63	4.36	5.52	6.74	7.92
Addition to Retained earnings		5.88	6.73	8.10	10.24	12.53	14.71
	2011	2012	2013	2014	2015	2016	2017
Balance Sheet	Actual	Actual	Actual	Actual	Forecast	Forecast	Forecast
Asset
Current Assets
Cash & equivalent	4.27	6.38	7.62	8.83	10.24	11.57	12.85
Receivables	20.58	24.39	28.77	34.11	39.57	44.71	49.63
Inventory	26.73	30.45	36.75	43.27	50.19	56.72	62.96
Total Current Assets	51.58	61.22	73.14	86.21	100.00	113.00	125.43
PPE	331.64	423.92	503.87	613.28	613.28	613.28	613.28
Accum Dep	98.72	105.09	112.40	122.26	132.12	141.98	151.84
Net PPE	232.92	318.83	391.47	491.02	481.16	471.30	461.44
Total Assets	284.50	380.05	464.61	577.23	581.16	584.30	586.87
Liabilities & Shareholder's equity
Current Liabilities
Accounts Payable	31.83	63.43	83.84	94.41	109.52	123.75	137.37
Short term debt	30.86	43.03	64.85	79.49	92.21	104.20	115.66
Total Current Liabilities	62.69	106.46	148.69	173.90	201.72	227.95	253.02
Long term debt	40.00	45.90	51.50	70.81	70.81	70.81	70.81
Total Liabilities	102.69	152.36	200.19	244.71	272.53	298.76	323.83
Shareholder's equity
Paid in capital	90.00	130.00	160.00	220.00	220.00	220.00	220.00
Retained earnings	91.81	97.69	104.42	112.52	122.76	135.29	150.00
Total Shareholder's equity	181.81	227.69	264.42	332.52	342.76	355.29	370.00
Total Liab & shareholder's equity	284.50	380.05	464.61	577.23	615.30	654.05	693.83
TA - (TL + TE)	-	-	-	-	(34.13)	(69.74)	(106.96)