Posted: September 13th, 2017

Spreadsheet

Spreadsheet For ACC-202

Order Description

1. Sales increase 5% per period
2. Selling expenses are 10% of sales
3. Administrative expenses are 15% of sales
4. Tax rate is 34%
5. 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.
6. All merchandise purchases are on account. 70% are paid in the month of sale and 30% in the month following the month of purchase.
7. Merchandise purchased each month is 70% of that month’s sales.
8. Ending inventory is 25% of the following month’s sales.
9. 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.
10. The building has a 40-year useful life. Use straight line depreciation.
11. The equipment has a remaining useful life of 120 months. Use straight line depreciation.
12. Selling expenses are paid 80% in the month incurred and 20% in the following month.
13. Administrative expenses are paid 90% in the month incurred and 10% in the following month.
14. 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 appropriate adjustments for that change. The same applies to all variable expenses, depreciation, loan amortization, etc. Any change must flow through all projections.
In order to determine the correct cash balance for the balance sheet each month, you must determine the projected net cash inflows/outflows for each month (i.e., cash balance at the beginning of the month plus net cash inflow or minus net cash outflow equals the cash balance at the end of the month).