The business manufactures custom patios made of concrete, brick,fiberglass, and lumber- depending upon customer preference. Thecompany's fiscal year is the calendar year. At the beginning ofJuly, selected balances were as follows:
| Direct Materials Inventory, July 1 | $4200 |
| Work-In-Process Inventory, July 1 | 5540* |
| Manufacturing Overhead Applied to Date | 32640 |
| Actual Manufacturing Overhead to Date | 31650 |
*Details for Work in Process
| Job 85 | | Job 86 | | Job 87 | | Total |
| Direct Materials | $600 | | 800 | | 900 | | |
| Direct Labor | 320 | | 540 | | 580 | | |
| Manufacturing Overhead | 400 | | 675 | | 725 | | |
| = | 1320 | + | 2015 | + | 2205 | = | 5540 |
(the last row are the separate columns added together and thentotaled at the end).
During July, total direct materials purchased were $4900.Overhead costs incurred were $3800. Direct materials and directlabor used were as follows:
| Materials Requested Amounts | Labor Time Ticket Amounts | | |
| Job 85 | $1100 | $840 | | |
| Job 86 | 500 | 360 | | |
| Job 87 | 1300 | 1200 | | |
| Job 88 | 2000 | 800 | | |
The Company uses conventional overhead application with overheadcharged to jobs at the rate of $1.25 per dollar of direct laborcost. The patios for Jobs 85 and 87 were completed during July andsold at cost plus a 30 percent markup.
Prepare an Excel Spreadsheet that determines the cost of eachjob at the end of July. Then program cells that determine end ofJuly balance of direct materials inventory, end ofJuly balance of work in process inventory, end ofJuly balance of finished goods,sales for July, cost of goodssold for July, and gross profit marginfor July. On the lower part of the spreadsheet: Prepare a formalcost of goods manufactured schedule for the month of July.
(the company only deals with underapplied or overappliedoverhead at the end of December therefore it is not needed inJuly).
