I need forecasts for a balance sheet, income statement, and financial ratios. I have some work done...

Question

I need forecasts for a balance sheet, income statement, and financial ratios. I have some work done already.

Financials and Forecast Midwest ShocksMidwest Shocks Consolidated Balance SheetConsolidated Income Statement Days ($ in 000s) ActualActualManagement ForecastGroup ForecastManagement AssumptionsGroup AssumpitionsAssumption($ in 000s) ActualActualActual Management ForecastForecast% of SalesGroup ForecastManagement AssumptionsGroup Assumpitions365 Assets 12/31/1812/31/1912/30/2012/31/20Inputs12/31/1712/31/1812/31/1912/30/2012/30/2012/31/20Assumptions Inputs 2019 Income Statement Growth Rate Cash 38,34924,22427,913PlugNet Sales628,800474,831544,111580,000600,0006.7% GrowthNet Sales A/R Net 75,65469,82972,70369,82945.75 dohCost of Sales548,499356,435397,386386,8003.45%73.03%% Growth14.59%-24%-4.95% Inventory 174,066157,134153,172157,134144.54 dohGross Profit80,301118,396146,725193,200386,800Significant improvement from strategy COGS Other Current Assets 14,82816,72133,314~100% growthGross Profit Margin12.77%24.93%26.97%33.31%193,200% Growth11.49% Total Current Assets302,897267,908287,102SG&A Expense50,10055,97192,205134,502134,502Sales Force GrowthEBIT Land and Improvements 20,06718,48618,486No additionsDepreciation Expense13,6007,68712,65413,10013,100Deprec. Schedule% Growth-17.53% Buildings 43,84237,80537,805No additionsOther expenses14,4005,2803,0003,000Misc.EBT Equipment 40,64461,16281,264~33% growthRestructuring-19,7649,4312,5982,598Nearly CompleteRemains the same% Growth-15.83% Accum. Depreciation 7,36019,45032,550Deprec. ScheduleManagement fees-3,9761,5915,0005,000ContractualRemains the sameEarnings Before Exo Item PP&E - Net 97,19398,003105,005Total Operating Expenses78,10087,398121,161158,200158,20022.27%22.27% of sales% Growth-15.33% Intangibles 03,0882,779S/L Amort.Earnings Before Int. & Tax2,20130,99825,56435,00035,000Bargain Purchase Gain Other L/T Assets 6,5277,5411,307DispositionInterest Expense(2,100)(2,588)(4,620)(4,000)(4,000)3.3% average rate% Growth-100.00% Total Non-Current Assets 103,720108,632109,091Interest Income06208570- Total Assets406,617376,540396,193Dividend on Preferred Stock0( 1,099)00-2019 Balance Sheet Growth Rate FX Exchange Loss'Restructnring/Other1,500(4,772)(2,307)(1,500)(1,500)A/R Liabilities and EquityEarnings Before Tax1,60123,15919,49429,50029,500% Growth-7.70% Accounts Payable 58,62654,43162,85854,43159.32 dohIncome Tax Expense10,2009,4687,9027,3757,375Cross Border BenefitsA/P Accrued Compensation 15,59315,33318,941based on pay dateEffective Tax Rate637.1%40.9%40.5%25.0%% Growth-7.16% Other Accrued Expenses 40,55050,70149,246no meaningful chg.Earnings Before Exo Item(8,599)13,69111,59222,12522,125Inventory Taxes Payable 10,2009127,576From Tax AttorneyBargain Purchase Gain- 0147,22000-one-time event% Growth-9.73% CMLTD000Net Earnings(8,599)160,91111,59222,12522,125 TOTAL CURRENT LIABILITIES 124,969121,377138,621Dividends - Common-(34,400)(45,000)(40,000)(40,000) ABL - Revolving Loan 50,00043,00067,247PlugEBITDA15,80138,68538,21848,10048,100Meaningful Increase Senior Debt (foreign) 11,92811,92811,928No changeNOPAT(11,822)18,32515,20126,250 Senior Debt (#1)35,12232,97632,976No change Senior Debt (#2)020,98120,981No change Other Long-Term Liabilities4,4342,7221,820No meaningful chg2018 Bargain Gain147,220 Deferred Income Taxes 2,5374,8313,589From Tax AttorneyMidwest Shocks TOTAL NON-CURRENT LIAB'S. 104,021116,438138,541Financial Ratios TOTAL LIABILITIES 228,990237,815277,162MangamentGroup 12/31/1712/31/1812/31/19ForecastForecastNotes EQUITYProfitability12/30/2012/30/20 Common Stock292929No changeNo ChangeGross Profit Margin GP/Sales12.77%24.93%26.97%33.31%firm’s ability to begin to convert sales dollars into profits/cash flow measures demand for a product and effeciencies Additoninal Paid-In Capital37,87038,23238,232No changeNo ChangeOperating Profit Margin OP/Sales0.35%6.53%4.70%6.03%Reflects Managements ability to control selling, general, admin costs Measures overall operating effeciencny along w/ rev. and exp. From operating activities Retained Earnings136,521103,11385,238Net Profit Margin NP/Sales-1.37%33.89%2.13%3.81%Reflects all of the above including costs associated to leverage (interest expense) and income taxes Accum. Other Comp. (Loss)/lncome3,207(2,649)(4,468)Derivative estimate Total Shareholder's Equity177,627138,725119,031Efficiency TOTAL LIABILITIES AND EQUITY 406,617376,540396,193A/R doh AR/(Sales/365)58.1546.8445.75Indicates time required to convert receivables into cash aka avg num. of days between invoice date and collection date Check$ - 0$ - 0$ - 0$ - 0Inventory doh Inv/(COGS/365)178.25144.33144.54Indicates days required to sell inventory. COGS is used here and not sales measuring at wholesale level (from buying raw mats to finished goods to cash or from inventory to cash) A/P doh Sales/(COGS/365)60.0350.0059.32Indicates days required to pay suppliers. Total Asset turnover Sales/Tot Assets1.171.451.46Assesses management’s effectiveness in generating sales from investments in all assets (current and long-term) Midwest ShocksFixed Asset Turnover Sales/PP&E4.895.555.52Management’s effectiveness in generating sales from the investment in fixed assets where High ratio generally means only a small investment is required to generate sales (and thus the firm will be more profitable). Consolidated Statement of Cash FlowsLeverage Funded Debt/Equity (Debt to Equity)1.291.712.33Measures the riskiness of the firm’s capital structure in terms of the relationship between the funds supplied by creditors (debt) and investors (equity) ($ in OOOs)Actual 12/31/2018Actual 12/31/2019Management ForecastGroup ForecastManagementGroupLTD/Total Capital LTD/(LTD+SE)0.370.460.54The extent to which Long Term Debt is used for the firms permanent financing needs Operating Activities2018201912/30/2012/31/20AssumptionsAssumptionsTotal Debt/Total Capital0.560.630.70The extent to which all debt is used to fund the firms financing needs Net Income160,91111,59222,125Funded Debt/EBITDA1.221.721.37Most commonly used Leverage mesure by creditors Measured in terms of years for how quickly/long they can pay off all their debt EBITDA is not used to pay debt b/c of interest and tax this is a broad generalization but more accurate than CFFO or FCF Depreciation7,68712,65413,100Times Interest Earned OP Prof/Int Exp11.985.538.75Measures the comfort for

