I know how to do the cash budgets but not the income statements or the balance sheets on this assignment.

Spreadsheet Assignment #2 - Junior's Restoration Palace Assignment: 1. Junior wants to open his own specialized car restoration shop. In order to get approved for a loan to finance his capital investment, he must provide realistic financial data to the bank. Given the information below, prepare the required documents Junior needs for submission to the bank. To consider his file, the bank requires: a. Three years of monthly cash budgets b. Three year-end income statements c. Three year-end balance sheets 2. You may work in groups of two or less. Please e-mail me one spreadsheet for your group. The spreadsheet should include both group members' last names at the top of the first sheet and in the beginning of the file name. This should be e-mailed to me by the start of class on Tuesday, April 26th. Information: 1. The data below depicts the anticipated sales for the first three years. Junior estimates that sales occur equally during each month of the quarter. Sales for Q1 of the 4th year are expected to be up 30% over the same period in year 3. Sales Forecast 1st Quarter 2nd Quarter 3rd Quarter 4th Quarter Yearly Totals Year 1 $42,000 $67,000 $87,000 $102,000 $298,000 Year 2 $54,000 $86,500 $112,500 $133,000 $386,000 Year 3 $65,600 $107,850 $141,650 $163,000 $478,100 2. The credit card company charges fees on every purchase with a debit or credit card. These fees are 2.5% of all related sales. On average, Junior's is expecting that 50% of their sales will be made using a debit or credit card. The fees are paid in the month incurred and are deducted from Junior's checking account. The credit card company deposits the customer payments in Junior's account the same month the sales are made. 3. Of the customers that do not pay with a credit card, 20% will pay cash at the time of sale and the other 80% become A/R sales and will be collected in the month following the sale. 4. The fixed monthly expenses are as follows (paid as incurred): Rent: $2,700.00 Phone & Utilities: $439.27 Insurance: $150.00 Office Supplies: 0.5% of sales Repairs and Maintenance: $60.00 Advertising: $400.00 Professional Services: $145.00 5. Total salaries and wages for the year are listed below. Salaries are distributed/paid monthly as incurred. In addition, employee related expenses are 15 percent of wages and salaries and are also paid monthly. Year Annual Salaries 1 $60,000 2 $75,000 3 $95,000 Other Required Information: 1. Junior estimates that he will need $70,000 from the bank for start-up expenses. Receipt of the loan and purchases will occur in December, before the start of the business. The current rate at the bank for investments of this type is 10% and is to be re-paid in monthly installments over the next five years beginning in January of Year 1. The $70,000 will be used as follows: a. Purchase an initial inventory balance of $40,000. b. Purchase $20,000 of long-term assets to open the shop. All of his assets will be depreciated on a straight-line basis over the first five years with no residual value. c. The remaining proceeds will be placed in cash. 2. Junior will make a $5,000 equity investment (in cash) to get the shop up and running. 3. The first and last month's rent & a security deposit of $600 must be paid in advance at the start of the lease in January, Year 1. The prepaid expenses (last month's rent and security deposit) will be carried on the books until Junior moves out of his current location. 4. A deposit on January 1, Year 1 is required to start services with the utility company. The $1,500 will be returned to the company at the end of year 2. 5. Inventory purchases are made in the month before sale to cover anticipated sales the next month. All purchases are paid for two months after purchased. Junior will start with his initial inventory, and make his first inventory purchase in January of year 1 based on February's sales projections. Assume an average CGS of 57% of sales. 6. Junior has chosen to run his restoration shop as a sole proprietorship, so you can ignore taxes