accountin spreadsheet project 494918

EXCEL SPREADSHEET ASSIGNMENT Assignment Due Date is August 12th Attached are the December 31, 2012 Balance Sheet and Income Statement for ABC Company. Use this information as your basis for projections. You are to create an Excel spreadsheet that projects the balance sheet and income statement on a monthly basis for six consecutive months. The following assumptions are to be used on the assignment: Sales increase 5% per period Selling expenses are 10% of sales Administrative expenses are 15% of sales Tax rate is 34% Half the sales are for cash. The other half are collected 70% in month of sale and the balance in the month following the month of sale. All merchandise purchases are on account. 70% are paid in the month of sale and 30% in the month following the month of purchase. Merchandise purchased each month is 70% of that month’s sales. Ending inventory is 25% of the following month’s sales. On February 1, the company purchased a building for $750,000 and financed the entire amount over twenty years with monthly payments beginning on March 1. You must prepare an amortization schedule to amortize the loan, and link the payments to the balance sheet and income statement. Use an interest rate of 6 percent. The building has a 40-year useful life. Use straight line depreciation. The equipment has a remaining useful life of 120 months. Use straight line depreciation. Selling expenses are paid 80% in the month incurred and 20% in the following month. Administrative expenses are paid 90% in the month incurred and 10% in the following month. Prepare projected monthly income statements through the month of June 2012 and prepare a projected balance sheet at the end of each month. Note: The spreadsheet must be prepared for “what if” situations. That is, if any variable changes, the cell formulas must take that change into consideration and flow it through the statements. For example, if projected sales change, the spreadsheet must make…