Exhibit 3A-1 contains a Microsoft Excel spreadsheet that includes the beginning balances shown in the balance sheet above. Notice that column "J" of the spreadsheet contains "=" signs (see cells 35 and J6). This means that after we record each of the forthcoming transactions, the amounts on the left-hand side of column "J" will always need to equal the amounts on the right-hand side of column "J." EXHIBIT 3A-1 Sapphire Company: Transaction Analysis A B D E G H 1 J L 1 Sapphire Company 2 Transaction Analysis 3 For the Month Ended January 31 Row Work in Finished Manufacturing Prepaid Accounts Retained 5 Transactions Cash Materials Process Goods Overhead Expenses PP&E (net) - Payable Earnings Beginning balances @ 1/1 $ 15,000 $ 8,000 $ 5,000 $ 13,000 $ $ 3,000 $240,000 - $ 4,000 $280,000 7 HH Exhibit 24-1_Ehite 34-2 Bab 32 Puhbat 344 Bet3 Bebt 3-5 Also, notice that the spreadsheet contains all of the accounts shown in the January 1 balance sheet plus an account called Manufacturing Overhead. As discussed earlier in the chapter, Manufacturing Overhead is a clearing account that always has a beginning and ending balance of zero. This account is used to record two thingsall actual overhead costs and the amount of manufacturing overhead applied to production using the predetermined overhead rate. The difference between the actual overhead cost and the amount of overhead applied to production is the underapplied or overapplied overhead. Finally, to conserve space, the Excel spreadsheet abbreviates Property, Plant, and Equipment (net) as PP&E (net). The term net implies that the acquisition cost of property, plant, and equipment is being reported net of accumulated depreciation. Sapphire Company-Transaction Analysis The remainder of the appendix proceeds in three steps. First, it lists Sapphire Company's transactions for the month of January. Second, it explains how each of these transactions is recorded in the Microsoft Excel spreadsheet. Finally, it explains how to use the information depicted in the spreadsheet to prepare a schedule of cost of goods manufactured, a schedule of cost of goods sold, and an income statement for the month of January Page 137 To begin our illustration, let's assume that Sapphire Company has a predetermined overhead rate of $25 per direct labor-hour that was based on a cost formula that estimated $100,000 in manufacturing overhead cost for an estimated allocation base of 4,000 direct labor-hours. During January, the company completed the following transactions: Sapphire Company-Transaction Analysis The remainder of the appendix proceeds in three steps. First, it lists Sapphire Company's transactions for the month of January. Second, it explains how each of these transactions is recorded in the Microsoft Excel spreadsheet. Finally, it explains how to use the information depicted in the spreadsheet to prepare a schedule of cost of goods manufactured, a schedule of cost of goods sold, and an income statement for the month of January Page 137 To begin our illustration, let's assume that Sapphire Company has a predetermined overhead rate of $25 per direct labor-hour that was based on a cost formula that estimated $100,000 in manufacturing overhead cost for an estimated allocation base of 4,000 direct labor-hours. During January, the company completed the following transactions: a. Purchased raw materials on account, S80,000. b. Raw materials used in production, $78,000 $70,000 was direct materials and $8,000 was indirect materials). c. Paid S135,000 of salaries and wages in cash (S68,000 was direct labor, $45,000 was indirect labor, and $22,000 was related to employees responsible for selling and administration). d. Utility costs incurred (on account) to support production, $15,000. e. Depreciation recorded on property, plant, and equipment, S40,000 (70% related to manufacturing equipment and 30% related assets that support selling and administration), f. Advertising expenses paid in cash, $18,000. g. Prepaid insurance expired during the month, $1,000 (80% related to production, and 20% related to selling and administration). h. Manufacturing overhead applied to production, $102.500. This amount was computed by multiplying 4,100 direct labor-hours worked in January by the predetermined overhead rate of $25 per direct labor-hour. i. Cost of goods manufactured. S235.000. j. Cash sales, k. Cost of goods sold, $245,000. 1. Cash payments to creditors, $92.000. m. Close overapplied overhead of $5,700 to cost of goods sold