Bartleby Sitemap - Textbook Solutions
All Textbook Solutions for Excel Applications for Accounting Principles
On June 1 of the current year, Wilson Wood opened Woodys Web Services. This sole proprietorship had the following transactions during June. a. Opened a business checking account and made a deposit, 9,000. b. Paid rent for June for office space and computer equipment, 3,000. c. Purchased office supplies (stamps, pens, etc.) on account, 1,980. d. Received cash for services rendered, 4,500. e. Paid creditor for office supplies purchased on account, 1,400. f. Purchased office supplies for cash, 420. g. Billed clients for consultations performed on account, 4,600. h. Paid monthly internet service bill of 360. i. Paid the secretarys salary of 2,400. j. Returned 300 of office supplies purchased in transaction f. Received a full refund. k. Received cash from clients previously billed, 3,000. l. Cash was withdrawn for owners personal use, 2,000. REQUIREMENT 1. Review the printout of the worksheet PTRANS. You have been asked to complete the worksheet by recording these transactions.On June 1 of the current year, Wilson Wood opened Woodys Web Services. This sole proprietorship had the following transactions during June. a. Opened a business checking account and made a deposit, 9,000. b. Paid rent for June for office space and computer equipment, 3,000. c. Purchased office supplies (stamps, pens, etc.) on account, 1,980. d. Received cash for services rendered, 4,500. e. Paid creditor for office supplies purchased on account, 1,400. f. Purchased office supplies for cash, 420. g. Billed clients for consultations performed on account, 4,600. h. Paid monthly internet service bill of 360. i. Paid the secretarys salary of 2,400. j. Returned 300 of office supplies purchased in transaction f. Received a full refund. k. Received cash from clients previously billed, 3,000. l. Cash was withdrawn for owners personal use, 2,000. Open the file PTRANS from the website for this book at cengagebrain.com. Enter the formulas in the appropriate cells on the worksheet. Then enter the increases and decreases resulting from each transaction on the worksheet. For example, the first transaction increases the cash account by 9,000 and also increases the capital account by 9,000. This transaction has already been recorded on the worksheet. The worksheet will automatically total each column as values are entered in that column. Enter negative numbers with a minus sign (). When you are finished, check row 26 to make sure that total assets equal total liabilities and equity.On June 1 of the current year, Wilson Wood opened Woodys Web Services. This sole proprietorship had the following transactions during June. a. Opened a business checking account and made a deposit, 9,000. b. Paid rent for June for office space and computer equipment, 3,000. c. Purchased office supplies (stamps, pens, etc.) on account, 1,980. d. Received cash for services rendered, 4,500. e. Paid creditor for office supplies purchased on account, 1,400. f. Purchased office supplies for cash, 420. g. Billed clients for consultations performed on account, 4,600. h. Paid monthly internet service bill of 360. i. Paid the secretarys salary of 2,400. j. Returned 300 of office supplies purchased in transaction f. Received a full refund. k. Received cash from clients previously billed, 3,000. l. Cash was withdrawn for owners personal use, 2,000. It has been determined that the cost of the supplies used during the month was 600. Record the increase or decrease in the appropriate columns on the worksheet. This is transaction m. Enter your name in cell A1. Save the completed file as PTRANS3. Print the worksheet. Also print your formulas. Check figure: Ending cash balance (cell B21), 7,220.On June 1 of the current year, Wilson Wood opened Woodys Web Services. This sole proprietorship had the following transactions during June. a. Opened a business checking account and made a deposit, 9,000. b. Paid rent for June for office space and computer equipment, 3,000. c. Purchased office supplies (stamps, pens, etc.) on account, 1,980. d. Received cash for services rendered, 4,500. e. Paid creditor for office supplies purchased on account, 1,400. f. Purchased office supplies for cash, 420. g. Billed clients for consultations performed on account, 4,600. h. Paid monthly internet service bill of 360. i. Paid the secretarys salary of 2,400. j. Returned 300 of office supplies purchased in transaction f. Received a full refund. k. Received cash from clients previously billed, 3,000. l. Cash was withdrawn for owners personal use, 2,000. How does transaction m differ from transactions a through l? In other words, why is it treated as a special item?On June 1 of the current year, Wilson Wood opened Woodys Web Services. This sole proprietorship had the following transactions during June. a. Opened a business checking account and made a deposit, 9,000. b. Paid rent for June for office space and computer equipment, 3,000. c. Purchased office supplies (stamps, pens, etc.) on account, 1,980. d. Received cash for services rendered, 4,500. e. Paid creditor for office supplies purchased on account, 1,400. f. Purchased office supplies for cash, 420. g. Billed clients for consultations performed on account, 4,600. h. Paid monthly internet service bill of 360. i. Paid the secretarys salary of 2,400. j. Returned 300 of office supplies purchased in transaction f. Received a full refund. k. Received cash from clients previously billed, 3,000. l. Cash was withdrawn for owners personal use, 2,000. Use the space below to prepare an income statement, a statement of owners equity (capital statement), and a balance sheet in good form for Woodys Web Services for the month of June.On June 1 of the current year, Wilson Wood opened Woodys Web Services. This sole proprietorship had the following transactions during June. a. Opened a business checking account and made a deposit, 9,000. b. Paid rent for June for office space and computer equipment, 3,000. c. Purchased office supplies (stamps, pens, etc.) on account, 1,980. d. Received cash for services rendered, 4,500. e. Paid creditor for office supplies purchased on account, 1,400. f. Purchased office supplies for cash, 420. g. Billed clients for consultations performed on account, 4,600. h. Paid monthly internet service bill of 360. i. Paid the secretarys salary of 2,400. j. Returned 300 of office supplies purchased in transaction f. Received a full refund. k. Received cash from clients previously billed, 3,000. l. Cash was withdrawn for owners personal use, 2,000. Wilson initially invested 9,000 in the business. Could he have invested less? How little could he have invested initially and never have his cash balance go below zero? To help answer this question, move to column J and analyze the information provided. Then enter different amounts in cell B8 to help determine an answer. When you have determined an answer, use the File Print command to print the range A1 to J21 all on one page. Explain below how you derived your answer.7RThe trial balance of Wikki Cleaners at December 31, 2012, the end of the current fiscal year, is as follows: Information for the adjusting entries is as follows: a. Cleaning supplies on hand on December 31, 2012, 18,750. b. Insurance premiums expired during the year, 1,800. c. Depreciation on equipment during the year, 21,600. d. Wages accrued but not paid at December 31, 2012, 1,830. As the accountant for Wikki Cleaners, you have been asked to prepare financial statements for the year. A file called F1WORK has been provided to assist you in this assignment. As you review this file, it should be noted that columns H and I will automatically change when you enter values in columns E or G.The trial balance of Wikki Cleaners at December 31, 2012, the end of the current fiscal year, is as follows: Information for the adjusting entries is as follows: a. Cleaning supplies on hand on December 31, 2012, 18,750. b. Insurance premiums expired during the year, 1,800. c. Depreciation on equipment during the year, 21,600. d. Wages accrued but not paid at December 31, 2012, 1,830. Open the file F1WORK from the website for the book at cengagebrain.com. Enter the formulas in the appropriate cells on the worksheet. Then enter the adjusting amounts in columns E and G. Also, in column D or F, insert the letter corresponding to the adjusting entry (ad). Column A is frozen on the screen to assist you in completing requirement 3.The trial balance of Wikki Cleaners at December 31, 2012, the end of the current fiscal year, is as follows: Information for the adjusting entries is as follows: a. Cleaning supplies on hand on December 31, 2012, 18,750. b. Insurance premiums expired during the year, 1,800. c. Depreciation on equipment during the year, 21,600. d. Wages accrued but not paid at December 31, 2012, 1,830. Complete the income statement and balance sheet columns by entering formulas in columns J, K, L, and M that reference the appropriate cells in columns H or I. Net income will be automatically calculated for the income statement and balance sheet. Check to be sure that these numbers are the same. Enter your name in cell A1. Save the completed file as F1WORK3. Print the worksheet. Also print your formulas using landscape orientation and fit-to-1 page scaling. Check figure: Net income (cell J30), 71,320.The trial balance of Wikki Cleaners at December 31, 2012, the end of the current fiscal year, is as follows: Information for the adjusting entries is as follows: a. Cleaning supplies on hand on December 31, 2012, 18,750. b. Insurance premiums expired during the year, 1,800. c. Depreciation on equipment during the year, 21,600. d. Wages accrued but not paid at December 31, 2012, 1,830. Suppose you discover that an assistant in your department had misunderstood your instructions and had provided you with the wrong information on two of the adjusting entries. Cleaning supplies consumed during the year should have been 18,750, and insurance premiums unexpired at year-end were 1,800. Make the corrections on your worksheet and save the corrected file as F1WORK4. Reprint the worksheet.The trial balance of Wikki Cleaners at December 31, 2012, the end of the current fiscal year, is as follows: Information for the adjusting entries is as follows: a. Cleaning supplies on hand on December 31, 2012, 18,750. b. Insurance premiums expired during the year, 1,800. c. Depreciation on equipment during the year, 21,600. d. Wages accrued but not paid at December 31, 2012, 1,830. Use the space below to prepare an income statement, a statement of retained earnings, and a balance sheet, using the corrected worksheet (F1WORK4).The trial balance of Wikki Cleaners at December 31, 2012, the end of the current fiscal year, is as follows: Information for the adjusting entries is as follows: a. Cleaning supplies on hand on December 31, 2012, 18,750. b. Insurance premiums expired during the year, 1,800. c. Depreciation on equipment during the year, 21,600. d. Wages accrued but not paid at December 31, 2012, 1,830. With the F1WORK4 file open, click the Chart sheet tab. On the screen, a pie chart shows the percentage composition of the total expenses of Wikki Cleaners. Enter the percentages in the space provided. Compare the percentages of Wikki Cleaners with the national statistics provided. Comment on the differences noted. Why might depreciation, utilities, and rent be so far off from the national percentages? When the assignment is complete, close the file without saving it again. Worksheet. You prepared an income statement in requirement 6. Put a formal income statement somewhere on the F1WORK4 worksheet. Enter formulas in the income statement to reference the appropriate income statement cells in the worksheet. Use proper formats for all value cells. Enter your name above the income statement. Print your work (select and print just the income statement cells). Preview the printout to make sure that the income statement alone (not the whole worksheet) will print neatly on one page. Save the completed file as F1WORKT. Chart. Using the F1WORK4 file, create a 3-D column chart showing the dollar total of each of the expenses of Wikki Cleaners. Use the Chart Data Table as a basis for preparing the chart. Use appropriate titles, legends, and formats. Enter your name somewhere on the chart. Save the file again as F1WORK4. Select the chart and then print it out.The trial balance of Sports Connection at June 30, 2013, the end of the current fiscal year, is as follows: Adjustment information is as follows: a. Supplies on hand as of June 30, 2013, 450. b. Insurance premiums that expired during the year, 2,420. c. Depreciation on equipment during the year, 1,500. d. Included in the rent expense of 30,000 is 1,200 that is prepaid for July 2013. e. Salaries accrued but not paid at June 30, 2013, 1,440. f. Merchandise inventory on June 30, 2013, 68,864. As the accountant for Sports Connection, you have been asked to prepare adjusting entries and financial statements to complete the accounting cycle for the year. A worksheet called P2WORK has been provided to assist you in this assignment. As you review this worksheet, it should be noted that columns H and I will automatically change when you enter values in columns E and G.The trial balance of Sports Connection at June 30, 2013, the end of the current fiscal year, is as follows: Adjustment information is as follows: a. Supplies on hand as of June 30, 2013, 450. b. Insurance premiums that expired during the year, 2,420. c. Depreciation on equipment during the year, 1,500. d. Included in the rent expense of 30,000 is 1,200 that is prepaid for July 2013. e. Salaries accrued but not paid at June 30, 2013, 1,440. f. Merchandise inventory on June 30, 2013, 68,864. Open the file P2WORK from the website for this book at cengagebrain.com. Enter the formulas in the appropriate cells on the worksheet. Then enter the adjusting amounts in columns E and G. Also, in column D or F, insert the letter corresponding to the adjusting entry (ae). (Note: Not all textbooks handle the change in inventory as an adjustment. Use the method for handling inventory that is prescribed in your textbook.) Column A is frozen on the screen to assist you in completing requirement 3.The trial balance of Sports Connection at June 30, 2013, the end of the current fiscal year, is as follows: Adjustment information is as follows: a. Supplies on hand as of June 30, 2013, 450. b. Insurance premiums that expired during the year, 2,420. c. Depreciation on equipment during the year, 1,500. d. Included in the rent expense of 30,000 is 1,200 that is prepaid for July 2013. e. Salaries accrued but not paid at June 30, 2013, 1,440. f. Merchandise inventory on June 30, 2013, 68,864. Complete the income statement and balance sheet by entering formulas in columns J, K, L, and M that reference the appropriate cells in column H or I. Net income will be automatically calculated at the bottom of the income statement and balance sheet columns. Check to be sure that these numbers are the same. Enter your name in cell A1. Save the completed file as P2WORK3. Print the worksheet. Also print your formulas using landscape orientation and fit-to-1 page scaling. Check figure: Net income (cell J34), 37,902.The trial balance of Sports Connection at June 30, 2013, the end of the current fiscal year, is as follows: Adjustment information is as follows: a. Supplies on hand as of June 30, 2013, 450. b. Insurance premiums that expired during the year, 2,420. c. Depreciation on equipment during the year, 1,500. d. Included in the rent expense of 30,000 is 1,200 that is prepaid for July 2013. e. Salaries accrued but not paid at June 30, 2013, 1,440. f. Merchandise inventory on June 30, 2013, 68,864. You discover that your boss has mistakenly provided you with the wrong information on two of the adjusting entries. Expired insurance premiums should have been 3,420, and unpaid salaries should have been 2,440. Make the corrections on your worksheet and save the corrected file as P2WORK4. Reprint the worksheet.The trial balance of Sports Connection at June 30, 2013, the end of the current fiscal year, is as follows: Adjustment information is as follows: a. Supplies on hand as of June 30, 2013, 450. b. Insurance premiums that expired during the year, 2,420. c. Depreciation on equipment during the year, 1,500. d. Included in the rent expense of 30,000 is 1,200 that is prepaid for July 2013. e. Salaries accrued but not paid at June 30, 2013, 1,440. f. Merchandise inventory on June 30, 2013, 68,864. In the space provided below, prepare an income statement, a capital statement (statement of owners equity), and a balance sheet. Use the corrected worksheet (P2WORK4) as a basis for your work. Assume no additional owner investments were made during the year.Open P2WORK4 and click the Chart sheet tab. On the screen, a pie chart shows the percentage composition of the total expenses of Sports Connection. Enter the percentages in the spaces provided. Compare the percentages of Sports Connection with the national statistics provided. Comment on the differences noted. Why might depreciation, utilities, and rent (as a group) be so far off from the national percentages? Any explanation for salaries? Cost of goods sold? When the assignment is complete, close the file without saving it again. TICKLERS (optional) Worksheet. You prepared a balance sheet in requirement 5. Add a new sheet to P2WORK4 and prepare a balance sheet on it using the same format and values from requirement 5. Enter formulas in the balance sheet to reference the appropriate balance sheet cells in the worksheet. Use proper formats for all value cells. Enter your name above the balance sheet. Preview the balance sheet printout to make sure that it will print neatly on one page, and then print the worksheet. Save the completed file as P2WORKT. Chart. Using the P2WORK4 file, create a column chart showing the dollar total of each of the expenses (except Cost of goods sold) of Sports Connection. Use the Chart Data Table as a basis for preparing the chart. Use appropriate titles, legends, and formats. Enter your name somewhere on the chart. Save the file again as P2WORK4. Select the chart and then print it out.1RThe following information is for Bonnies Buds, a nursery and floral shop, for the month ended May 31, 2012: Open the file FMERCH from the website for this book at cengagebrain.com. Enter all formulas and titles where indicated on the worksheet. For example, FORMULA2 is =G29 and TITLE A is Sales Discount. When you are finished, make sure that your balance sheet balances. Enter your name in cell A1. Save your completed file as FMERCH2. Print the worksheet. Also print your formulas. Check figure: Total assets (cell G101), 545,880.3RThe following information is for Bonnies Buds, a nursery and floral shop, for the month ended May 31, 2012: Compare the May and June income statements. Comment on any trends noted.The following information is for Bonnies Buds, a nursery and floral shop, for the month ended May 31, 2012: Click the Chart sheet tab on the FMERCH3 file. You will see a chart depicting the five-month trend in sales, gross profit, and net income. What favorable and unfavorable trends do you see in this month-to-month comparison? Comment on any unusual changes. When the assignment is complete, close the file without saving it again. Worksheet. Your boss would prefer to have the balance sheet shown before the income statement and the statement of retained earnings. Please make this change on the FMERCH3 file. Preview the printout to make sure that the worksheet will print neatly on two or three pages, and then print the worksheet. Save the completed file as FMERCHT. Chart. Using the FMERCH3 file, prepare a 3-D pie chart that shows the amount of each of the selling expenses in June. No Chart Data Table is needed. Select A57 to A62 as one range on the worksheet to be charted and then hold down the CTRL key and select E57 to E62 as the second range. Enter your name somewhere on the chart. Save the file again as FMERCH3. Select the chart and then print it out.1R2R3R4R5RYou have been asked to estimate the total amount of uncollectible accounts expense as of October 31 by completing the file called AGING.2RIn the spaces provided below, record the journal entry for the provision for uncollectible accounts under each of the following independent assumptions: a. The Allowance for Doubtful Accounts before adjustment has a credit balance of 500. b. The Allowance for Doubtful Accounts before adjustment has a debit balance of 250. c. Assume that Octobers credit sales were 70,000. Uncollectible accounts expense is estimated at 2% of sales.Erase the aging information for October and enter the following information for November 30, 2012: Save the results as AGING4. Print the worksheet. Has the estimated total uncollectible accounts increased or decreased in November? Explain.5RDel Rio began Rio Enterprises on January 1 with 200 units of inventory. During the year, 500 additional units were purchased, 500 units were sold, and Del ended the year with 200 units. Del is very satisfied with his first year of business although the cost of replacing his inventory rose continually throughout the year. The 500 units sold for a total of 320,000 and the 500 units purchased to replace them cost 256,000, so his cash account has increased by 64,000. Del is concerned however because he has three obligations yet to meet: taxes, dividends, and his wife. Federal and state income taxes will take 40% of his income. His investors are to receive dividends equal to half of any income after taxes are paid. And finally, Del promised his wife a big trip to Hawaii if she let him quit his job as a professor and start his own business. He promised her hed make at least 50,000 after taxes. That will give us 25,000 after paying off the investors. Del kept fairly good records during the year and knows the specific cost of each inventory unit sold. He has prepared the following table to summarize his purchases and sales. Del has heard that the choice of an inventory cost flow assumption can have a significant effect on net income and taxes. He asks you to show him the differences between the specific identification method and the cost flow assumptions of FIFO, LIFO, and weighted average methods. Review the worksheet FIFOLIFO that follows these requirements. Note that all of the problem data have been entered in the Data Section of the worksheet.Del Rio began Rio Enterprises on January 1 with 200 units of inventory. During the year, 500 additional units were purchased, 500 units were sold, and Del ended the year with 200 units. Del is very satisfied with his first year of business although the cost of replacing his inventory rose continually throughout the year. The 500 units sold for a total of 320,000 and the 500 units purchased to replace them cost 256,000, so his cash account has increased by 64,000. Del is concerned however because he has three obligations yet to meet: taxes, dividends, and his wife. Federal and state income taxes will take 40% of his income. His investors are to receive dividends equal to half of any income after taxes are paid. And finally, Del promised his wife a big trip to Hawaii if she let him quit his job as a professor and start his own business. He promised her hed make at least 50,000 after taxes. That will give us 25,000 after paying off the investors. Del kept fairly good records during the year and knows the specific cost of each inventory unit sold. He has prepared the following table to summarize his purchases and sales. Using a pencil, fill in columns F and G in the Data Section of the worksheet printout at the end of this problem.3RDel Rio began Rio Enterprises on January 1 with 200 units of inventory. During the year, 500 additional units were purchased, 500 units were sold, and Del ended the year with 200 units. Del is very satisfied with his first year of business although the cost of replacing his inventory rose continually throughout the year. The 500 units sold for a total of 320,000 and the 500 units purchased to replace them cost 256,000, so his cash account has increased by 64,000. Del is concerned however because he has three obligations yet to meet: taxes, dividends, and his wife. Federal and state income taxes will take 40% of his income. His investors are to receive dividends equal to half of any income after taxes are paid. And finally, Del promised his wife a big trip to Hawaii if she let him quit his job as a professor and start his own business. He promised her hed make at least 50,000 after taxes. That will give us 25,000 after paying off the investors. Del kept fairly good records during the year and knows the specific cost of each inventory unit sold. He has prepared the following table to summarize his purchases and sales. Examine your completed worksheet and answer the following questions: a. Which inventory cost flow assumption produces the most net income? b. Which inventory cost flow assumption produces the least net income? c. What caused the difference between your answers to a and b? d. Which inventory cost flow assumption produces the highest ending cash balance? e. Which inventory cost flow assumption produces the lowest ending cash balance? f. Does the assumption that produces the highest net income also produce the highest cash balance? Explain. g. As you recall, Del originally used the specific identification method in his initial calculations when he projected 51,600 net income. According to Dels reckoning, that should have left him cash of 25,800 (50% of 51,600) after paying his investors. Why would he only have 3,800 left? Explain. h. Which inventory cost flow assumption would you suggest Del use? Explain.What changes would have taken place if Dels purchase prices had fallen rather than risen? To find out, enter the following values in cells C11 through C14, respectively: 390, 380, 370, and 360. Print the results. Explain what the changes are and why they have taken place.Suppose Dels purchase prices had remained constant. Enter 400 in cells C11 through C14. Explain what changes take place and why.Del Rio began Rio Enterprises on January 1 with 200 units of inventory. During the year, 500 additional units were purchased, 500 units were sold, and Del ended the year with 200 units. Del is very satisfied with his first year of business although the cost of replacing his inventory rose continually throughout the year. The 500 units sold for a total of 320,000 and the 500 units purchased to replace them cost 256,000, so his cash account has increased by 64,000. Del is concerned however because he has three obligations yet to meet: taxes, dividends, and his wife. Federal and state income taxes will take 40% of his income. His investors are to receive dividends equal to half of any income after taxes are paid. And finally, Del promised his wife a big trip to Hawaii if she let him quit his job as a professor and start his own business. He promised her hed make at least 50,000 after taxes. That will give us 25,000 after paying off the investors. Del kept fairly good records during the year and knows the specific cost of each inventory unit sold. He has prepared the following table to summarize his purchases and sales. Reset the purchase prices to their original values (cells C11 through C14). Suppose Del had purchased 250 units on November 20 rather than 150. Enter 250 in cell C14 and alter column G in the Data Section. Explain what happens to net income under each inventory cost flow assumption and why. Also, what management implications might this have for Del?Reset the November 20 purchase to 150 units, including column G. To test your formulas, suppose that Del had sold 600 units rather than 500. Sales now total 384,000. The extra units sold come from the May 13 purchase (25 units) and the November 20 purchase (75 units). Change cell B17 to 600 and cells D32 through G32 to 384,000. Alter columns E, F, and G in the Data Section to reflect the change. Your formulas should automatically redo the Calculations and Answer sections. Print the results again.Click the Chart sheet tab. On the screen is a column chart showing ending inventory costs. During a deflationary period, which bar (A, B, or C) represents FIFO costing, which represents LIFO costing, and which represents weighted average? Explain your reasoning. On January 4 following year-end, Rio Enterprises received a shipment of 60 units of product costing 580 each. These units had been ordered by Del in December and had been shipped to him on December 27. They were shipped FOB shipping point. Revise the FIFOLIFO3 worksheet to include this shipment. Preview the printout to make sure that the worksheet will print neatly on one page, and then print the worksheet. Save the completed file as FIFOLIFOT. Using the FIFOLIFO3 file, prepare a 3-D bar (stacked) chart showing the cost of goods sold and ending inventory under each of the four inventory cost flow assumptions. No Chart Data Table is needed. Use the values in the Calculations Section of the worksheet for your chart. Enter your name somewhere on the chart. Save the file again as FIFOLIFO3. Print the chart.On September 30, 2013, the general ledger of Leons Golf Shop, which uses the calendar year as its accounting period, showed the following year-to-date account balances: The merchandise inventory account had a 48,000 balance on January 1, 2013. The historical gross profit percentage is 40%. Leon prepares quarterly financial statements and takes physical inventory once a yearat the end of the accounting period. In order to prepare the financial statements for the third quarter, the store needs to have an estimate of ending inventory. You have been asked to use the gross profit method to estimate the ending inventory. Review the worksheet called GP. Study it carefully because it may have a solution format somewhat different from the one shown in your textbook.2ROn December 31, 2013, the year-to-date account balances of selected accounts were as follows: Estimated ending merchandise inventory at December 31, 2013, is __________.A physical count of merchandise inventory on December 31, 2013, revealed inventory costing 22,000. In the space below, list at least two possible reasons for this balance to be different from the estimate computed in requirement 3.5RDunedin Drilling Company recently acquired a new machine at a cost of 350,000. The machine has an estimated useful life of four years or 100,000 hours, and a salvage value of 30,000. This machine will be used 30,000 hours during Year 1, 20,000 hours in Year 2, 40,000 hours in Year 3, and 10,000 hours in Year 4. Dunedin buys equipment frequently and wants to print a depreciation schedule for each assets life. Review the worksheet called DEPREC that follows these requirements. Since some assets acquired are depreciated by straight-line, others by units of production, and others by double-declining balance, DEPREC shows all three methods. You are to use this worksheet to prepare depreciation schedules for the new machine.Open the file DEPREC from the website for this book at cengagebrain.com. The formulas on this worksheet are somewhat complicated. Be sure to use cell references wherever possible in your formulas instead of numbers. You should find the year numbers in column B helpful for some of the formulas in columns C, D, and E. Your instructor may tell you whether you are to construct your own formulas or use =SLN and =DDB (see Appendix A of Excel Quick for an explanation of these functions). For example, FORMULA1 could be either =(D7D8)/D9 or =SLN(D7, D8, D9). Assume that all assets acquired will have at least a three-year life. Note that all cells on the worksheet containing zeros have been preprogrammed to perform depreciation calculations. Enter the formulas in the appropriate cells. Does your depreciation total 320,000 under all three methods? If not, correct your error. (Hint: If your double-declining-balance method is off, check cell E32 where FORMULA12 is located. It should include an =IF statement that will enter a modified calculation of depreciation if Year 4 is the last year of the assets expected life. See Appendix A of Excel Quick for an explanation of the =IF function. Save the completed file as DEPREC2. Print the worksheet. Also print your formulas using landscape orientation and fit-to-1 page scaling. Check figure: DDB depreciation for Year 3 (cell E31), 43,750.In the space below, prepare the journal entry to record the depreciation taken in Year 3 under the units of production method.To test your formulas, assume the machine purchased had an estimated useful life of three years (20,000, 30,000, and 50,000 hours, respectively). Enter the new information in the Data Section of the worksheet. Does your depreciation total 320,000 under all three methods? There are three common errors made by students completing this worksheet. Lets clear up two of them. One, an asset that has a three-year life should have no depreciation claimed in Year 4. This can be corrected using an =IF statement in Year 4. For example, the correct formula in cell C32 is =IF(B32D9,0,(D7D8)/D9) or =IF(B32D9, 0, SLN(D7, D8, D9)). You may wish to edit what you have already entered rather than retype it. Two, as mentioned in requirement 2, the double-declining-balance calculation needs to be modified in the last year of the assets life. Assuming you have already modified the formula for Year 4 (per instructions in step 2), alter the formula for Year 3 also. If you corrected any formulas, test their correctness by trying different estimated useful lives (between 3 and 8) in cell E9. Then reset the Data Section to the original values, save the revised file as DEPREC2, and reprint the worksheet to show the correct formulas. The third common error doesnt need to be corrected in this problem. The general form of the double-declining-balance formula needs to be modified to check the net book value of the asset each year to make sure it does not go below salvage value. =DDB does this automatically, but if you are writing your own formulas, this gets very complicated and is beyond the scope of the problem.A truck was recently purchased for 75,000 with a salvage value of 5,000 and an estimated useful life of eight years or 150,000 miles (24,000 miles per year for the first five years and 10,000 miles per year after that). Enter the new information in the Data Section of the worksheet. Again, make sure the totals for all three methods are in agreement. Print the worksheet. Save this new data as DEPREC5.Dunedin Drilling Company recently acquired a new machine at a cost of 350,000. The machine has an estimated useful life of four years or 100,000 hours, and a salvage value of 30,000. This machine will be used 30,000 hours during Year 1, 20,000 hours in Year 2, 40,000 hours in Year 3, and 10,000 hours in Year 4. With DEPREC5 still on the screen, click the Chart sheet tab. This chart shows the accumulated depreciation under all three depreciation methods. Identify below the depreciation method that each represents. Series 1 _____________________ Series 2 _____________________ Series 3 _____________________ When the assignment is complete, close the file without saving it again. Worksheet. The problem thus far has assumed that assets are depreciated a full year in the year acquired. Normally, depreciation begins in the month acquired. For example, an asset acquired at the beginning of April is depreciated for only nine months in the year of acquisition. Modify the DEPREC2 worksheet to include the month of acquisition as an additional item of input. To demonstrate proper handling of this factor on the depreciation schedule, modify the formulas for the first two years. Some of the formulas may not actually need to be revised. Do not modify the formulas for Years 3 through 8 and ignore the numbers shown in those years. Some will be incorrect as will be some of the totals. Preview the printout to make sure that the worksheet will print neatly on one page, and then print the worksheet. Save the completed file as DEPRECT. Hint: Insert the month in row 6 of the Data Section specifying the month by a number (e.g., April is the fourth month of the year). Redo the formulas for Years 1 and 2. For the units of production method, assume no change in the estimated hours for both years. Chart. Using the DEPREC5 file, prepare a line chart or XY chart that plots annual depreciation expense under all three depreciation methods. No Chart Data Table is needed; use the range B29 to E36 on the worksheet as a basis for preparing the chart if you prepare an XY chart. Use C29 to E36 if you prepare a line chart. Enter your name somewhere on the chart. Save the file again as DEPREC5. Print the chart.Based on 2011 tax rates provided, use a calculator to compute how much would be withheld from Joness November paycheck in the following three cases (round to the nearest penny):You have been asked to record the November payroll information using a payroll register and a general journal. Review the printout of the worksheet PR, a computerized payroll register, that follows these requirements. The columns will automatically retotal as new entries are made. Entries in column B indicate whether or not employees are union members. Assume Joness cumulative gross pay at October 31 could be 85,000, or 105,000, or 125,000.To make the worksheet reusable each month, the Social Security tax formulas should be designed to automatically compute whether (1) full tax is due. (2) no tax is due (e g., cumulative gross pay is over the ceiling), or (3) some tax is due. =IF statements will be required. The =ROUND function should be used for FORMULAS 1 through 3 to eliminate rounding errors. FORMULAS 4 and 5 should also use the =IF function. FORMULA2 has been provided for you below. Review Appendix A of Excel Quick and explain the meaning of each part of the formula. a. b. c. d. e.4R5RIn the space provided below, prepare the journal entry to record the November payroll for all employees assuming that the payroll is paid on November 30 and that Joness cumulative gross pay (cell I13) is 85,000.In the space provided below, prepare the journal entry as of November 30 to record the employers payroll taxes for November. Also prepare the journal entries to record the payment of all payroll taxes and union dues, assuming that they are all due on December 15.8RClick the Chart sheet tab. On the screen is a chart of the four payroll taxes that a company might incur in total during the year. The four taxes are federal unemployment, state unemployment, Social Security, and Medicare. By the behaviors shown on the chart, identify below which of the four taxes each represents. Series 1 _____ Series 2 _____ Series 3 _____ Series 4 _____ When the assignment is complete, close the file without saving it again. TICKLERS (optional) Worksheet. A new employee was hired during November and was mistakenly omitted from the payroll register. The employees last name is Zuckerman, and his gross pay for November is 1,300. Add the new employee to the PR5 worksheet and include all standard withholding rates in computing net pay. Federal income tax withheld is 101. He is a union member. Preview the printout to make sure that the worksheet will print neatly on one page, and then print the worksheet. Save the completed file as PRT. Chart. Using the PR4 file, prepare a 3-D pie chart to show the percentage of an employees gross pay that is withheld for taxes, Social Security, and so on, and how much is left over as net pay. Use Smith as your example. Complete the Chart Tickler Data Table and use it as a basis for preparing the chart. Enter your name somewhere on the chart. Save the file again as PR4. Print the chart.The University Club recently issued 1,500,000 of 10-year, 9% bonds at an effective interest rate of 10%. Bond interest is payable annually. You have been asked to calculate the issuance price of the bonds and prepare amortization schedules for any discount or premium. The worksheet BONDS has been provided to assist you. Note that the worksheet contains a scratch pad at the bottom that has been preprogrammed to automatically compute and display the relevant cash flows needed for bond pricing.The bond pricing formula utilizes the NPV (Net Present Value) function on your spreadsheet program. The formula is broken into two parts as identified by the letters in parentheses above the formula. See Appendix A in Excel Quick for a discussion on the NPV function, and then explain the meaning of each part of the formula. a. b.3R4RUse the worksheet to compute the bond issue price and amortization schedules if the effective interest rate is 8.2%. Save the file as BONDS5. Print the worksheet when done. Also, repeat requirement 4 in the space provided below for this bond.Use the worksheet to compute the bond issue price if the effective interest rate is 9%. Bond issue price _____7Ra. Reset the Data Section to its initial values. The price of this bond is 1,407,831. What would it be if there were only 9 or 8 years to maturity? Use the worksheet to compute the bond issue prices and enter them in the spaces provided. Bond issue price (9 years to maturity) __________________ Bond issue price (8 years to maturity) __________________ b. Compare these prices to the bond-carrying values found in the effective interest amortization schedule you originally printed out in requirement 3. Explain the similarity. c. Click the Chart sheet tab. The chart presented shows the price behavior of this bond based on years to maturity. Explain what effect years to maturity has on bond prices. Check your explanation by trying 8% as the effective rate (cell E10) and clicking the Chart sheet tab again. Also try 9%. When the assignment is complete, close the file without saving it again. Worksheet. Modify the BONDS3 worksheet to accommodate bonds with up to 20-year maturity. Use your new model to determine the issue price and amortization schedules of a 2,000,000, 18-year, 10% bond issued to yield 9%. Preview the printout to make sure that the worksheet will print neatly, and then print the worksheet. Save the completed file as BONDST. Hint: Expand both amortization schedules to 20 years. Expand the scratch pad to 20 years. Modify FORMULA1 in cell F17 to include the new ranges. Chart. Using the BONDS3 file, prepare a line chart that plots annual interest expense over the 10-year life of this bond under both the straight-line and effective interest methods. No Chart Data Table is needed. Put A23 to A32 in the Label format and then select A23 to A32, D23 to D32, and B40 to B49 as a collection. Enter all appropriate titles, legends, formats, and so forth. Enter your name somewhere on the chart. Save the file again as BONDS3. Print the chart.Chen Corporation began 2012 with the following stockholders equity balances: The following selected transactions and events occurred during the year: a. Issued 10,000 shares of common stock for 60,000. b. Purchased 1,200 shares of treasury stock for 4,800. c. Sold 2,000 shares of treasury stock for 11,000. d. Generated net income of 94,000. e. Declared and paid the full years dividend on preferred stock and a dividend of 1.00 per share on common stock outstanding at the end of the year. Chen Corporation maintains several paid-in capital accounts (Paid-in Capital in Excess of Par, Paid-in Capital from Treasury Stock, etc.) in its ledger, but combines them all as Additional paid-in capital when preparing financial statements.Chen Corporation began 2012 with the following stockholders equity balances: The following selected transactions and events occurred during the year: a. Issued 10,000 shares of common stock for 60,000. b. Purchased 1,200 shares of treasury stock for 4,800. c. Sold 2,000 shares of treasury stock for 11,000. d. Generated net income of 94,000. e. Declared and paid the full years dividend on preferred stock and a dividend of 1.00 per share on common stock outstanding at the end of the year. Chen Corporation maintains several paid-in capital accounts (Paid-in Capital in Excess of Par, Paid-in Capital from Treasury Stock, etc.) in its ledger, but combines them all as Additional paid-in capital when preparing financial statements. Open the file STOCKEQ from the website for this book at cengagebrain.com. Enter the formulas in the appropriate cells on the worksheet. Then fill in the columns to show the effect of each of the selected transactions and events listed earlier. Enter your name in cell A1. Save the completed worksheet as STOCKEQ2. Print the worksheet. Also print your formulas. Check figure: Total stockholders equity balance at 12/31/12 (cell G21). 398,800.Chen Corporation began 2012 with the following stockholders equity balances: The following selected transactions and events occurred during the year: a. Issued 10,000 shares of common stock for 60,000. b. Purchased 1,200 shares of treasury stock for 4,800. c. Sold 2,000 shares of treasury stock for 11,000. d. Generated net income of 94,000. e. Declared and paid the full years dividend on preferred stock and a dividend of 1.00 per share on common stock outstanding at the end of the year. Chen Corporation maintains several paid-in capital accounts (Paid-in Capital in Excess of Par, Paid-in Capital from Treasury Stock, etc.) in its ledger, but combines them all as Additional paid-in capital when preparing financial statements. In the space provided below, prepare the stockholders equity section of Chen Corporations balance sheet as of December 31, 2012. Use proper headings and provide full disclosure of all appropriate information. Chens corporate charter authorizes the issuance of 1,000 shares of preferred stock and 100,000 shares of common stock.The following selected transactions and events occurred during 2013: a. Issued 200 shares of preferred stock for 20,000. b. Sold 800 shares of treasury stock for 2,800. c. Declared and issued a 4% common stock dividend. The market value on the date of declaration was 5 per share. d. Generated a net loss for the year of 16,000. e. Declared and paid the full years dividend on all the preferred stock and a dividend of 15 per share on common stock outstanding at the end of the year. Enter beginning balances for 2013 on STOCKEQ2. Then erase all 2012 entries and enter the transactions for 2013. Save the results as STOCKEQ4. Print the results.Click the Chart sheet tab. The stacked bar chart shows Chens equity account balances at December 31, 2013. Match the stacked bars (AG) that best describe what will happen to the equity accounts if the following transactions and events occur in 2014. Letters may be repeated or not used. Consider each case independently. When the assignment is complete, close the file without saving it again. TICKLERS (optional) Worksheet. Suppose that the 54,000 Additional paid-in capital balance at December 31, 2011, comes from two ledger accounts: 42,000 from Paid-in capital in excess of par and 12,000 from Paid-in capital from treasury stock transactions. Revise the STOCKEQ2 worksheet to show a column for each of these accounts instead of the single column for Additional paid-in capital. Then redo the 2012 transactions using the new columns. Preview the printout to make sure that the worksheet will print neatly on one page, and then print the worksheet. Save the file as STOCKEQT. Chart. Using the STOCKEQ4 file, prepare a column chart showing the dollar amount of each of the stockholders equity account balances at December 31, 2013. Treasury stock can be shown as a negative value. Enter your name somewhere on the chart. Save the file again as STOCKEQ4. Print the chart.Ponce Towers, Inc., had 50,000 shares of common stock and 10,000 shares of 100 par value, 8% preferred stock outstanding on January 1, 2011. Each share of preferred stock is convertible into four shares of common stock. The stock has not been converted. During the year, Ponce Towers issued additional shares of common stock as follows: For 2011, Ponce Towers, Inc., had income from continuing operations of 545,000 and a 72,000 loss from discontinued operations (net of tax). As vice president of finance for the firm, you have been asked to calculate earnings per share for 2011. The worksheet EPS has been provided to assist you.Ponce Towers, Inc., had 50,000 shares of common stock and 10,000 shares of 100 par value, 8% preferred stock outstanding on January 1, 2011. Each share of preferred stock is convertible into four shares of common stock. The stock has not been converted. During the year, Ponce Towers issued additional shares of common stock as follows: For 2011, Ponce Towers, Inc., had income from continuing operations of 545,000 and a 72,000 loss from discontinued operations (net of tax). Open the file EPS from the website for this book at cengagebrain.com. Enter all input items (AF) in the appropriate cells in the Data Section. Enter all formulas in the appropriate cells in the Answer Section. Enter your name in cell A1. Save the completed file as EPS2. Print the worksheet when done. Also print your formulas. Check figure: Basic earnings per share from continuing operations (cell D29), 5.94.During 2012, Ponce Towers issued 30,000 additional shares of common stock on June 1 and 24,000 on November 1. The company earned 602,000 from continuing operations and 28,000 from another segment of the business that was discontinued during the year. Use your completed worksheet to prepare a computation of earnings per share for 2012. Erase any data in the Data Section that are not required for 2012. Save the solution for 2012 as EPS3 and print the results.4RThe comparative balance sheet of Prime Sports Gear, Inc., at December 31, the end of the fiscal year, is as follows: Additional data obtained from the records of Prime Sports Gear are as follows: a. Net income for 2013 was 121,610. b. Depreciation reported on income statement for 2013 was 46,500. c. Purchased 165,000 of new equipment, putting 90,000 cash down and issuing 75,000 of bonds for the balance. d. Old equipment originally costing 19,500, with accumulated depreciation of 7,950, was sold for 8,000. e. Retired 60,000 of bonds. f. Declared cash dividends of 64,000. g. Issued 1,500 shares of common stock at 27 cash per share. You have been asked to prepare a statement of cash flows for Prime Sports Gear for 2013. Review the worksheet called CASHFLOW that has been provided to assist you in preparing the statement. The worksheet has been designed so that as you make entries in columns D and F, column G will be automatically updated. For example, FORMULA1 should be entered as =B17+D17F17. Columns C and E are to be used to enter letter references for each of the debit and credit entries on the worksheet.The comparative balance sheet of Prime Sports Gear, Inc., at December 31, the end of the fiscal year, is as follows: Additional data obtained from the records of Prime Sports Gear are as follows: a. Net income for 2013 was 121,610. b. Depreciation reported on income statement for 2013 was 46,500. c. Purchased 165,000 of new equipment, putting 90,000 cash down and issuing 75,000 of bonds for the balance. d. Old equipment originally costing 19,500, with accumulated depreciation of 7,950, was sold for 8,000. e. Retired 60,000 of bonds. f. Declared cash dividends of 64,000. g. Issued 1,500 shares of common stock at 27 cash per share. Open the file CASHFLOW from the website for this book at cengagebrain.com. First, enter the formulas. Then, complete the worksheet in the manner described next. According to the problem, cash increased from 39,600 to 67,210 during the year. This is a 27,610 increase. To record this increase on the worksheet, move to row 17. Since this is the first account you are analyzing, enter the letter a in column C. Then enter 27610 in column D (a debit since cash increased). This brings the year-end balance (column G) to 67,210, its proper balance. Now move to the bottom part of the statement where you see the categories Operating Activities, Investing Activities, and so on. The credit side of the entry has to be entered here. The proper space for this cash entry is on row 59. Enter the letter a in cell E59 and 27610 in cell F59. Notice the totals at the bottom of the page (row 60) now agree. The next account balance that changed is accounts receivable. It increased by 9,035. To enter this change on the worksheet, enter the letter b in cell C18 and 9035 in cell D18 (again, a debit since accounts receivable increased). This brings the year-end balance in column G to 121,250, its proper balance. The change in accounts receivable balance is an operating activity adjustment (as explained in your textbook). Enter the credit side of this entry in cells E34 and F34, and enter the explanation Increase in accounts receivable in cell A34. Note: Your textbook probably shows Net income as the first item under Operating Activities. We will get to that later. The sequence in which you enter items on this worksheet is not important. All other balance sheet accounts must be analyzed in the same manner, placing appropriate debit or credit entries in the top part of the worksheet to obtain the proper balances in column G, and then entering the second side of the entry in the appropriate row on the bottom part of the worksheet. You should use letter references to identify all entries. Also, you must enter a description of the entry in column A under the appropriate activity category. Although a sequence of analyzing the balance sheet from top to bottom is suggested here, this order is not necessary. As mentioned earlier, your textbook may specify a different sequence. Also, note that some accounts may have both debit and credit adjustments to them. The worksheet is not a substitute for a statement of cash flows, but it does provide you with all the numbers you need to properly prepare one. You will be done with your analysis when: a. The individual account balances at December 31, 2013, as shown on the worksheet (column G) equal those shown in the given problem data. b. The transaction column totals are equal (cells D60 and F60). c. The sum of the operating, investing, and financing activities (cell G59) equals the change in cash (cell D59 or F59). When you are finished, enter your name in cell A1. Save your completed file as CASHFLOW2. Print the worksheet when done. Also print your formulas. Check figure: Total credits at 12/31/2013 (cell G31), 860,460.In the space provided on the next page, prepare a statement of cash flows in good form using the indirect method. Use the format shown in your textbook.Suppose that an audit of Prime Sports Gear encountered the following two errors: a. Inventory totaling 4,500 should have been written off as worthless at the end of the year. Year-end inventory should be only 195,600. Net income is reduced to 117,110. b. Checks totaling 20,000 for some of the salaries payable at year-end had in fact been written and mailed out on December 31, 2013. Thus, both the cash account and the salaries payable account are overstated at year-end. Correct both errors on the worksheet. Save your completed file as CASHFLOW4. Print the worksheet when done. What impact did each of these adjustments have on cash flow for 2013?5RThe comparative financial statements of Global Technology are as follows: Review the worksheet RATIOA that follows these requirements. You have been asked to perform a ratio analysis of this company for 2012.The comparative financial statements of Global Technology are as follows: Open the file RATIOA from the website for this book at cengagebrain.com. Enter the formulas in the appropriate cells. Enter your name in cell A1. Save the completed model as RATIOA2. Print the worksheet when done. Also print your formulas. Check figure: Acid test (quick) ratio (cell C58), .82.a. What information does a comparison of the current ratio and acid test ratio provide? b. Is the company using leverage to its advantage? Explain. c. What other observations can be made comparing Global Technologys ratios to the following industry norms:Prepare a ratio analysis for Global Technology for 2013. The following information is available for 2013: The 2013 information should be entered in column B of the RATIOA2 worksheet. The 2012 information should be entered in column C. Save the revised file as RATIOA4. Print the worksheet when done.Compare your printout from requirement 2 with your printout from requirement 4. From these two sets of ratios, what conclusions can be drawn concerning changes from 2012 and 2013?With the 2013 data still on the screen, click the Chart sheet tab. The chart presented shows the rates of return for Global Technology for the last five years. Answer the following questions: a. In 2009, the rate of return on assets exceeded the rate of return on common stockholders equity. Why might this have occurred? Be as specific as possible. b. Is the company better off in 2013 than it was in 2009? Why or why not? When the assignment is complete, close the file without saving it again. Worksheet. Modify the RATIOA4 worksheet to have it compute two additional activity ratios: number of days sales in receivables and number of days sales in merchandise inventory. Use the 2012 and 2013 data and assume a 365-day year. Write out the formulas for your ratios in the spaces provided. Days sales in receivables (average collection period) ________________ Days sales in inventory (average sales period) ________________ Preview the printout to make sure that the worksheet will print neatly, and then print the worksheet. Save the completed file as RATIOAT. Chart. Using the RATIOA4 file, prepare a column chart that compares the acid test and current ratios for Global Technology for 2012 and 2013. Complete the Chart Tickler Data Table and use it as a basis for preparing the chart. Enter all appropriate titles, legends, and formats. Enter your name somewhere on the chart. Save the file again as RATIOA4. Print the chart.The following information is for Twisp Industries for the year ended December 31, 2011: REQUIREMENT You have been asked to prepare a schedule of cost of goods manufactured and sold for the year just ended. Review the worksheet MFG that follows these requirements.2RThe following data pertain to 2012 activities of Twisp Industries: Use your completed worksheet to determine the firms cost of goods sold for 2012. Remember to change the year in row 24 and to enter new beginning inventory balances. Save the 2012 file as MFG3. Print the worksheet when done. If sales and other expenses were identical in 2011 and 2012, during which year did Twisp earn more income? Why?Open MFG2 and click the Chart sheet tab. The management of Twisp is convinced that the quality of its products is highly dependent on their relative labor costs. Experience has shown that direct labor should account for at least 45% of the total product cost. According to the pie chart that appears on the screen, Twisp did not achieve this goal in 2011. How much should Twisp have spent on direct labor to reach its 45% goal? To find out, try different values for direct labor (cell C8), clicking the Chart sheet tab after each attempt. When you find a direct labor level that increases the direct labor percentage to 45%, enter the answer in the space provided: In 2011, direct labor needed to be ________________. Open MFG3 and click the Chart sheet tab. Did Twisp achieve its 45% goal in 2012? If not, answer the following: In 2012, direct labor needed to be ________________. When the assignment is complete, close the files without saving them again. Worksheet. The MFG2 worksheet presents the companys manufacturing activities for 2011. The company also had the following selling and general activities in 2011: sales of 13,503,000, selling expenses of 2,400,000, and general expenses of 1,200,000. Modify the worksheet to include this information in the Data Section and change the Answer Section so that it is in the form of an income statement. Preview the printout to make sure that the worksheet will print neatly on one page, and then print the worksheet. Save the completed file as MFGT. Hint: Expand the Data Section to include these additional input items alphabetically. Insert a row for sales under the heading in the Answer Section. Add the rest of the income statement information to the bottom of the schedule. You will need to enter formulas for gross profit, all the expenses, and net income. You will also need to change the statement name. Chart. Using the MFG2 file, prepare a single 3-D bar chart to show the dollar amount of materials, labor, and overhead incurred by Twisp in 2011 and 2012. Complete the Chart Data Table and use it as a basis for preparing the chart. Enter all appropriate titles, legends, and formats. Enter your name somewhere on the chart. Save the file again as MFG2. Print the chart.Nutt Products manufactures screws and bolts made to customer specifications. During August, Nutt incurred the following manufacturing costs: direct materials, 28,019.00; direct labor, 15,276.75; and applied factory overhead, 9,854.50. The following data pertain to these costs: The overhead application rates are 4 per direct labor hour for Dept. 1 and 175% of direct labor cost for Dept. 2. Nutt had no beginning work in process for August. Job 8958, which cost 14,190.18 to manufacture, was completed in July and was sold on account in August for 19,000. The job cost sheet for this job is shown on page 103. Of the jobs begun in August, Job 8961 was completed and sold on account for 24,000, Jobs 8962 and 8964 were completed but not sold, and Job 8963 was still in process. As cost accountant for this company, you have been asked to prepare job cost sheets for each of the four jobs started in August. Review the printed worksheet called JOB that follows these requirements.2RNow that your worksheet has been saved as JOB2, fill in the appropriate data for Job 8961. Save the results as JOB8961 and print the completed job cost sheet when done. Check figure: Cost of Job 8961 (cell G31), 16,229.35.Next, open worksheet JOB2 again. Enter the data for Job 8962, save the results as JOB8962, and print the worksheet. Repeat these steps for Jobs 8963 and 8964.Use the space provided below to prepare six summary journal entries for the month of August. These entries record (1) cost of direct materials used, (2) cost of direct labor, (3) cost of applied overhead, (4) cost of jobs completed, (5) cost of goods sold, and (6) total sales on account. Then set up T-accounts for Work in Process, Finished Goods, Cost of Goods Sold, and Sales. Post the entries to the appropriate accounts and then balance each account. Finally, prepare a supporting schedule by job number showing the cost of ending work in process, finished goods, and cost of goods sold.Nutts management is very concerned about the cost of overhead on its jobs. When jobs are complete, overhead costs should be between 15% and 20% of total costs. For example, the labor cost on Job 8958 is 25% of total costs, higher than the norm. Open Job 8961 and click the Chart sheet tab. A pie chart appears showing the cost components on that job. Record the labor cost percentage in the space provided. Repeat this for each of the jobs worked on in August. Did Nutt maintain good cost control on all its jobs? Explain. Worksheet. During September, Job 8963 required two additional material requisitions to complete the job. Open JOB8963 and modify the job cost sheet to include an area for four direct material requisition entries instead of three. Then enter the following two materials requisitions onto the worksheet: Preview the printout to make sure it will print neatly on one page, and then print the worksheet. Save the completed worksheet as JOBT. Chart. Open JOB8964 and click the Chart sheet tab. Prepare a bar chart for JOB8964 showing the amount of material, labor, and overhead required to complete the job. Use the Chart Data Table found in rows 4246 as a basis for preparing the chart. Enter your name somewhere on the chart. Save the file again as J0B8964. Print the chart.Poleski Manufacturing, which maintains the same level of inventory at the end of each year, provided the following information about expenses anticipated for next year: The selling price of Poleskis single product is 16. In recent years, profits have fallen and Poleskis management is now considering a number of alternatives. Poleski wants to have a net income next year of 250,000, but expects to sell only 120,000 units unless some changes are made. The president of Poleski has asked you to calculate the companys projected net income (assuming 120,000 units are sold) and the sales needed to achieve the companys net income objective for next year. Also, compute Poleskis contribution margin per unit, contribution margin ratio, and break-even point for next year. The worksheet CVP has been provided to assist you. Note that the data from the problem have already been entered into the Data Section of the worksheet.Open the file CVP from the website for this book at cengagebrain.com. Enter the formulas where indicated on the worksheet. Enter your name in cell A1. Save the solution as CVP2 and print the worksheet. Also print your formulas. Check figures: Break-even point in sales dollars (cell C35), 1,616,000; Net income (cell C38), 95,000.Based on Poleskis current situation, will it earn its target net income? If not, how many units need to be sold to achieve the target? Explain.The president of Poleski would like to know the effect that each of the following suggestions for improving performance would have on contribution margin per unit, sales needed to break even, and projected net income for next year. Each change should be considered independently. Reset the Data Section to its original values after each suggestion is analyzed. Fill in the table following the suggestions with the results of your analysis. a. The president suggests cutting the products price. Since the market is relatively sensitive to price, . . . a 10% cut in price ought to generate a 30% increase in sales (to 156,000 units). How can you lose? b. The sales manager feels that putting all sales personnel on straight commission would help. This would eliminate 77,000 in fixed sales salaries expense. Variable sales commissions would increase to 2.00 per unit. This move would also increase sales volume by 30%. c. Poleskis head of product engineering wants to redesign the package for the product. This will cut 1.00 per unit from direct materials and 0.50 per unit from direct labor, but will increase fixed factory overhead by 100,000 for additional depreciation on the new packaging machine. The package redesign would not affect sales volume. d. The firms consumer marketing manager suggests undertaking a new advertising campaign on Facebook. This would cost 30,000 more than is currently planned for advertising but would be expected to increase sales volume by 30%. e. The production superintendent suggests raising quality and raising price. This will increase direct materials by 1.00 per unit, direct labor by 0.50 per unit, and fixed factory overhead by 110,000. With improved quality, . . . raise the price to 18.50 and advertise the heck out of it. If you double your current planned advertising, Ill bet you can increase your sales volume by 30%.From this analysis, which suggestion would you advise Poleski Manufacturing to try? Explain.The records of Anderjak Corporation contain the following information for the month of January:
The company has no beginning inventory.
REQUIREMENT
You have been asked to prepare a variable costing (direct costing) income statement and an absorption costing income statement for the month of January. Review the worksheet VARCOST that follows these requirements.
Open the file VARCOST from the website for this book at cengagebrain.com. Enter the formulas where indicated on the worksheet. Enter your name in cell A1. Save the completed file as VARCOST2. Print the worksheet when done. Also print your formulas. Check figure: Absorption income (cell C31), 768,000.In the space below, explain why the operating income calculated by the absorption method is not the same as that calculated by the variable cost method.To determine the effect of different levels of production on the company’s income, move to cell B7 (Actual production). Change the number in B7 to the different production levels given in the table below. The first level, 100,000, is the current level. What happens to the operating income on both statements as production levels change? Enter the operating incomes in the following table.
Does the level of production affect income under either costing method? Explain your findings.
Click the Chart sheet tab. This chart is based on the problem data and the two income statements. Answer the following questions about the chart: a. What is the title for the X-axis? b. What is the title for the Y-axis? c. What does data range A represent? d. What does data range B represent? e. Why do the two data ranges cross? f. What would be a good title for this chart? When the assignment is complete, close the file without saving it again. Worksheet. The VARCOST2 worksheet is capable of calculating variable and absorption income when unit sales are equal to or less than production. An equally common situation (that this worksheet cannot handle) is when beginning inventory is present and sales volume exceeds production volume. Revise the worksheet Data Section to include: Also, change actual production to 70,000. Revise the Answer Section to accommodate this new data. Assume that Anderjak uses the weighted-average costing method for inventory. Preview the printout to make sure that the worksheet will print neatly on one page, and then print the worksheet. Save the completed file as VARCOSTT. Check figure: Absorption income, 670,000. Chart. Using the VARCOST2 file, fix up the chart used in requirement 5 by adding appropriate titles and legends and formatting the X- and Y-axes. Enter your name somewhere on the chart. Save the file again as VARCOST2. Print the chart.Bumblebee Mobiles manufactures a line of cell phones. The management has identified the following overhead costs and related cost drivers for the coming year. The following were incurred in manufacturing two of their cell phones, Bubble and Burst, during the first quarter. REQUIREMENT Review the worksheet called ABC that follows these requirements. You have been asked to determine the cost of each product using an activity-based cost system. Note that the problem information is already entered into the Data Section of the ABC worksheet.2R3R4R5R6R7ROn January 1, Sweet Pleasures, Inc., begins business. The company has 14,000 cash on hand and is attempting to project cash receipts and disbursements through April 30. On May 1, a note payable of 10,000 will be due. This amount was borrowed on January 1 to carry the company through its first four months of operation. The unit purchase cost of the companys single product, a box of Sweet Pleasures chocolates, is 12. The unit sales price is 28. Projected purchases and sales in units for the first four months are: Sales terms call for a 5% discount if paid within the same month that the sale occurred. It is expected that 50% of the billings will be collected within the discount period, 25% by the end of the month after purchase, 19% in the following month, and 6% will be uncollectible. Approximately 60% of the purchases are paid for in the month purchased. The rest are due and payable in the next month. Total fixed marketing and administrative expenses for each month include cash expenses of 5,000 and depreciation on equipment of 2,000. Variable marketing and administrative expenses total 6 per unit sold. All marketing and administrative expenses are paid as incurred. REQUIREMENT You have been asked to prepare a cash budget for the next four months to see if the loan can be repaid. Review the worksheet CASHBUD that follows these requirements. The problem data have already been entered in the Data Section of the worksheet.Open the file CASHBUD from the website for this book at cengagebrain.com. Enter the eight formulas where indicated on the worksheet. Enter your name in cell A1. Save the file as CASHBUD2. Print the worksheet when done. Also print your formulas. Check figure: Ending cash balance for April (cell E54), 7,772.Can the 10,000 note be repaid on May 1? Explain.4RThe following four suggestions have been made to improve the company’s cash position. Evaluate the effect on cash flow for each of the four suggestions. After evaluating each suggestion, enter the projected cash balances in the spaces provided. Consider each suggestion separately. Reset cells to their initial values after each new suggestion.
Seek agreement with suppliers to extend the credit period to 30 days. This would mean that all current monthly purchases would be paid for in the following month.
Raise the unit price from $28 to $30. A price increase will reduce unit sales by 10% each month. Unit purchases will also be reduced by 10%.
Put the company’s two salespeople on straight commission. This would reduce fixed marketing and administrative costs to $1,500 per month and raise variable marketing and administrative costs to $7 per unit.
Increase the cash discount from 5% to 10%. It is anticipated that this would increase the percentage of customers paying within the discount period to 85%, and those paying the month after the discount period would drop to 8%. Five percent would pay in the following month and 2% would still be uncollectible.
What are your recommendations for Sweet Pleasures, Inc.? Consider potential impact on profits as well as cash balances.
Reset cells to their initial values. Sweet Pleasures, Inc., is considering undertaking a 1,200 per month advertising campaign to promote Sweet Pleasures chocolate as exclusive and extraordinarily tasty. What price (dollars and cents) would Sweet Pleasures have to charge for each box to be able to pay for the campaign, pay back the 10,000 note, and have 15,000 left over at the end of April? (Try different prices in cell C9.) Sales price ___7RRanger Industries has provided the following information at June 30: Other information: Average selling price, 196 Average purchase price per unit, 110 Desired ending inventory, 40% of next months unit sales Collections from customers: In month of sale20% In month after sale50% Two months after sale30% Projected cash payments: Inventory purchases are paid for in the month following acquisition. Variable cash expenses, other than inventory, are equal to 25% of each months sales and are paid in the month of sale. Fixed cash expenses are 40,000 per month and are paid in the month incurred. Depreciation on equipment is 2,000 per month. REQUIREMENT You have been asked to prepare a master budget for the upcoming quarter (July, August, and September). The components of this budget are a monthly sales budget, a monthly purchases budget, a monthly cash budget, a forecasted income statement for the quarter, and a forecasted September 30 balance sheet. The worksheet MASTER has been provided to assist you. Ranger Industries desires to maintain a minimum cash balance of 8,000 at the end of each month. If this goal cannot be met, the company borrows the exact amount needed to reach its goal. If the company has a cash balance greater than 8,000 and also has loans payable outstanding, the amount in excess of 8,000 is paid to the bank. Annual interest of 18% is paid on a monthly basis on the outstanding balance.Open the file MASTER from the website for this book at cengagebrain.com. Enter all the formulas where indicated on the worksheet. Check to be sure that your balance sheet balances. Enter your name in cell A1. Save the completed file as MASTER2. Print the worksheet when done. Also print your formulas using fit-to-1 page scaling. Check figures: Borrowing (cell B78), $7,280; forecasted net income (cell D95), $40,200; total assets (cell D105), $507,600.
Review the completed master budget and answer the following questions:
Is Ranger Industries expecting to earn a profit during the next quarter? If so, how much?
Does the company need to borrow cash during the quarter? Can it make any repayments? Explain. (Carefully review rows 74 through 80.)
Suppose the company has to revise its estimates because of a downturn in the economy. Unit sales for August, September, and October will be half (50%) of the original estimates. Revise the estimates in cells 1311 through 1313. After this is done, check your forecasted balance sheet. It should still balance! What effect will this new state of affairs have on net income and borrowing? Explain why these items changed.Suppose the company has just the opposite news and now expects unit sales for August, September, and October to be double (200%) the original estimates. What effect will this have on the company’s net income and borrowing? Explain your findings.
6R1R2R3R4R5R6R1RThe president of McGrade Industries wants an analysis prepared to help explain why the variances computed in requirement 1 occurred. Using the worksheet called PRIMEVAR that follows these requirements, calculate the material and labor variances for McGrade Industries. The problem requires you to enter the input in the Data Section as well as formulas in the Answer Section.
3RThe worksheet you have developed will handle most simple variance analysis problems. Try the problem below for Pscheidl, Inc.: Actual production for October was 11,500 units. Compute the direct materials and direct labor variances for Pscheidl, Inc. Be careful when entering your input because this problem presents the information in a different format from the McGrade Industries data. Save the file as PRIMEVAR4. Print the worksheet when done.Close the PRIMEVAR4 file and open PRIMEVAR3. Click the Chart sheet tab. On the screen is a graphical representation of the variances computed in requirement 3. Review the chart and answer the following questions: a. Which variances does each bar represent? A __________ B __________ C __________ D __________ b. Which of the variances shown would be of most concern to management for immediate attention? (Consider groups of variances and materiality also.) Explain. When the assignment is complete, close the file without saving it again. Worksheet. McGrade Industries also has the following information regarding overhead for October: actual overhead 375,000, standard variable overhead of 3 per direct labor hour, and standard fixed overhead of 5 per direct labor hour (based on 47,000 hours budgeted). Modify the PRIMEVAR3 worksheet to compute all appropriate overhead variances. Preview the printout to make sure that the worksheet will print neatly on one page, and then print the worksheet. Save the completed file as PRIMEVART. Hint: Insert several new rows in the Data Section and in the Answer Section. Chart. Using the PRIMEVAR3 file, prepare a 3-D stacked bar chart to compare total standard cost per unit with actual cost per unit. Complete the Chart Tickler Data Table and use it as a basis for preparing the chart. You do not need to use cell references when completing the table. Enter all appropriate titles, legends, and formats. Enter your name somewhere on the chart. Save the file again as PRIMEVAR3. Print the chart.1R2R3R4R5R6R1R2R3R4R5RReset the Data Section of the CAPBUD2 worksheet to the original values. In requirement 4, you assessed the sensitivity of the investment’s internal rate of return to changes in some of the input data. This was done in a trial-and-error fashion. Click the Chart sheet tab. Presented on the screen is a graphical analysis of the sensitivity of the internal rate of return to changes in annual cash flows. To demonstrate the usefulness of such a chart, note the ease with which you are able to answer the following questions that might be of interest to the owner:
What annual cash flow (approximately) is required to:
earn 0% rate of return? _______________
earn 10% rate of return? _______________
earn over 20% rate of return? _______________
Approximately, how much is the rate of return reduced for each drop of $10,000 annual cash flow?
When the assignment is complete, close the file without saving it again.
Worksheet. The CAPBUD2 worksheet handles only cash inflows that are even in amount each year. Many capital projects generate uneven cash inflows. Suppose that the new store had expected cash earnings of $80,000 per year for the first two years, $140,000 for the next four years, and $220,000 for the last four years. The new store will generate the same total cash return ($1,600,000) as in the original problem, but the timing of the cash flows is different. Alter the CAPBUD2 worksheet so that the NPV and IRR calculations can be made whether there are even or uneven cash flows. When done, preview the printout to make sure that the worksheet will print neatly on one page, and then print the worksheet. Save the completed file as CAPBUDT.
Hint: One suggestion is to label column F in the scratch pad as Uneven cash flows.
Enter the uneven cash flows for each year. Modify FORMULA3 to include these cash flows. Modify the formulas in the range E30 to E39 to include the new data. Then set cell E10 (estimated Annual Net Cash Inflow) to zero. When you have even cash flows, use cell E10 and set column F in the scratch pad to zeros. If you have uneven cash flows, set cell E10 to zero and fill in column F in the scratch pad.
Note that this solution causes garbage to come out in cells E15 and E16 because those formulas were not altered. Check figure for uneven cash flows: NPV (cell E17), $68,674.
Chart. Using the CAPBUD2 file, develop a chart just like the one used in requirement 6 to show the sensitivity of net present value to changes in cost of the investment amount from $440,000 to $500,000 (use $10,000 increments). Complete the Chart Tickler Data Table and use it as a basis for preparing the chart. Enter your name somewhere on the chart. Save the file again as CAPBUD2. Print the chart.
1R2R1E2E3E4E5E6E7E8E9E10E1R2R3R4RThe general ledger of Jay Consulting shows the following balances at July 31: Jay has asked you to develop a worksheet that will serve as a trial balance (file name PTB). Use the data provided as input for your model. Review the Model-Building Problem Checklist on page 154 to ensure that your worksheet is complete. Print the worksheet when done. Check figure: Total debits, 17,731. To test your model, use the following balances at August 31: Print the worksheet when done. Check figure: Total debits, 18,810. CHART (optional) Using the test data worksheet, prepare a pie chart showing the percentage of each asset to total assets. Print the chart when done.2M3M4M5M6M7M8M9M10M11M12M13M14M15M16M17M18M19M20M21M22M23M24M25M26M27M28M29M1C2C3C
Page: [1]