Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Cartwright Inc produces wiring harnesses used in antique sports cars. The wiring harnesses sales for the next 5 months is projected to be: January 15,500

Cartwright Inc produces wiring harnesses used in antique sports cars. The wiring harnesses sales for the next 5 months is projected to be: January 15,500 February 17,500 March 20,500 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 15% of the following months sales. b. Data for materials used are: Part A87 4 parts per unit $5.50 per part Part D33 3 parts per unit $4.50 per part Raw materials ending inventory is always budgeted to equal 20% of the following months production needs. On March 31, the ending inventory of A87 is expected to be 40,000 parts and for D33 is expected to be 30,000 parts. c. Payments for Raw materials purchases are on the following schedules: 25% paid in the month of purchase and 75% in the month following purchase. Accounts payable as of January 1 is $30,000.00. d. Direct labor used per harness is 1.5 hours at a rate of $15/hr. e. Overhead each month is estimated at: f. Selling and Admin is estimated each to be: g. Selling price per harness is $110.00. h. Sales are all on account. 33% of sales are collected in the month of sale and 67% in the month following the sale. Accounts receivable on January 1 is $40,000. i. February is a planned purchase of equipment for $237,500. j. 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. January 1 beginning cash is $20,000. Interest on borrowings is paid before any principal can be paid. To the extent possible, the company strives to repay any debt at the end of each month if there is cash 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 five (light green highlighted) budgets are to be on one tab, separate from the original data, and the two (light blue highlighted) budgets are to be on a third, separate, tab. All budgets should be formulae and cell references: there should be no Values directly keyed in. Good financial formatting for the budgets should be followed and will be graded. As well, making the budgets easy for the reader to read will also count toward the grade. DUE Friday, November 22, 6.30PM. PLACE YOUR SPREADSHEET SOLUTION IN THE Cartwright Excel Project DROPBOX IN FOLIO. 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

image text in transcribed
Editing Overhead each month is estimated at: e. Overhead estimates Fixed portion (per month) variable cost (S per DLH (cost dri Supplies 1 0.2 Power Maintenance 12500 11 Supervision 14000 Depreciation 45000 Taxes 4300 Other 1.6 f. Selling and Admin is estimated each to be: Selling and admin per momth variable cost (S per unit sold (cost driver) Fixed portion (per month) 30000 Salaries Commissions 14 Depreciation S000 2.6 Shipping Other 13000 0.6 g. Selling price per harness is $110.00. h. Sales are all on account. 33% of sales are collected in the month of sale and 67% in the month following the sale. Accounts receivable on January 1 is $40,000. Editing Overhead each month is estimated at: e. Overhead estimates Fixed portion (per month) variable cost (S per DLH (cost dri Supplies 1 0.2 Power Maintenance 12500 11 Supervision 14000 Depreciation 45000 Taxes 4300 Other 1.6 f. Selling and Admin is estimated each to be: Selling and admin per momth variable cost (S per unit sold (cost driver) Fixed portion (per month) 30000 Salaries Commissions 14 Depreciation S000 2.6 Shipping Other 13000 0.6 g. Selling price per harness is $110.00. h. Sales are all on account. 33% of sales are collected in the month of sale and 67% in the month following the sale. Accounts receivable on January 1 is $40,000

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Risk Based Tax Audits Approaches And Country Experiences

Authors: Munawer Sultan Khwaja, Rajul Awasthi, Jan Loeprick

1st Edition

0821387545, 978-0821387542

More Books

Students also viewed these Accounting questions