Accounting 11 Name: Spreadsheet Assignment Portion of in-Class Writing Activity Date_ COMPANY: TIME TO PLAY, INC. You are a member of top management of Time To Play, Inc., a seller of model airplanes. The firm originally operated as a sole proprietorship, but recently changed to the corporate form in hopes of expanding operations by generating additional financing from the sale of common stock You and other top managers will be meeting next week with the firm's new Board of Directors to provide background information on the business and detail its profitability since its inception in 2017. You just got off the phone with the firm's comptroller (chief accountant), Randi Hunter, who was on her way to a hospital for treatment of her worsening pneumonia. Could you determine the profitability information for the Chief Executive Officer (CEO) Galen Carter, who will be making the presentation to the Board of Directors? Because you thoroughly enjoyed the accounting courses you took in college, you remember much about profitability determination and feel confident in your ability to help out Because of competition, the company has had to drop selling prices. The selling price per unit was $6.85 in 2017. $6.75 in 2018, and $6.70 in 2019. Then there is the issue of product" costs versus "period" expenses. There has not been much inflation the past few years, so product costs (as opposed to period expenses) have been stable. Searching through files in Randi's office, you find that since 2017. Time To Play, Inc. has had the following manufactured cost per unit for all three years Direct Materials Per Unit $1.10 Direct Labor Per Unit Overhead Applied Per Unit 2.35 In addition, the firm incurred selling and administrative costs that are considered period expenses. Other numerical data can be summarized as follows: Selling and Year Units in Beg Inven Units Manufactured Units Sold Units in End Inven Admin Expenses 2017 118,000 18,000 $ 52,000 2018 290,000 66,000 $106,800 2019 320,000 114,000 $127,600 15 ? Remember that although this new corporation will have to pay income taxes on the income it generates, the sole proprietorship it was (for those three years) did not (because sole proprietorship owners report the firm's income on their personal tax returns). Finally, you remind yourself that to calculate profit, you will have to consider the difference between "expired" and "unexpired" costs. REQUIRED: Prepare a spreadsheet with appropriate INPUT and OUTPUT sections that calculates (a) number of units sold each year (for 2017, 2018, and 2019). (b) prepares a formal multiple-step income statement for each year (be sure it also includes a detailed cost of goods sold section), and (c) calculate trend (or horizontal) percentages for growth in sales and in net income from the base year of 2017. Decause the firm was SO SUCcessful in die somer relationships with the youth segment of the model plane market, Time to play in h e aven an expansion project to also make and sen other types of toys starting this year 1202 Th a t olan is to make a limited production of one new loy each quarter. For example the first der of 2020 had the firm manufacturing a special edition toy truck, second quarter will be to m ore thind will be toy boats, and fourth will be toy cars Your friend in the Finance Departments Matthew secured a loan from a local bank to provide financing for this production. As part of the loan agreement, the firm was required to repon balances at the end of each month for its inventories The firm decided to use a job order costing system. The resulting job-order cost sheet for manufacturing 10.000 toy trucks is as follows: Job Number 124 Date Started January 4 Date Completed March 15 Direct Materials Direct Labor Date TYPE COST QTY AMOUNT COST HOURS Jan 4 X $3.00 each 10,000 $30,000 (Jan) $18.00 200 Feb 9 Y $1.00 each 40,000 $40,000 (Feb) $19.00 100 Mar 5 Z $2.00 each 10,000 $20,000 (Mar) $18.50 1,000 TOTAL $90,000 TOTAL Total direct materials $ 90,000 Total direct labor $ 24,000 Overhead applied (1300 direct labor hours @ $12 per hour) $ 15,600 TOTAL $129.500 AMOUNT $ 3,600 $ 1,900 $18,500 $24,000 All of the materials for the job were purchased on January 2. All 10.000 of the toy trucks were sold to a major retailer on April 10. Sam completely forgot about submitting the required figures to the bank and was contacted today by a loan officer concerning the matter. (Assume today is April 30.) Because Sam is so worried about losing his job from the oversight, he does not feel confident about calculating the information. He calls you because he knows he can count on you for help. You realize you must first consider whether these costs will appear on the firm's Balance Sheet or on its Income Statement for each month so far this year. REQUIRED: Prepare a spreadsheet with appropriate INPUT and OUTPUT sections that calculates the FOUR items of Direct Materials Inventory, Work-in-Process Inventory (with detail on Direct Material total, Direct Labor total, and Overhead Applied), Finished Goods Inventory (with detail on Direct Material total, Direct Labor total and Overhead Applied), and Cost of Goods Sold for EACH of the following dates: (a) January 31, (b) February 29, (c) March 31, and (d) April 30. IMPORTANT: In-Class Writing Activity Day will be on Bring both (a) your completed Excel spreadsheets AND (b) formula sheets fu your Excel spreadsheets and be ready to write about what the numbers me and some of the related concepts to explain to Board members or Bankers Accounting 11 Name: Spreadsheet Assignment Portion of in-Class Writing Activity Date_ COMPANY: TIME TO PLAY, INC. You are a member of top management of Time To Play, Inc., a seller of model airplanes. The firm originally operated as a sole proprietorship, but recently changed to the corporate form in hopes of expanding operations by generating additional financing from the sale of common stock You and other top managers will be meeting next week with the firm's new Board of Directors to provide background information on the business and detail its profitability since its inception in 2017. You just got off the phone with the firm's comptroller (chief accountant), Randi Hunter, who was on her way to a hospital for treatment of her worsening pneumonia. Could you determine the profitability information for the Chief Executive Officer (CEO) Galen Carter, who will be making the presentation to the Board of Directors? Because you thoroughly enjoyed the accounting courses you took in college, you remember much about profitability determination and feel confident in your ability to help out Because of competition, the company has had to drop selling prices. The selling price per unit was $6.85 in 2017. $6.75 in 2018, and $6.70 in 2019. Then there is the issue of product" costs versus "period" expenses. There has not been much inflation the past few years, so product costs (as opposed to period expenses) have been stable. Searching through files in Randi's office, you find that since 2017. Time To Play, Inc. has had the following manufactured cost per unit for all three years Direct Materials Per Unit $1.10 Direct Labor Per Unit Overhead Applied Per Unit 2.35 In addition, the firm incurred selling and administrative costs that are considered period expenses. Other numerical data can be summarized as follows: Selling and Year Units in Beg Inven Units Manufactured Units Sold Units in End Inven Admin Expenses 2017 118,000 18,000 $ 52,000 2018 290,000 66,000 $106,800 2019 320,000 114,000 $127,600 15 ? Remember that although this new corporation will have to pay income taxes on the income it generates, the sole proprietorship it was (for those three years) did not (because sole proprietorship owners report the firm's income on their personal tax returns). Finally, you remind yourself that to calculate profit, you will have to consider the difference between "expired" and "unexpired" costs. REQUIRED: Prepare a spreadsheet with appropriate INPUT and OUTPUT sections that calculates (a) number of units sold each year (for 2017, 2018, and 2019). (b) prepares a formal multiple-step income statement for each year (be sure it also includes a detailed cost of goods sold section), and (c) calculate trend (or horizontal) percentages for growth in sales and in net income from the base year of 2017. Decause the firm was SO SUCcessful in die somer relationships with the youth segment of the model plane market, Time to play in h e aven an expansion project to also make and sen other types of toys starting this year 1202 Th a t olan is to make a limited production of one new loy each quarter. For example the first der of 2020 had the firm manufacturing a special edition toy truck, second quarter will be to m ore thind will be toy boats, and fourth will be toy cars Your friend in the Finance Departments Matthew secured a loan from a local bank to provide financing for this production. As part of the loan agreement, the firm was required to repon balances at the end of each month for its inventories The firm decided to use a job order costing system. The resulting job-order cost sheet for manufacturing 10.000 toy trucks is as follows: Job Number 124 Date Started January 4 Date Completed March 15 Direct Materials Direct Labor Date TYPE COST QTY AMOUNT COST HOURS Jan 4 X $3.00 each 10,000 $30,000 (Jan) $18.00 200 Feb 9 Y $1.00 each 40,000 $40,000 (Feb) $19.00 100 Mar 5 Z $2.00 each 10,000 $20,000 (Mar) $18.50 1,000 TOTAL $90,000 TOTAL Total direct materials $ 90,000 Total direct labor $ 24,000 Overhead applied (1300 direct labor hours @ $12 per hour) $ 15,600 TOTAL $129.500 AMOUNT $ 3,600 $ 1,900 $18,500 $24,000 All of the materials for the job were purchased on January 2. All 10.000 of the toy trucks were sold to a major retailer on April 10. Sam completely forgot about submitting the required figures to the bank and was contacted today by a loan officer concerning the matter. (Assume today is April 30.) Because Sam is so worried about losing his job from the oversight, he does not feel confident about calculating the information. He calls you because he knows he can count on you for help. You realize you must first consider whether these costs will appear on the firm's Balance Sheet or on its Income Statement for each month so far this year. REQUIRED: Prepare a spreadsheet with appropriate INPUT and OUTPUT sections that calculates the FOUR items of Direct Materials Inventory, Work-in-Process Inventory (with detail on Direct Material total, Direct Labor total, and Overhead Applied), Finished Goods Inventory (with detail on Direct Material total, Direct Labor total and Overhead Applied), and Cost of Goods Sold for EACH of the following dates: (a) January 31, (b) February 29, (c) March 31, and (d) April 30. IMPORTANT: In-Class Writing Activity Day will be on Bring both (a) your completed Excel spreadsheets AND (b) formula sheets fu your Excel spreadsheets and be ready to write about what the numbers me and some of the related concepts to explain to Board members or Bankers