Can you please review the attached Excel project for accuracy and make any required changes. Attached is the Excel project instructions and the ending balance inventory should be $126,000 and not $126,00.

Part A: Prepare the year-end balance sheet for 2013 with proper headings Balancesheet As on 31st Dec 2015 Details $ Amount $ 50,000 175,000 126,000 351,000 480,000 (90,000) 390,000 741,000 Assets Cash Accounts Receivable Inventory Total Current Assets Equipment Less accumulated depreciation Total Assets Liabilities & Owner's Equity Accounts Payable Short term Notes Payable Total current liabilities Long term Notes payable Total Liabilites Common Stock Retained earnings Total Liabilities & shareholder's Equity 156,000 12,000 168,000 200,000 368,000 235,000 138,000 741,000 Prepare budgets such that the pro-forma financial statements may be prepared Sales budget, including budgeted sales for April - Sales budget Particulars January Budgeted Unit Sales Budgeted Unit price Budgeted Total Dollars 10,500 25 262,500 February 11,025 ### 275,625 March 11,576 25 289,406 Total 33,101 ### 827,531 April 12,155 25 303,877 Purchases budget, the budgeted cost of goods sold for each month and quarter, and the cost of the March 31 budgeted inventor - Production budget Particulars January Budgeted Unit Sales Add Desired Ending inventory Total Etimated Units Required Less finished Goods Beginning Inventory Units to be produced 10,500 8,820 19,320 (8,400) 10,920 February 11,025 9,261 20,286 (8,820) 11,466 March 11,576 26,481 38,057 (9,261) 28,796 Total 33,101 26,481 59,582 (8,400) 51,182 Direct materials purchase budgets Particulars January Units to be produced Cost Per Unit Cost of Material to be purchased 10,920 15 163,800 February 11,466 ### 171,990 March 28,796 15 431,944 Total 51,182 ### 767,734 Cost of goods sold budget For the Year ended 31st December Particulars January February March Total Direct materials used Direct materials inventory, beginning Purchases 126,000 163,800 70,560 171,990 74,088 431,944 126,000 767,734 Cost of direct materials available for use Less Direct materials inventory,ending Cost of direct materials used Total manufacturing cost 289,800 (70,560) 219,240 219,240 242,550 (74,088) 168,462 168,462 506,032 (211,848) 294,184 294,184 893,734 (211,848) 681,886 681,886 April 12,155 Selling expense budget. Particulars January Units to be Sold Variable Selling expenses Comission @ 12.5% February March Total 262,500 275,625 289,406 827,531 32,813 34,453 36,176 103,441 32,813 34,453 36,176 103,441 Fixed Selling expenses Sales manager salary Total fixed Expenses 3,500 3,500 4,000 4,000 4,000 4,000 11,500 11,500 Total Selling expenses 36,313 38,453 40,176 114,941 Total variable Selling expenses General and administrative expense budget. Particulars Interest @ .9 % on the long term notes paya Total variable adminstrative expenses ex January February March Total 1,800 1,800 1,800 1,800 1,800 1,800 5,400 5,400 Adminstrative salaries Depreciation Total fixed Expenses 8,000 5,000 13,000 ### ### 13,000 8,000 5,000 13,000 24,000 15,000 39,000 Total adminstrative Expenses less Depreciation Cash Paid 14,800 (5,000) 9,800 14,800 (5,000) 9,800 14,800 (5,000) 9,800 44,400 (15,000) 29,400 Expected cash receipts from customers and the expected March 31 balance of accounts receivable. Calculation of cash receipt from customers: Particulars Total budgeted sales Cash sales Sales on credit January 262,500 78,750 183,750 February 275,625 82,688 192,938 March 289,406 86,822 202,584 Total 827,531 248,259 579,272 Total cash receipt from customers Particulars January February March Total Current month's cash sale Collections of receivables 78,750 175,000 82,688 183,750 86,822 192,938 248,259 551,688 Total cash receipt from customers 253,750 266,438 279,759 799,947 Expected cash payments for purchases and the expected March 31 balance of accounts payable. Particulars Purchases of Material Paid in the following month Total payments for purchases January 163,800 156,000 156,000 February 171,990 163,800 163,800 March 431,944 171,990 171,990 Total 767,734 491,790 491,790 January February March Total Cash budget. - Cash budget Particulars Opening Balance of Cash 160,000 193,155 147,539 160,000 Add Cash received from Customers Total Cash Available Less Disbursements Payment for Purchases Payment for selling expenses Payment for Adminstrative expenses Purchase of Equipment Payment of Dividend Total Payments 253,750 413,750 279,759 427,298 799,947 959,947 156,000 36,313 9,800 163,800 38,453 9,800 171,990 40,176 9,800 55,000 202,113 100,000 312,053 276,966 491,790 114,941 29,400 55,000 100,000 791,131 211,638 Excess (Deficiency) Additional financing Repayment of loan Interest payment Closing balance of cash 266,438 459,592 147,539 150,332 168,815 150,332 18,300 183 150,332 18,300 183 193,155 147,539 Budgeted income statement. Particulars Details $ Sales less Cost of Goods Sold Finished goods beginning Inventory Cost of goods Manufactured Cost of Finished goods Available for sale Less Finished goods ending Inventory Cost of goods Sold Gross Profit Less Selling & adminstration expenses Net Income from operations Less tax Rate @35% Net Income after tax Quarter 827,531 126,000 767,734 893,734 (211,848) 681,886 145,646 (159,341) (13,696) 4,794 (8,902) Budgeted statement of retained earnings. Particulars Opening balnce of Retained earnings Add Net Income Less Dividend Closing balance of Retained earnings Details $ 138,000 (8,902) (100,000) Quarter 29,098 Budgeted balance sheet. Assets Cash Accounts Receivable Inventory Total Current Assets Equipment Less accumulated depreciation Total Assets Balancesheet As on 31st March.,2014 Details $ Amount $ 150,332 202,584 211,848 564,765 800,000 (307,460) 492,540 1,057,305 Liabilities & Owner's Equity Accounts Payable Short term Notes Payable Total current liabilities Long term Notes payable Total Liabilites Common Stock Retained earnings Total Liabilities & shareholder's Equity 431,944 431,944 157,225 589,169 450,000 29,098 1,057,305 Part B: Calculate using Excel formulas, the NPV of each of the 3 projects Year 0 1 Project 1 -80000 48,000 P.V @ 6% 1 0.9434 Total NPV (80,000) 45,283 2 3 4 5 36,000 22,000 - 0.8900 0.8396 0.7921 0.7473 0 1 2 3 4 5 Project 2 -175000 85,000 74,000 38,000 26,800 19,000 0 1 2 3 4 5 Project 3 P.V @ 10% -22700 1 15,000 0.8929 12,000 0.7972 0.7118 0.6355 0.5674 Total NPV Year P.V @ 8% 1 0.9259 0.8573 0.7938 0.7350 0.6806 Total NPV Year Total NPV 32,040 18,471 15,794 Total NPV (175,000) 78,704 63,443 30,166 19,699 12,931 29,942 Total NPV (22,700) 13,394 9,566 260 2. 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 firs ABC company should start first with the project 2 , then with project 1 and lastly with project 3. The following is the rank Project 2 Project 1 Project 3 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 docu I have chosen this order as the NPV of project 2 is the highest which exceeds project 1 and 3. However, project 3 has the lowest NPV of the inventory to do first, second, and last). ate document. PV of the three projects BMAL 530 EXCEL PROJECT INSTRUCTIONS Assume ABC Company has asked you to not only prepare their 2015 year-end Balance Sheet but to also provide pro-forma financial statements for 2016. 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 Cash Accounts Receivable Inventory Equipment Accumulated Depreciation Accounts Payable Short-term Notes Payable Long-term Notes Payable Common Stock Retained Earnings 12/31/15 Ending Balance 50,000 175,000 126,00 480,000 90,000 156,000 12,000 200,000 235,000 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 2015 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 $4,000 per month thereafter. 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. Page 1 of 3 BMAL 530 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 are 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 $15,000 Cash Flows - Year 2 $36,000 $74,000 $12,000 Cash Flows - Year 3 $22,000 $38,000 N/A Cash Flows - Year 4 N/A $26,800 N/A Cash Flows - Year 5 N/A $19,000 N/A Page 2 of 3 BMAL 530 Required Action: Part A: Prepare the year-end balance sheet for 2015. Be sure to use proper headings. Prepare budgets such that the pro-forma financial statements for the first quarter of 2016 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 assignment must be submitted as 1 Excel document. This assignment is due by 11:59 p.m. (ET) on Friday of Module/Week 8. Page 3 of 3