Does this work answer the requirements below it?

Does this work answer all the requirements below it? Balance Sheet Year End 31 Dec, 2017 ASSETS Current Assets Cash Accounts Receivable Inventory Total Current Assets Property plant and equipment Equipment Accumulated Deprciation TOTAL ASSETS 50000 175000 126000 351000 480000 -90000 390000 741000 156000 12000 168000 LIABILITY AND OWNERS EQUITY Current Liabilities Accounts Payablc Short-term Notes Payable Total Current Libabilities Long term liabilities Long-term Notes Payable Owners Equity Common Stock Retained Earnings TOTAL LIABILITY AND OWNERS EQUITY 200000 235000 138000373000 741000 Expected Unit Sales Each Month Jan Feb 10500 11025 Mar 11576 Apr 12155 Expected Cnit Sales Expected Cnit Sales Unit Selling Price Total Sales Sales Budget Jan 10500 25 262500 Feb 11025 25 275625 Mar 11576 25 289406 Otr Apr 33101 12155 25 25 827531 303877 11576 33101 12155 Purchases budget Budgeted Sales in units 10500 11025 Add:Ending inventory reqd.(80% next mth sales) 88209261 Total units needed 19320 20286 Less: Beginning inventory 8400 8820 Budgeted Purchases( units) 10920 11466 Purchase price/unit 15 Budgeted purchases (S) 163800 171987 9724 21300 9261 12039 15 180585 9724 42825 8400 34425 15 516375 15 Budgeted Sales in units COGS at S 15/unit COGS Budget 10500 157500 11025 11576 165375 173643.75 33101 496515 Cost of Mar 31, ending inventory 145860 Selling expense Budget Budgeted sales | 262500 275625 Sales Rep. commn at 12.5%* Sales $ 32813 34453 Sales mgr. salary 3500 4000 Total sell.exp. Budgeted 36313 38453 289406 36176 4000 40176 827531 103441 11500 114941 General and Administrative Expense Budget Administrative salaries 8000 8000 Interest exp.(0.9%*200000) 1800 1800 Total cash G&A exp. 9800 9800 Depreciation 5000 5000 Total G&A exp. 14800 14800 Without interest exp.(for IS) 8000 1800 9800 5000 14800 24000 5400 29400 15000 44400 39000 Budgeted sales $ Cash sales (30%) Credit sales of: Expected Cash Receipts From Customers 262500 275625 7875082688 289406 86822 827531 248259 175000 17-Dec 18-Jan 18-Feb 183750 175000 183750 192938 799947 192938 279760 Total sales collections expected 253750 266438 Exp. March 31 balance of accounts receivable 202584 Expected Cash Payments 163800 171990 180590 516380 Budgeted purchases (S) Payment for purchases of: 156000 17-Dec 18-Jan 18-Feb 163800 156000 163800 171990 491790 171990 171990 Total Payment for Purchases 156000 163800 Exp. March 31 balance of accounts payable. 180590 Cash Budget 50000 89518 253750 266438 303750355955 50000 279759 329759 50000 799947 849947 156000 36313 9800 Beginning balance Total sales collections exp. Total cash available Less: Disbursements: Total pmt. For purchases Total sell.exp. Budgeted Total cash G&A exp. Dividends Equipment purchase Total disbursements Surplus (Delicit) Add: Borrowings Less: Repayments Less: Interest Ending balance 163800 38453 9800 100000 171990 40176 9800 55000 276966 52793 202113312053 101638 43902 6098 12000 120 89518 50000 491790 114941 29400 100000 55000 791131 58815 6098 14732 181 50000 2732 61 50000 Budgeted Income Statement Sales revenue 827531 Less: COGS 496519 Gross profit 331012 Less: Operating expenses Selling expenses 114941 G&A exp. 39000 153941 Operating Income 177071 Less: Interest exp. 5400 181 Int. on Long-term notcs Int. on short-term notes Income before tax Less: Income Tax at 35% Net Income 5581 171490 60022 111468 Budgeted Statement of Retained Earnings Beginning balance Add:Net income for the ytr. Less: Cash dividends Ending balance 138000 111468 100000 149468 50.000 202.584 145,860 398,444 535.000 105,000 430,000 828,444 Budgeted Balance Sheet Assets Current assets Cash Accounts Receivable Inventory Total Current assets Fixed assets: Equipment Less:Accumulated Depreciation Total Assets Liabilities & SIL Equity Current Liabilities Accounts Payable Income tax payable Short-term Notes Payable Total current liabilities Long-term Liabilities Long-term Notes Payable Total Long-term Liabilities Total Liabilities Sh. Equity Common Stock Retained Earnings Total Shareholder's Equity Liabilities & Shareholder's Equity 180.590 60,021 3.366 243,977 200.000 200000 443,977 235.000 149.468 384,468 828,445 Year Project 1 Annual Cash Inflows/(cash outflows) Present Value Factor at 6% -80000 1.0000 18000 0.9131 36000 0.8900 22000 0. 8396 Net Present Value Present Value of Annual Cash Flows -8000C 15283.02 32039.87 1 8471.62 15794.51 2 3 Year 1 2 Project 2 Anninal Cash Intlows (cash outflows) Present Value Factor at 8% 175000 85000 0. 92591 74000 38000 0.7938 26800 0.7350 19000 0. 6806 Net Present Value Present Value of Annual Cash Flons -175000.00 7 8703.70 63443.07 30165.63 19698.80 1 2931.08 29942.28 4 5 Year Project 3 Annual Cash Intlows (cash outflows) Present Value Factor at 12% -22700 13000 0.8929 12000 0.7972 Net Present Value 1 Present Value of Annual Cash Flows -22700 11607.14 10363.52 -729.34 TC ABC may not complete all thuee projects then they should prioritize as follows. Priority: 1. Project 2 since it has the highest NPV of $29,942.28 2. Project 1 follows since it has the next highest NPV of $15,794.51 3. Last, Project 3 should not be pursued since it gives a negative NPV of $729.34. The Project is: Assume ABC Company has asked you to not only prepare their 2017 year-end Balance Sheet but to also provide pro-forma financial statements for 2018. In addition, they have asked you to evaluate their company based on the pro-forma statements with regard to ratios. They also want you to evaluate 3 projects they are considering. Their information is as follows: End of the year information: Account 12/31/17 Ending Balance Cash 50,000 Accounts Receivable 175,000 Inventory 126,000 Equipment 480,000 Accumulated Depreciation 90,000 Accounts Payable 156.000 Short-term Notes Payable 12,000 Long-term Notes Payable 200,000 Common Stock 235.000 Retained Earnings solve Additional Information: Sales for December total 10,000 units. Each month's sales are expected to exceed the prior month's results by 5%. The product's selling price is $25 per unit. Company policy calls for a given month's ending inventory to equal 80% of the next month's expected unit sales. The December 31 2017 inventory is 8,400 units, which complies with the policy. The purchase price is $15 per unit Sales representatives commissions are 12.5% of sales and are paid in the month of the sales. The sales manager's monthly salary will be $3.500 in January and S4,000 per month thereatter. Monthly general and administrative expenses include 8,000 administrative salaries, $5,000 depreciation, and 0.9% monthly interest on the long-term note payable. The company expects 30% of sales to be for cash and the remaining 70% on credit Receivables are collected in full in the month following the sale (none is collected in the month of sale). All merchandise purchases are on credit, and no payables arise from any other transactions. One month's purchases are fully paid in the next month. The minimum ending cash balance for all months is $50,000. If necessary, the company borrows enough cash using a short-term note to reach the minimum. Short-term notes require an interest payment of 1% at each month-end (before any repayment). If the ending cash balance exceeds the minimum, the excess will be applied to repaying the short-term notes payable balance. Dividends of $100,000 are to be declared and paid in February. No cash payments for income taxes are to be made during the first calendar quarter. Income taxes will be assessed at 35% in the quarter. Equipment purchases of $55,000 arc scheduled for March. ABC Company's management is also considering 3 new projects consisting of the purchase of new equipment. The company has limited resources, and may not be able to complete make all 3 purchases. The information is as follows for the purchases below. Project 1 Project 2 Project 3 Purchase Price $80,000 $175,000 $22,700 Required Rate of Return 6% 8% 12% Time Period 3 years 5 years 2 years Cash Flows - Year 1 $48,000 $85,000 $13,000 Cash Flows - Year 2 $36,000 $74,000 $13,000 Cash Flows Ycar 3 $22,000 $38,000 NA Cash Flows - Year 4 $26,800 NA Cash Flows - Year 5 NA $19,000 NA Required Action: Part A: Prepare the year-end balance sheet for 2017. Be sure to use proper headings. Prepare budgets such that the pro-forma financial statements for the first quarter of 2018 may be prepared. Sales budget, including budgeted sales for April. Purchases budget, the budgeted cost of goods sold for each month and quarter, and the cost of the March 31 budgeted inventory. Selling expense budget. General and administrative expense budget. Expected cash receipts from customers and the expected March 31 balance of accounts receivable. Expected cash payments for purchases and the expected March 31 balance of accounts payable. Cash budget. Budgeted income statement. Budgeted statement of retained earnings. Budgeted balance sheet. Part B: Calculate using Excel formulas, the NPV of each of the 3 projects. . It is possible that ABC Company may not be able to complete all 3 projects. Therefore, advise ABC Company as to the order in which they should pursue the projects (i.e., which project should ABC Company attempt to do first, second, and last). Provide justification and analysis as to why you chose the order you did. The analysis must also be done in Excel, not in a separate document. This problem must be an Excel document, Does this work answer all the requirements below it? Balance Sheet Year End 31 Dec, 2017 ASSETS Current Assets Cash Accounts Receivable Inventory Total Current Assets Property plant and equipment Equipment Accumulated Deprciation TOTAL ASSETS 50000 175000 126000 351000 480000 -90000 390000 741000 156000 12000 168000 LIABILITY AND OWNERS EQUITY Current Liabilities Accounts Payablc Short-term Notes Payable Total Current Libabilities Long term liabilities Long-term Notes Payable Owners Equity Common Stock Retained Earnings TOTAL LIABILITY AND OWNERS EQUITY 200000 235000 138000373000 741000 Expected Unit Sales Each Month Jan Feb 10500 11025 Mar 11576 Apr 12155 Expected Cnit Sales Expected Cnit Sales Unit Selling Price Total Sales Sales Budget Jan 10500 25 262500 Feb 11025 25 275625 Mar 11576 25 289406 Otr Apr 33101 12155 25 25 827531 303877 11576 33101 12155 Purchases budget Budgeted Sales in units 10500 11025 Add:Ending inventory reqd.(80% next mth sales) 88209261 Total units needed 19320 20286 Less: Beginning inventory 8400 8820 Budgeted Purchases( units) 10920 11466 Purchase price/unit 15 Budgeted purchases (S) 163800 171987 9724 21300 9261 12039 15 180585 9724 42825 8400 34425 15 516375 15 Budgeted Sales in units COGS at S 15/unit COGS Budget 10500 157500 11025 11576 165375 173643.75 33101 496515 Cost of Mar 31, ending inventory 145860 Selling expense Budget Budgeted sales | 262500 275625 Sales Rep. commn at 12.5%* Sales $ 32813 34453 Sales mgr. salary 3500 4000 Total sell.exp. Budgeted 36313 38453 289406 36176 4000 40176 827531 103441 11500 114941 General and Administrative Expense Budget Administrative salaries 8000 8000 Interest exp.(0.9%*200000) 1800 1800 Total cash G&A exp. 9800 9800 Depreciation 5000 5000 Total G&A exp. 14800 14800 Without interest exp.(for IS) 8000 1800 9800 5000 14800 24000 5400 29400 15000 44400 39000 Budgeted sales $ Cash sales (30%) Credit sales of: Expected Cash Receipts From Customers 262500 275625 7875082688 289406 86822 827531 248259 175000 17-Dec 18-Jan 18-Feb 183750 175000 183750 192938 799947 192938 279760 Total sales collections expected 253750 266438 Exp. March 31 balance of accounts receivable 202584 Expected Cash Payments 163800 171990 180590 516380 Budgeted purchases (S) Payment for purchases of: 156000 17-Dec 18-Jan 18-Feb 163800 156000 163800 171990 491790 171990 171990 Total Payment for Purchases 156000 163800 Exp. March 31 balance of accounts payable. 180590 Cash Budget 50000 89518 253750 266438 303750355955 50000 279759 329759 50000 799947 849947 156000 36313 9800 Beginning balance Total sales collections exp. Total cash available Less: Disbursements: Total pmt. For purchases Total sell.exp. Budgeted Total cash G&A exp. Dividends Equipment purchase Total disbursements Surplus (Delicit) Add: Borrowings Less: Repayments Less: Interest Ending balance 163800 38453 9800 100000 171990 40176 9800 55000 276966 52793 202113312053 101638 43902 6098 12000 120 89518 50000 491790 114941 29400 100000 55000 791131 58815 6098 14732 181 50000 2732 61 50000 Budgeted Income Statement Sales revenue 827531 Less: COGS 496519 Gross profit 331012 Less: Operating expenses Selling expenses 114941 G&A exp. 39000 153941 Operating Income 177071 Less: Interest exp. 5400 181 Int. on Long-term notcs Int. on short-term notes Income before tax Less: Income Tax at 35% Net Income 5581 171490 60022 111468 Budgeted Statement of Retained Earnings Beginning balance Add:Net income for the ytr. Less: Cash dividends Ending balance 138000 111468 100000 149468 50.000 202.584 145,860 398,444 535.000 105,000 430,000 828,444 Budgeted Balance Sheet Assets Current assets Cash Accounts Receivable Inventory Total Current assets Fixed assets: Equipment Less:Accumulated Depreciation Total Assets Liabilities & SIL Equity Current Liabilities Accounts Payable Income tax payable Short-term Notes Payable Total current liabilities Long-term Liabilities Long-term Notes Payable Total Long-term Liabilities Total Liabilities Sh. Equity Common Stock Retained Earnings Total Shareholder's Equity Liabilities & Shareholder's Equity 180.590 60,021 3.366 243,977 200.000 200000 443,977 235.000 149.468 384,468 828,445 Year Project 1 Annual Cash Inflows/(cash outflows) Present Value Factor at 6% -80000 1.0000 18000 0.9131 36000 0.8900 22000 0. 8396 Net Present Value Present Value of Annual Cash Flows -8000C 15283.02 32039.87 1 8471.62 15794.51 2 3 Year 1 2 Project 2 Anninal Cash Intlows (cash outflows) Present Value Factor at 8% 175000 85000 0. 92591 74000 38000 0.7938 26800 0.7350 19000 0. 6806 Net Present Value Present Value of Annual Cash Flons -175000.00 7 8703.70 63443.07 30165.63 19698.80 1 2931.08 29942.28 4 5 Year Project 3 Annual Cash Intlows (cash outflows) Present Value Factor at 12% -22700 13000 0.8929 12000 0.7972 Net Present Value 1 Present Value of Annual Cash Flows -22700 11607.14 10363.52 -729.34 TC ABC may not complete all thuee projects then they should prioritize as follows. Priority: 1. Project 2 since it has the highest NPV of $29,942.28 2. Project 1 follows since it has the next highest NPV of $15,794.51 3. Last, Project 3 should not be pursued since it gives a negative NPV of $729.34. The Project is: Assume ABC Company has asked you to not only prepare their 2017 year-end Balance Sheet but to also provide pro-forma financial statements for 2018. In addition, they have asked you to evaluate their company based on the pro-forma statements with regard to ratios. They also want you to evaluate 3 projects they are considering. Their information is as follows: End of the year information: Account 12/31/17 Ending Balance Cash 50,000 Accounts Receivable 175,000 Inventory 126,000 Equipment 480,000 Accumulated Depreciation 90,000 Accounts Payable 156.000 Short-term Notes Payable 12,000 Long-term Notes Payable 200,000 Common Stock 235.000 Retained Earnings solve Additional Information: Sales for December total 10,000 units. Each month's sales are expected to exceed the prior month's results by 5%. The product's selling price is $25 per unit. Company policy calls for a given month's ending inventory to equal 80% of the next month's expected unit sales. The December 31 2017 inventory is 8,400 units, which complies with the policy. The purchase price is $15 per unit Sales representatives commissions are 12.5% of sales and are paid in the month of the sales. The sales manager's monthly salary will be $3.500 in January and S4,000 per month thereatter. Monthly general and administrative expenses include 8,000 administrative salaries, $5,000 depreciation, and 0.9% monthly interest on the long-term note payable. The company expects 30% of sales to be for cash and the remaining 70% on credit Receivables are collected in full in the month following the sale (none is collected in the month of sale). All merchandise purchases are on credit, and no payables arise from any other transactions. One month's purchases are fully paid in the next month. The minimum ending cash balance for all months is $50,000. If necessary, the company borrows enough cash using a short-term note to reach the minimum. Short-term notes require an interest payment of 1% at each month-end (before any repayment). If the ending cash balance exceeds the minimum, the excess will be applied to repaying the short-term notes payable balance. Dividends of $100,000 are to be declared and paid in February. No cash payments for income taxes are to be made during the first calendar quarter. Income taxes will be assessed at 35% in the quarter. Equipment purchases of $55,000 arc scheduled for March. ABC Company's management is also considering 3 new projects consisting of the purchase of new equipment. The company has limited resources, and may not be able to complete make all 3 purchases. The information is as follows for the purchases below. Project 1 Project 2 Project 3 Purchase Price $80,000 $175,000 $22,700 Required Rate of Return 6% 8% 12% Time Period 3 years 5 years 2 years Cash Flows - Year 1 $48,000 $85,000 $13,000 Cash Flows - Year 2 $36,000 $74,000 $13,000 Cash Flows Ycar 3 $22,000 $38,000 NA Cash Flows - Year 4 $26,800 NA Cash Flows - Year 5 NA $19,000 NA Required Action: Part A: Prepare the year-end balance sheet for 2017. Be sure to use proper headings. Prepare budgets such that the pro-forma financial statements for the first quarter of 2018 may be prepared. Sales budget, including budgeted sales for April. Purchases budget, the budgeted cost of goods sold for each month and quarter, and the cost of the March 31 budgeted inventory. Selling expense budget. General and administrative expense budget. Expected cash receipts from customers and the expected March 31 balance of accounts receivable. Expected cash payments for purchases and the expected March 31 balance of accounts payable. Cash budget. Budgeted income statement. Budgeted statement of retained earnings. Budgeted balance sheet. Part B: Calculate using Excel formulas, the NPV of each of the 3 projects. . It is possible that ABC Company may not be able to complete all 3 projects. Therefore, advise ABC Company as to the order in which they should pursue the projects (i.e., which project should ABC Company attempt to do first, second, and last). Provide justification and analysis as to why you chose the order you did. The analysis must also be done in Excel, not in a separate document. This problem must be an Excel document