i need help completing the attached assignment. all of the questions are on the attached sheet.

FIN 340 Spreadsheet Assignment - Jenny's Antique Palace Assignment: 1. Jenny wants to open her own specialized antique restoration shop. In order to get approved for a loan to finance her capital investment, she must provide projected financial data to the bank. Given the information below, prepare the required documents Jenny needs for submission to the bank. To consider her loan request, the bank requires (so you are required to complete): a. Three years of monthly cash budgets b. Three year-end income statements c. Three year-end balance sheets 2. You should use spreadsheet modeling for this assignment. All cells in the cash budgets and pro formas should be formulas based on input/worksheet information. Modeling is worth 10% of your total grade on this assignment. 3. You may work in groups of two or less. Please e-mail me one spreadsheet for your group. You must submit an Excel spreadsheet (not a Google doc). 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 beginning of class on Monday, November 14th. Please do not turn in a hard copy. Information: 1. The data below depicts the anticipated sales for the first year. Jenny estimates that sales occur equally during each month of the quarter. Quarterly sales are expected to grow at 25% per year over the next three years. Sales Forecast 1st Quarter 2nd Quarter 3rd Quarter 4th Quarter Yearly Totals Year 1 $21,000 $30,000 $33,000 $36,000 $120,000 2. Jenny will accept credit card payments and the credit card company (i.e. Visa, MC) charges fees on every purchase with a debit or credit card. These fees are 1.5% of all related sales. On average, Jenny'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 Jenny's checking account. The credit card company deposits the customer payments in Jenny's account the same month the sales are made. 3. Of the customers that do not pay with a credit card, 10% will pay cash at the time of sale and the other 90% become A/R sales and will be collected in the month following the sale. 4. 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 as incurred. Year Annual Salaries 1 $45,000 2 $50,000 3 $55,000 5. The fixed monthly expenses are as follows (paid as incurred): Rent: $1,800.00 Phone & Utilities: $250 Insurance: $200.00 Office Supplies: .5% of sales Repairs and Maintenance: $65.00 Advertising: $250.00 Professional Services: $100.00 Other Required Information: 1. Jenny estimates that she will need $30,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 7% and is to be re-paid in monthly installments over the next five years beginning in January of Year 1. The $30,000 will be used as follows: a. Purchase an initial inventory balance of $10,000. b. Purchase $17,000 of long-term assets to open the shop. All of her 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. Jenny will make a $2,000 equity investment (in cash) to get the shop up and running. This is her initial retained earnings. 3. A deposit of $1,000 on January 1, Year 1 is required to start services with the utility company. The $1,000 will be returned to the company at the end of year 2. 4. 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. Jenny will start the business with her initial inventory (purchased with loan proceeds), and make her first inventory purchase in January of year 1 based on February's sales projections. Assume an average CGS of 25% of sales. 5. Jenny has chosen to run her restoration shop as a sole proprietorship, so you can ignore taxes