BMAL 530 EXCEL PROJECT ASSIGNMENT
Assume ABC Firm has requested you to not solely put together their 2013 year-end Steadiness Sheet butto additionally present pro-forma monetary statements for 2014. As well as, they've requested you toevaluate their firm primarily based on the pro-forma statements with regard to ratios. In addition they wantyou to judge three initiatives they're contemplating. Their data is as follows:Finish of the yr data:AccountCashAccounts ReceivableInventoryEquipmentAccumulated DepreciationAccounts PayableShort-term Notes PayableLong-term Notes PayableCommon StockRetained Earnings12/31/13Ending Steadiness160,000126,00075,200745,000292,46036,90018,300157,225450,000solveAdditional Data:Gross sales for December complete 12,000 models. Every months gross sales are anticipated to exceed theprior months outcomes by 5%. The merchandise promoting value is $15 per unit.Firm coverage requires a given months ending stock to equal 80% of the nextmonths anticipated unit gross sales. The December 31 2012 stock is 9,400 models, whichcomplies with the coverage. The acquisition value is $eight per unit.Gross sales representatives commissions are 10.zero% of gross sales and are paid within the month of thesales. The gross sales managers month-to-month wage shall be $three,500 in January and $four,000 permonth thereafter.Month-to-month basic and administrative bills embody $eight,000 administrative salaries,$5,000 depreciation, and zero.9% month-to-month curiosity on the long-term observe payable.The corporate expects 30% of gross sales to be for money and the remaining 70% on credit score.Receivables are collected in full within the month following the sale (none is collected in themonth of sale).All merchandise purchases are on credit score, and no payables come up from any othertransactions. One months purchases are absolutely paid within the subsequent month.The minimal ending money steadiness for all months is $140,000. If obligatory, the companyborrows sufficient money utilizing a short-term observe to succeed in the minimal. Quick-term notesrequire an curiosity cost of 1% at every month-end (earlier than any compensation). If theending money steadiness exceeds the minimal, the surplus shall be utilized to repaying theshort-term notes payable steadiness.Dividends of $100,000 are to be declared and paid in February.No money funds for revenue taxes are to be made throughout the first calendar quarter.Revenue taxes shall be assessed at 35% within the quarter.Tools purchases of $55,000 are scheduled for March.ABC Companys administration can also be contemplating three new initiatives consisting of the purchaseof new gear. The corporate has restricted sources, and should not be capable of completemake all three purchases. The data is as follows for the purchases beneath.Venture 1Project 2Project 3Purchase Value$50,000$75,000$32,500Required Fee of Return12%eight%10%Time Period3 years5 years2 yearsCash Flows 12 months 1$18,000$25,000$20,000Money Flows 12 months 2$22,000$20,000$18,000Money Flows 12 months three$22,000$18,000N/ACash Flows 12 months 4N/A$16,500N/ACash Flows 12 months 5N/A$15,000N/APage 2 of 3BMAL 530Required Motion:Half A:Put together the year-end steadiness sheet for 2013. You should definitely use correct headings.Put together budgets such that the pro-forma monetary statements could also be ready.Gross sales funds, together with budgeted gross sales for April.Purchases funds, the budgeted price of products bought for every month and quarter, and thecost of the March 31 budgeted stock.Promoting expense funds.Basic and administrative expense funds.Anticipated money receipts from prospects and the anticipated March 31 steadiness of accountsreceivable.Anticipated money funds for purchases and the anticipated March 31 steadiness of accountspayable.Money funds.Budgeted revenue assertion.Budgeted assertion of retained earnings.Budgeted steadiness sheet.Half B:Calculate utilizing Excel formulation, the NPV of every of the three initiatives.It's attainable that ABC Firm might not be capable of full all three initiatives. Due to this fact,advise ABC Firm as to the order through which they need to pursue the initiatives (i.e.,which challenge ought to ABC Firm try and do first, second, and final).Present justification and evaluation as to why you selected the order you probably did. The analysismust even be performed in Excel, not in a separate doc.