EXCEL HOMEWORK ACCT2102 SPRING 2019 DUE Thursday June 13, 11:59PM. PLACE your Excel in the DropBox in FOLIO. Cartwright Inc produces wiring harnesses used in semi-trailers. The wiring harnesses sales for the next 4 months is projected to be: January 15,000 February 17,000 March 22,000 April 24,000 May 26,000 The following information pertains to the budget assumptions. a. Finished goods inventory on January 1 is expected to be 5000 units. The desired ending FGI for any month (except for the Dec 31 inventory as noted previously) is expected to be 20% of the following months sales. b. Data for materials used are: Part A87 4 parts per unit $26.50 per part Part D33 3 parts per unit $10.00 per part Raw materials inventory is always budgeted to equal 25% of the following months production needs. On March 31, the ending inventory of A87 is expected to be 18,000 parts and for D33 is expected to be 12,000 parts. c. Direct labor is made up of two types: Leather work is done by a Leather worker who is paid $20 per hour. The leather worker spends 1.75 hours on each harness. Assembly work is done by an Assembler who is $15 per hour. The Assembler spends 0.75 hour on each harness. d. Overhead each month is estimated at: The cost driver is DLH, which consists of Leather worker DLH plus Assembler DLH. e. Selling and Admin is estimated each to be: f. Selling price per harness is $330.00. g. Sales are all on account. 25% of sales are collected in the month of sale and 75% in the month following the sale. Accounts receivable on January 1 is $150,000. h. February is a planned purchase of land for $237,500. i. Borrowings are at 6% per year interest, and borrowings are assumed to be at the beginning of the month required and at the end of the month of repayment. Beginning cash is $20,000. Interest on borrowings is paid before any principal can be paid. Principal and any accumulated interest are assumed to be paid at the end of any month when excess funds are available. Minimum cash balance at the end of any month is $10,000. REQUIRED: Please prepare the following budgets in an Excel spreadsheet for each month of the first quarter and for the quarter in total. The first six budgets are to be on one tab, separate from the original data, and the two last budgets (Cash and Income Statement) are to be on a third, separate, tab. 1. Sales budget 2. Production budget 3. Direct materials purchases budget 4. Direct labor budget 5. Overhead budget 6. Selling and Admin budget 7. Cash Budget 8. Income Statement All budgets should be formulae and cell references: there should be no Values directly keyed into any of the number/quantitative cells in second and third tabs. Good format for the budgets should be followed and will be graded. As well, making the budgets easy for the reader will also count toward the grade. DUE Thursday June 13, 11:59PM. PLACE your Excel in the DropBox in FOLIO.