Question

1. You are examining the desirability of selling widgets. To conduct the analysis, you’ve estimated that you will sell 1 million units and will sell the units for $99 each for the next five years. The variable costs for producing this product is estimated to be $32 per unit. Fixed costs will be $3 million per year. The equipment used to produce the widgets will cost $20 million and will be depreciated using the MACRS depreciation schedule for a five-year useful life. In year 5, the equipment will be sold for 20% of its original cost. The tax rate is 21%. Construct a proforma income statement to find the net income and operating cash flow for each of the five years of the project.

Kindly use excel to develop the proforma in this format. And please show work. I started but need assistance.

Tax Rate	21%
Starting Sales	$1,000,000.00
Sales Growth Rate
Price	$99.00
COGS %
SGA %
Depreciation
Year	$0	1	2	3	4	5
Sales Revenue		$99,000,000	$99,000,000.00	$99,000,000.00	$99,000,000.00	$99,000,000.00
COGS $
SGA
Depreciation
Operating Profit
Taxes
Net Profit
Operating Cash Flow

Answer 1

Calculation of Operating Cash flows
Patriculars	0	1	2	3	4	5
Depreciation rate (A)		20%	32%	19.20%	11.52%	11.52%
Annual Sales (B = 1,000,000 * $99)		99,000,000	99,000,000	99,000,000	99,000,000	99,000,000
Variable Costs (C = 1,000,000 * $32)		32,000,000	32,000,000	32,000,000	32,000,000	32,000,000
Fixed Costs (D)		3,000,000	3,000,000	3,000,000	3,000,000	3,000,000
Depreciation (E = $20,000,000 * A)		4,000,000	6,400,000	3,840,000	2,304,000	2,304,000
Profit Before Tax (F = B-C-D-E)		60,000,000	57,600,000	60,160,000	61,696,000	61,696,000
Tax @ 21% (G = F*21%)		12,600,000	12,096,000	12,633,600	12,956,160	12,956,160
Net Income (H = F-G)		47,400,000	45,504,000	47,526,400	48,739,840	48,739,840
Add back Depreciation (I = E)		4,000,000	6,400,000	3,840,000	2,304,000	2,304,000
Operating Cash Flows (J = H+I)		51,400,000	51,904,000	51,366,400	51,043,840	51,043,840