Project the financial statements for 2017 and 2018. Use the line of credit account to balance...

Transcribed Image Text:

Project the financial statements for 2017 and 2018. Use the line of credit account to balance (plug) your balance sheets. Perform a sensitivity analysis on your plug by calculating its values at different values of accounts receivable, accounts payable, and inventory. Is there any seasonality that could affect your plug? M8 A fx Assumptions 1 2 Cash 3 Accounts Receivable 4 Inventory 5 Pre-paid Expenses 6 Total Current Assets 7 Fixed Assets B C D E F 2014 2015 2016 2017 same $ previous year $33,205 $15,772 $12,306 reduce to 60 days of sales $1,502,707 $1,697,974 $2,047,376 89 days of COGS $1,424,515 $1,552,180 $1,563,464 same $ previous year calculated $51,364 $55,016 $53,012 $3,011,791 $3,320,942 $3,676,128 Bland given Equipment and Leaseholds old +1,300,000 building $1,986,479 $2,254,618 $2,487,382 0 Less: Accumulated Depreciatio previous year + current yr exp $871,536 $971,470 $1,083,325 1 Net Fixed Assets calcultaed $1,114,943 $1,283,148 $1,404,057 2 Intangible Assets same $ previous year $54,975 $54,975 $54,975 3 calculated $4,181,679 $4,659,035 $5,135,130 4 Total Assets 5 6 7 Line of Credit (50k) 8 Accounts Payable 9 Customer Deposits 20 Accrued Expenses Payable 1 Current portion of LTD plug figure to balance 2 Total Current Liabilities 23 24 Long-Term Debt 5 original loan 26 new loan 27 28 Total Liabilities 29 O Capital, Brandon Wood. 11 Capital, Katherine Yates 2 Retained Earnings 3 Total Shareholders' Equity 14 5 Total Liabilities and Equity 16 17 18 $0 $0 $0 reduce to 30 days of purchases $543,916 $661,454 $808,091 same $ as previous year $86,711 $85,444 $88,807 same $ as previous year incr by $200,000 $75,489 $77,303 $76,575 $105,003 $105,003 $105,003 $811,119 $929,204 $1,078,476 $463,748 $358,745 $253,742 reduce by pmts 105003 150000 less 200000 to C.P. less payment of 200,000 $1,274,867 $1,287,949 $1,332,218 capital same $ $10,000 $10,000 $10,000 capital same $ from projected SRE 15,000 15,000 2,881,812 3,341,620 15,000 3,785,368 $2,906,812 $3,371,085 $3,814,833 $4,181,679 $4,659,034 $5,135,130 G H 2018 Paste D31 Aptos Narrow (Bod... v 11 v BIU fx ' A General Conditional Formatting Format as Table $ % 9 .00 0 Cell Styles 1 2 Net Sales 3 Cost of Sales A 4 Add: Beginning inv 5 Add: Purchases 6 Less: Ending inventory 7 Gross Profit 8 Operating Expenses: 9 Administrative 10 Miscellaneous 11 Research & Development 12 Sales & Marketing 13 Rent 14 Depreciation 15 Total Operating Expenses 16 Operating Income 17 Other Income 18 Interest and Bank Charges 19 Net Income before Taxes 20 Taxes 21 Net Income (Loss) Assumptions B increase by 10% each year estimate 2014 level 65.8% ending inv from pervious year COGS +EI-BI no ge change in 89 days sales - cogs incr by $105,000 same percentage 2.5% 2.7% and 5% of sales same percentage 3.9% rent eliminated last year +$1,300,000/ 15 years sum operating expesnes gross profit - operating exp same $ booth years last year + $80,000 calculated based on previous year 22% NIBT calculated C D E F G H 2014 2015 2016 2017 2018 $9,390,310.00 $ 9,581,949.00 $6,225,776.00 $ 6,426,582.00 $ 9,296,407.00 $ 6,117,036.00 $ 3,179,371.00 $3,164,534.00 $3,155,367.00 963,544.00 $ 966,981.00 $ 965,589.00 $ 241,707.00 $ 225,367.00 $ 241,300.00 278,892.00 $ 272,319.00 $ 256,300.00 SSSS $ $ 381,153.00 $ 369,833.00 $ 377,380.00 160,854.00 $ 160,854.00 $ 160,854.00 99,934.00 $ 99,934.00 $ 111,855.00 $ 2,126,083.00 $2,095,288.00 $ 2,113,279.00 $ 1,053,288.00 $1,069,246.00 $ 1,042,088.00 $ 6,885.00 $ 6,387.00 $ 12,195.00 40,811.00 $ 39,114.00 $ 38,517.00 $ 1,019,362.00 $1,036,519.00 $ 1,015,766.00 SSSS 224,260.00 $ 230,891.00 $ 248,734.00 795,102.00 $ 804,339.00 $ 767,033.00 24 29 30 31 34 222222222223583333 26 27 36 37 IS BS RE SCF + Project the financial statements for 2017 and 2018. Use the line of credit account to balance (plug) your balance sheets. Perform a sensitivity analysis on your plug by calculating its values at different values of accounts receivable, accounts payable, and inventory. Is there any seasonality that could affect your plug? M8 A fx Assumptions 1 2 Cash 3 Accounts Receivable 4 Inventory 5 Pre-paid Expenses 6 Total Current Assets 7 Fixed Assets B C D E F 2014 2015 2016 2017 same $ previous year $33,205 $15,772 $12,306 reduce to 60 days of sales $1,502,707 $1,697,974 $2,047,376 89 days of COGS $1,424,515 $1,552,180 $1,563,464 same $ previous year calculated $51,364 $55,016 $53,012 $3,011,791 $3,320,942 $3,676,128 Bland given Equipment and Leaseholds old +1,300,000 building $1,986,479 $2,254,618 $2,487,382 0 Less: Accumulated Depreciatio previous year + current yr exp $871,536 $971,470 $1,083,325 1 Net Fixed Assets calcultaed $1,114,943 $1,283,148 $1,404,057 2 Intangible Assets same $ previous year $54,975 $54,975 $54,975 3 calculated $4,181,679 $4,659,035 $5,135,130 4 Total Assets 5 6 7 Line of Credit (50k) 8 Accounts Payable 9 Customer Deposits 20 Accrued Expenses Payable 1 Current portion of LTD plug figure to balance 2 Total Current Liabilities 23 24 Long-Term Debt 5 original loan 26 new loan 27 28 Total Liabilities 29 O Capital, Brandon Wood. 11 Capital, Katherine Yates 2 Retained Earnings 3 Total Shareholders' Equity 14 5 Total Liabilities and Equity 16 17 18 $0 $0 $0 reduce to 30 days of purchases $543,916 $661,454 $808,091 same $ as previous year $86,711 $85,444 $88,807 same $ as previous year incr by $200,000 $75,489 $77,303 $76,575 $105,003 $105,003 $105,003 $811,119 $929,204 $1,078,476 $463,748 $358,745 $253,742 reduce by pmts 105003 150000 less 200000 to C.P. less payment of 200,000 $1,274,867 $1,287,949 $1,332,218 capital same $ $10,000 $10,000 $10,000 capital same $ from projected SRE 15,000 15,000 2,881,812 3,341,620 15,000 3,785,368 $2,906,812 $3,371,085 $3,814,833 $4,181,679 $4,659,034 $5,135,130 G H 2018 Paste D31 Aptos Narrow (Bod... v 11 v BIU fx ' A General Conditional Formatting Format as Table $ % 9 .00 0 Cell Styles 1 2 Net Sales 3 Cost of Sales A 4 Add: Beginning inv 5 Add: Purchases 6 Less: Ending inventory 7 Gross Profit 8 Operating Expenses: 9 Administrative 10 Miscellaneous 11 Research & Development 12 Sales & Marketing 13 Rent 14 Depreciation 15 Total Operating Expenses 16 Operating Income 17 Other Income 18 Interest and Bank Charges 19 Net Income before Taxes 20 Taxes 21 Net Income (Loss) Assumptions B increase by 10% each year estimate 2014 level 65.8% ending inv from pervious year COGS +EI-BI no ge change in 89 days sales - cogs incr by $105,000 same percentage 2.5% 2.7% and 5% of sales same percentage 3.9% rent eliminated last year +$1,300,000/ 15 years sum operating expesnes gross profit - operating exp same $ booth years last year + $80,000 calculated based on previous year 22% NIBT calculated C D E F G H 2014 2015 2016 2017 2018 $9,390,310.00 $ 9,581,949.00 $6,225,776.00 $ 6,426,582.00 $ 9,296,407.00 $ 6,117,036.00 $ 3,179,371.00 $3,164,534.00 $3,155,367.00 963,544.00 $ 966,981.00 $ 965,589.00 $ 241,707.00 $ 225,367.00 $ 241,300.00 278,892.00 $ 272,319.00 $ 256,300.00 SSSS $ $ 381,153.00 $ 369,833.00 $ 377,380.00 160,854.00 $ 160,854.00 $ 160,854.00 99,934.00 $ 99,934.00 $ 111,855.00 $ 2,126,083.00 $2,095,288.00 $ 2,113,279.00 $ 1,053,288.00 $1,069,246.00 $ 1,042,088.00 $ 6,885.00 $ 6,387.00 $ 12,195.00 40,811.00 $ 39,114.00 $ 38,517.00 $ 1,019,362.00 $1,036,519.00 $ 1,015,766.00 SSSS 224,260.00 $ 230,891.00 $ 248,734.00 795,102.00 $ 804,339.00 $ 767,033.00 24 29 30 31 34 222222222223583333 26 27 36 37 IS BS RE SCF +