CAN SOME PLEASE NOT HARDCODE the numbers. THE ANSWER IS ATTACHED AS AN IMAGE BUT I NEED THE FORMULAS FOR THE ANSWER SO IT IS NOT HARDCODED. The image below is hardcoded. I need it to not be hardcoded.

the following assumptions are in the assumption part of the spreadsheet (grey highlighted cells at the top of the spreadsheet) and then reference those cells when projecting the appropriate income statement, balance sheet, or break-even account. Do NOT hard code numbers into your projections.

2018 2020 Income Statement and Balance Sheet.

Assumptions:

Sales growth rates in each of next 3 years: 20%, 25%, 30%

Gross margin and operating expenses/sales percentage stay at 2017 level

40% tax rate

No dividends

Interest expense fixed at $400 / year

Bank loan and LT debt stay constant

2018 A/R days = year 2017 days, then decrease by 3 days in 2019 &

another 3 days in 2020

2018 Inventory days = year 2017 days then decrease by 3 days in 2019 &

2 days 2020

A/P days stays at year 2017 days

Net fixed assets and accruals keep relationship with sales

Required cash fixed at 1000

Calculate Additional Funds Needed as plug (to get Balance Sheet to

balance!)

Calculate 2017 breakeven revenues and units. Assumptions:

All operating expenses are fixed

Average price per unit is $50

I added the excel version so you can see the cell numbers for reference

All the info that is needed is attached. The answers are currrently hardcoded and I need them not to be. I need to know the formulas used to get the answer.

average price per unit is supposed to be 50. Ignore the 65 in the excel spreadsheet

2017 Assumptions Sales Growth % of Current Sales Av. Price per item Cost of Goods sold % of sales Operating Expenses % of Forecast Sales Tax rate of EBT Dividend rate of NI Required Cash of Forecast Sales A/R days Inventory days Net FA % of Forecast Sales Days Payable Accrued Liability of Forecast Sales 2018 2019 0.2 0.25 03 65 65 65 70 70 70 16.667 16.667 16,667 40 40 40 0 0 0 5.56 4.44 3.42 48 45 42 52.8 49.8 50.8 45 45 45 54.86 54.86 54.86 8 8 8 Particulars Actual 2017 Forecast 2018 2019 2020 Net Sales Cost of Goods Sold Gross Profit Operating Exp Interest EBT Taxes (40%) Net Income 15000 10500 4500 -2500 -400 1600 -640 960 18000 22500 29250 12600 15750 20475 5400 6750 8775 -3000 -3750 -4875 -400 -400 - 400 2000 2600 3500 -800 -1040 -1400 1200 1560 2100 0 Cash Dividends (% of NT) Added Retained Earnings 0 1200 0 0 1560 2100 960 Balance Sheet 1000 2000 2200 Required Cash A/R (WNI) Inventories (WN 2) Total Current Assets Net Fixed Assets Total Assets 1000 2400 2640 6040 8159 14199 5200 6800 12000 1000 1000 28133413 3113 4128 6926 8541 10199 13259 17125 21800 Accounts Payable (WN 3) Bank Loan Accrued Liability Total Current Liability Long term Debt Common Stock Retained Earnings Additional Funds needed Total Liability & Equity 1600 1800 1200 4600 2200 2400 2800 0 12000 1920 1800 1440 5160 2200 2400 4000 439 2400 3120 1800 1800 1800 2340 6000 7260 2200 2200 2400 2400 5560 7660 965 2280 17125 21800 14199 Calibri (Body) 12 A- A+ Ct Copy ST) Wrap Text General Paste B Merge & Center $ - % > 00 0 Format Conditio Formate P17 X fx B G H K D F Assumptions (use these to drive projected IS and BS) 0.2 0.25 0.3 65 65 65 70 70 70 16.667 16.667 16.667 40 40 40 0 0 0 5.56 4.44 3.42 48 45 42 52.8 49.8 50.8 45 45 45 54.86 54.86 54.86 8 2 Sales Growth % of Current Sales 3 Average pricer per item 4 Cost of Goods Sold X of Sales 5 Operating Expense % of Forecast Sales 6 Tax Rate % of EBT 7 Dividend Rate % of NI 8 Required Cash of Forecast Sales 9 A/R days 10 Inventory Days 11 Net Fixed Assets of Forecast Sales 12 Days Payable 13 Accrued Liability of Forecast Sales 14 15 Actual 16 2017 17 Net Sales 15000 18 Cost of Goods Sold 10500 19 Gross Profit 4500 20 Operating exp -2500 21 Interest -400 22 EBT 1600 23 Taxes (40%) -640 24 Net Income 960 25 26 Cash Dividends (0% of NI) 0 27 Added Retained Earnings 960 28 29 Balance Sheet 30 Required Cash 1000 31 A/R 2000 32 Inventories 2200 33 Total Current Assets 5200 34 Net Fixed Assets 6800 35 Total Assets 12000 >>>>>>>>>>>> 2018 2019 2020 18000 22500 29250 12600 15750 20475 5400 6750 8775 -3000 -3750 -4875 -400 -400 -400 2000 2600 3500 -800 -1040 -1400 1200 1560 2100 0 1200 0 1560 2100 1000 2400 2640 6040 8159 14199 1000 2813 3113 6926 10199 17125 1000 3413 4128 8541 13259 21800 36 1600 1800 1200 4600 2200 2400 2800 0 12000 1920 1800 1440 5160 2200 2400 4000 439 14199 2400 1800 1800 6000 2200 2400 5560 965 17125 3120 1800 2340 7260 2200 2400 7660 2280 21800 37 Accounts Payable 38 Bank Loan 39 Accured Liability 40 Total Current Liability 41 Long Term Debt 42 Common Stock 43 Retained Earnings 44 Additional Funds Needed (AFN) 45 Total Liability & Equity 46 47 BREAKEVEN CALCULATION 48 Fixed Costs 49 Gross margin 50 BREAKEVEN REVENUES 51 Price per unit 52 Cost per unit 53 Contribution per unit 54 BREAKEVEN UNITS 55 56 57 58 59 60 2500 42.86 3000 42.86 3750 42.86 4875 42.86 65 45.5 19.5 65 45.5 19.5 65 45.5 19.5 65 45.5 19.5