midwest-shocksfinancialscochran-agoql4uw-qebu2vfh.xlsx

Akshay Kumar · Accepted Answer

Financials and Forecast
		Midwest Shocks									Midwest Shocks
		Consolidated Balance Sheet									Consolidated Income Statement
																					Days
		($ in 000s)                                            	Actual	Actual	Management Forecast	Group Forecast	Management Assumptions	Group Assumpitions	Assumption		($ in 000s)   	Actual	Actual	Actual 	Management Forecast	Forecast	% of Sales	Group Forecast	Management Assumptions	Group Assumpitions	365
		Assets                                          	12/31/18	12/31/19	12/30/20	12/31/20			Inputs			12/31/17	12/31/18	12/31/19	12/30/20	12/30/20		12/31/20			Assumptions Inputs		 2019  Income Statement Growth Rate
		Cash                                                                      	38,349	24,224	27,913	29,500	Plug	Plug			Net Sales	628,800	474,831	544,111	580,000	600,000		590,000	6.7% Growth	8.4% Growth			Net Sales
		A/R Net                                                                 	75,654	69,829	72,703	69,829	45.75 doh	43.20 doh			Cost of Sales	548,499	356,435	397,386	386,800	3.45%	73.03%	421,850		71.5% of Sales			% Growth	14.59%	-24%	-4.95%
		Inventory                                                               	174,066	157,134	153,172	157,134	144.54 doh	135.96 doh			Gross Profit	80,301	118,396	146,725	193,200	386,800		168,150	Significant improvement from strategy 	improvement from strategy 			COGS
		Other Current Assets                                             	14,828	16,721	33,314	30,098	~100% growth	~80% growth			Gross Profit Margin	12.77%	24.93%	26.97%	33.31%	64.47%		28.50%					% Growth	11.49%
		Total Current Assets	302,897	267,908	287,102	286,561					SG&A Expense	50,100	55,971	92,205	134,502	134,502		107,181	Sales Force Growth				EBIT
		Land and Improvements                                	20,067	18,486	18,486	18,486	No additions	No additions			Depreciation Expense	13,600	7,687	12,654	13,100	13,100		13,100	Deprec. Schedule	Deprec. Schedule			% Growth	-17.53%
		Buildings                                                                	43,842	37,805	37,805	37,805	No additions	No additions			Other expenses	14,400		5,280	3,000	3,000		3,500	Misc.	Misc.			EBT
		Equipment                                                       	40,644	61,162	81,264	81,264	~33% growth	~33% growth			Restructuring	-	19,764	9,431	2,598	2,598		2,598	Nearly Complete	Nearly Complete			% Growth	-15.83%
		Accum. Depreciation                                           	7,360	19,450	32,550	32,550	Deprec. Schedule	Deprec. Schedule			Management fees	-	3,976	1,591	5,000	5,000		5,000	Contractual	Contractual			Earnings Before Exo Item
		PP&E - Net                                                         	97,193	98,003	105,005	105,005					Total Operating Expenses	78,100	87,398	121,161	158,200	158,200	22.27%	131,379		22.27% of sales			% Growth	-15.33%
		Intangibles                                                            	0	3,088	2,779	2,779	S/L Amort.	S/L Amort.			Earnings Before Int. & Tax	2,201	30,998	25,564	35,000	35,000		36,771					Bargain Purchase Gain
		Other L/T Assets                                            	6,527	7,541	1,307	2,541	Disposition	Disposition			Interest Expense	(2,100)	(2,588)	(4,620)	(4,000)	(4,000)		(4,000)	3.3% average rate	3.3% average rate			% Growth	-100.00%
		Total Non-Current Assets                                    	103,720	108,632	109,091	110,325					Interest Income	0	620	857	0	-		0		No Investment
		Total Assets	406,617	376,540	396,193	396,886					Dividend on Preferred Stock	0	( 1,099)	0	0	-		0		No Preferred Stock			2019 Balance Sheet Growth Rate
											FX Exchange Loss'Restructnring/Other	1,500	(4,772)	(2,307)	(1,500)	(1,500)		(3,605)					A/R
		Liabilities and Equity									Earnings Before Tax	1,601	23,159	19,494	29,500	29,500		29,166					% Growth	-7.70%
		Accounts Payable                                               	58,626	54,431	62,858	54,431	59.32 doh	65.33 doh			Income Tax Expense	10,200	9,468	7,902	7,375	7,375		8,750	Cross Border Benefits	Cross Border Benefits are not same as expected			A/P
		Accrued Compensation                                             	15,593	15,333	18,941	20,283	based on pay date	based on pay date			Effective Tax Rate	637.1%	40.9%	40.5%	25.0%			30.0%					% Growth	-7.16%
		Other Accrued Expenses  	40,550	50,701	49,246	48,714	no meaningful chg.	no meaningful chg.			Earnings Before Exo Item	(8,599)	13,691	11,592	22,125	22,125		20,416					Inventory
		Taxes Payable                                                        	10,200	912	7,576	6,941	From Tax Attorney	From Tax Attorney			Bargain Purchase Gain	- 0	147,220	0	0	-		0	one-time event	one-time event			% Growth	-9.73%
		CMLTD	0	0	0	0					Net Earnings	(8,599)	160,911	11,592	22,125	22,125		20,416
		TOTAL CURRENT LIABILITIES                              	124,969	121,377	138,621	130,369					Dividends - Common	-	(34,400)	(45,000)	(40,000)	(40,000)		(40,000)		Remains the same as Management forecast
		ABL - Revolving Loan                                  	50,000	43,000	67,247	72,000	Plug	Plug			EBITDA	15,801	38,685	38,218	48,100	48,100		49,871	Meaningful Increase
		Senior Debt (foreign)                                           	11,928	11,928	11,928	11,928	No change	No change			NOPAT	(11,822)	18,325	15,201	26,250
		Senior Debt (#1)	35,122	32,976	32,976	36,000	No change	Plug
		Senior Debt (#2)	0	20,981	20,981	20,981	No change	No change
		Other Long-Term Liabilities	4,434	2,722	1,820	2,200	No meaningful chg	No meaningful chg					2018 Bargain Gain	147,220
		Deferred Income Taxes                               	2,537	4,831	3,589	4,189	From Tax Attorney	From Tax Attorney			Midwest Shocks
		TOTAL NON-CURRENT LIAB'S.                             	104,021	116,438	138,541	147,298					Financial Ratios
		TOTAL LIABILITIES                                    	228,990	237,815	277,162	277,667									Mangament	Group
												12/31/17	12/31/18	12/31/19	Forecast	Forecast			Notes
		EQUITY									Profitability				12/30/20	12/30/20
		Common Stock	29	29	29	29	No change	No Change			Gross Profit Margin      GP/Sales	12.77%	24.93%	26.

Financials and Forecast Midwest Shocks Midwest Shocks Consolidated Balance Sheet Consolidated Income Statement Days ($ in 000s) ActualActualManagement ForecastGroup ForecastManagement...

Answer To: Financials and Forecast Midwest Shocks Midwest Shocks Consolidated Balance Sheet Consolidated Income...

Answer To This Question Is Available To Download

Related Questions & Answers

Submit New Assignment