Instructions
Please complete the following:
Comprehensive Problem Instructions
1. You will find Comprehensive Problem #2 at the end of Chapter 8 in your text (after all of the exercises and problems)
2. Download the template provided for this assignment. Please note, you must use the templates provide. Do not create your own Excel sheets. Please use provided excel template
3. At the bottom of the Excel template, you will see different sheets (tabs) that need completing. Each sheet shows a different part of the problem you must complete.
4. When completing the problems you must show the Function used in Excel. Excel Functions are predefined formulas within Excel. In other words, you cannot just put the numeric answer in a cell. You must instruct Excel to do the specific computation you would like. This is required; otherwise, credit for your work will not be given. An example, the function =SUM(A1:A3, C1:C2, E2) will add the values of all cells.
5. Complete and submit your solution as one file attachment.
6. VERY IMPORTANT All Parts are to be completed using the provided Excel Template
7. All supporting calculations are to be shown.
8. Make sure you save your work while completing this problem.
9. There are several parts to these comprehensive problems, so please allow enough time to complete it and review it before handing it in.
10. Title the finalized file as "Comprehensive Problem
Check Figures:
Check figures are included as Notes within the provided Excel Template file, provided in the Module.
COMPREHENSIVE PROBLEM 2
Music-Is-Us, Inc.
Music-Is-Us, Inc., is a supplier of musical instruments for professional and amateur musicians. The company’s accountants make adjusting entries monthly, and they make all closing entries annually. The company is growing rapidly and prides itself on having no long-term liabilities.
The company has provided the following trial balance dated December 31, 2021.
MUSIC-IS-US, INC.
TRIAL BALANCE
DECEMBER 31, 2021
Cash $............................................................. 45,000
Marketable securities………………………… 25,000
Accounts receivable …………………………..125,000
Allowance for doubtful accounts…………………………….$ 5,000
Merchandise inventory………………………..250,000
Office supplies…………………………………….1,200
Prepaid insurance………………...………………6,600
Building and fixtures………………………..1,791,000
Accumulated depreciation………………………………………..800,000
Land……………………………………………….64,800
Accounts payable……………………………………………………70,000
Unearned customer deposits……………………………………….8,000
Income taxes payable……………………………………………….75,000
Capital stock………………………………………………………1,000,000
Retained earnings………………………………………………….240,200
Unrealized holding gain on investments………………………….6,000
Sales………………………………………………………………..1,600,000
Cost of goods sold……………………………958,000
Bank service charges……………………………..200
Uncollectible accounts expense………………9,000
Salary and wages expense…………………..395,000
Office supplies expense…………………….…….400
Insurance expense………………………………6,400
Utilities expense…………………………………3,600
Depreciation expense…………………………48,000
Income tax expense……………………………75,000
$3,804,200 $3,804,200
Other information pertaining to the company’s trial balance is provided as follows.
1. The most recent bank statement reports a balance of $46,975. Included with the bank statement was a $2,500 check from Iggy Smarts, a professional musician, charged back to Music-Is-Us as NSF. The bank’s monthly service charge was $25. Three checks written by Music-Is-Us to suppliers of merchandise inventory had not yet cleared the bank for payment as of the statement date. These checks included: no. 508, $5,500; no. 511, $7,500; and no. 521, $8,000. Deposits of $16,500 reached the bank too late for inclusion in the cu
ent bank statement. The company prepares a bank reconciliation at the end of each month.
2. Music-Is-Us has a portfolio of marketable securities that originally cost $19,000. As of December 31, the market value of these securities was $27,500. All short-term investments are classified as “available for sale.”
3. During December, $6,400 of accounts receivable were written off as uncollectible. A recent aging of the company’s accounts receivable led management to conclude that an allowance for doubtful accounts of $8,500 is needed at December 31, 2021.
4. The company uses a perpetual inventory system. A year-end physical count revealed that several guitars reported in the inventory records were missing. The cost of the missing units amounted to $1,350. This amount is not considered significant relative to the total cost of inventory on hand.
5. At December 31, approximately $900 in office supplies remained on hand.
6. The company pays for its insurance policies 12 months in advance. Its most recent payment was made on November 1, 2021. The cost of this policy was slightly higher than the cost of coverage for the previous 12 months.
7. Depreciation expense related to the company’s building and fixtures is $5,000 for the month ending December 31, 2021.
8. Although Music-Is-Us ca
ies an extensive inventory, it is not uncommon for experienced musicians to order custom guitars made to their exact specifications. Manufacturers do not allow any sales returns of custom-made guitars. The entire sales amount is collected at the time a custom order is placed, and is credited to an account entitled “Unearned Customer Deposits.” As of December 31, $4,800 of these deposits remained unfilled because the special-order guitars have not been received from the manufacturer. The cost of goods sold and the reduction in inventory associated with all custom orders is recorded when the custom merchandise is delivered to customers. At that time, the adjusting entry requires only a decrease to unearned customer deposits and an increase in sales.
9. Accrued income taxes payable for the entire year ending December 31, 2021, total $81,000. No income tax payments are due until early in 2022.
Instructions:
a. Prepare a bank reconciliation and make the journal entries to update the accounting records of Music-Is-Us as of December 31, 2021.
. Prepare the adjusting entry to update the company’s marketable securities portfolio to its mark-to-market value.
c. Prepare the adjusting entry at December 31, 2021, to report the company’s accounts receivable at their net realizable value (i.e., total amount receivable, less estimated allowance for uncollectible accounts).
d. Prepare the entry to account for the guitars missing from the company’s inventory at the end of the year.
e. Prepare the adjusting entry to account for the office supplies used during December.
f. Prepare the adjusting entry to account for the expiration of the company’s insurance policies during December.
g. Prepare the adjusting entry to account for the depreciation of the company’s building and fixtures during December.
h. Prepare the adjusting entry to report the portion of unearned customer deposits that were earned during December.
i. Prepare the adjusting entry to account for income tax expense that accrued during December.
j. On the basis of the adjustments made to the accounting records in parts a through i, prepare the company’s adjusted trial balance at December 31, 2021.
k. Using the adjusted trial balance prepared in part j, prepare an annual income statement, statement of retained earnings, and a balance sheet dated December 31, 2021.
l. Using the financial statements prepared in part k, determine approximately how many days on average an account receivable remains outstanding before it is collected. You may assume that page 389the company’s ending accounts receivable balance on December 31 is a close approximation of its average accounts receivable balance throughout the year.
m. Using the financial statements prepared in part k, determine approximately how many days on average an item of merchandise remains in stock before it is sold. You may assume that the company’s ending merchandise inventory balance on December 31 is a close approximation of its average merchandise inventory balance throughout the year.
n. Using the financial statements prepared in part k, determine approximately how many days it takes to convert the company’s inventory into cash. In other words, what is the length of the company’s operating cycle?
o. Comment
iefly upon the company’s financial condition from the perspective of a short-term creditor.
CP2-Desc.
COMPREHENSIVE PROBLEM 2
Music-Is-Us, Inc.
A mini-practice set illustrating numerous aspects of the accounting cycle for a merchandising business organized as a corporation. Students are expected to: (1) prepare a bank reconciliation, (2) make adjusting entries—including adjustments related to marketable securities, uncollectible accounts, inventory shrinkage, and depreciation, (3) prepare an income statement, a statement of retained earnings, and a balance sheet, and (4) assess the financial condition of the business from a short-term creditor’s perspective. 2 to 3 hours
Strong
&"Arial,Italic"&8&K000000Copyright © 2018 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
BankReconciliation-CP2
2 to 3 hours, Strong COMPREHENSIVE PROBLEM 2
a.
MUSIC-IS-US
Bank Reconciliation
December 31, 2018
General Journal
a.
b.
c.
d.
e.
&"Arial,Italic"&8&K000000Copyright © 2018 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
Journal-CP2(p.2)
COMPREHENSIVE PROBLEM 2
General Journal
f.
g.
h.
i.
&"Arial,Italic"&8&K000000Copyright © 2018 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
AdjTB-CP2(p.3)
COMPREHENSIVE PROBLEM 2
j.
MUSIC-IS-US
Adjusted Trial Balance
As of December 31, 2018
Cash
Marketable securities
Accounts receivable
Allowance for doubtful accounts
Merchandise inventory
Office supplies
Prepaid insurance
Building and fixtures
Accumulated depreciation
Land
Accounts payable
Unearned customer deposits
Income taxes payable
Capital stock
Retained earnings
Unrealized holding gain on investments
Sales
Cost of goods sold
Bank service charges
Uncollectible accounts expense
Salary and wages expense
Office supplies expense
Insurance expense
Utilities expense
Depreciation expense
Income tax expense
&"Arial,Italic"&8&K000000Copyright © 2018 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
IncomeStatement-CP2(p.4)
COMPREHENSIVE PROBLEM 2
MUSIC-IS-US (continued)
k.
MUSIC-IS-US
Income Statement
For the Year Ended December 31, 2018
MUSIC-IS-US
Statement of Retained Earnings
For the Year Ending December 31, 2018
&"Arial,Italic"&8&K000000Copyright © 2018 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
BalanceSHeet-CP2(p.5)
COMPREHENSIVE PROBLEM 2
k. (continued)
MUSIC-IS-US
Balance Sheet
As of December 31, 2018
&"Arial,Italic"&8&K000000Copyright © 2018 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
Finising-Ratios-CP2(P.6)
COMPREHENSIVE PROBLEM 2
l.
m.
n.
o.
&"Arial,Italic"&8&K000000Copyright © 2018 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
CP2-Desc.
COMPREHENSIVE PROBLEM 2
Music-Is-Us, Inc.
A mini-practice set illustrating numerous aspects of the accounting cycle for a merchandising business organized as a corporation. Students are expected to: (1) prepare a bank reconciliation, (2) make adjusting entries—including adjustments related to marketable securities, uncollectible accounts, inventory shrinkage, and depreciation, (3) prepare an income statement, a statement of retained earnings, and a balance sheet, and (4) assess the financial condition of the business from a short-term creditor’s perspective. 2 to 3 hours
Strong
&"Arial,Italic"&8&K000000Copyright © 2018 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
BankReconciliation-CP2
2 to 3 hours, Strong COMPREHENSIVE PROBLEM 2
a.
MUSIC-IS-US
Bank Reconciliation
December 31, 2018
General Journal
a.
b.
c.
d.
e.
&"Arial,Italic"&8&K000000Copyright © 2018 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
Journal-CP2(p.2)
COMPREHENSIVE PROBLEM 2
General Journal
f.
g.
h.
i.
&"Arial,Italic"&8&K000000Copyright © 2018 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
AdjTB-CP2(p.3)
COMPREHENSIVE PROBLEM 2
j.
MUSIC-IS-US
Adjusted Trial Balance
As of December 31, 2018
Cash
Marketable securities