Accounting 3401 Excel Project: Please Solve

ACCTNG3401 Excel Project Must be submitted online via Canvas Assignment by 11:59pm on the due date. 0. Bad debt for Accounts Receivable: DTS management does not expect there to be any change in the collectability of its credit sales related to its regular restaurant equipment operations. DTS uses the Accounts Receivable approach to estimate bad debts, and the aging schedule at 12/31/2019 is summarized below: Days outstanding % of Accts Rec Estimated % Uncollectible 0-30 days 58% 0.50% 30-60 days 26% 1.50% 60-90 days 14% 10.00% >90 days 2% 40.00% In addition to its physical therapy equipment products, DTS's management entered into a long-term agreement on September 1, 2019 to begin supplying its internally developed web-based commercial therapy tracking software, PROGRESS- TRACKER, along with maintenance support, to a regional chain of therapy centers. The details of the agreement called for DTS to be paid $3,600,000 up front for the software and 3 years of maintenance support (beginning on the agreement date). The therapy center chain could have bought just the software for $2,870,300 with no support, and they could have independently contracted for the maintenance support for $1,116,200 for the three-year period. DTS determines the sales price, and directs the customization of the software. The cost of the software sold to the therapy center chain was $2,160,000. DTS considers this software part of its normal operations, and has recorded this sale as a point-ofsale transaction. In October, 2019 DTS also launched a new service through its website (called PT CONT ED on the trial balance worksheet). PT CONT ED offers online continuing education to Physical Therapist and Assistant PT's through contracted independent instructors. Each instructor sets her own price for a training session. DTS connects interested individuals with the instructors but does not directly provide any of the training. For its services and per the contract, DTS is entitled to 20% of the total per session fee. DTS also considers this service part of its normal operations. DTS recorded the entire amount of the fee collected as revenue and recognized its cost associated with PT CONT ED as 80% of the fee. Additional Information: f. Finally, in addition to its normal operations, DTS also has launched a division that builds physical therapy centers. At the end of 2018, there was one building project in process: the $3,800,000 Bradshaw Clinic (BC) projectia state-of-theart physical therapy facility. This project, which was started in June, 2018, qualies as a single performance obligation with revenue recognized over the period of the project according to the percentage-ofcompletion method based on a cost-to-cost approach. The original estimated cost for the PC project was $2,720,800 at the end of 201 8. But there have been substantial increases in the cost of materials resulting, as of the end of 2019, in a total actual and estimated cost of $3,040,000. The contract does not allow for renegotiation of the contracted price. DTS has a legally binding, enforceable contract with the customer and all parties are expected to be able to perform under the Page 2 of 3 File Home Insert Draw Page Layout Formulas Data Review View Help l3 Share D Comments [an A v A A" E E \"'7' ab) Wrap Text Number v w E in a Insert V Z v A? p ; gg Paste 0, A l' _ _ o (0 00 Conditional Forrgas Cell? 1X Delete V 'L v sin 81 Find BL Ideas Ser'vity v V v : : : ' b _ 5': Merge 8' center V $ V A) , ' ' 7 Formatting v Table v Styles v H Format " 0 v Filter v Select v v Clipboard IE Font I Alignment I Number Ii Styles Cells Editing Ideas Sensitivity A :6 v x ~/ fx 8960000 v 4 Bll D IEI F I G |H| I l J M L I M M o l p L Unadjusted Trial Bal. 12/31 Adjusting Journal Entries Adjusted Trial Bal. 12/31 Closing Journal Entries Final Trial Balance 12/31 2 , Debit Credit Debit Credit Debit Credit Debit Credit Debit Credit E Cash 4,254,800 4,254,800 4,254,800 L Accounts Receivable 3,794,500 5 Allowance for Bad Debt 14,400 mm L Purchases 51,539,200 L Construction in Progress 3,039,240 9_ Billings on Contract 3,078,000 Property, Plant 8t Equipment 10,080,000 lAccumulated Depreciation 4,032,000 EAccounts Payable 429 E Income Tax Payable 35 1 Common Stock 500 E Retained Earnings 26,019 E Product Sales Revenue 49,328 [Sales Returns 690,600 PT Cont Ed Revenue 1,560,100 Progress-Tracker Revenue 3,600,000 Cost of Product Sales 0 Cost of PT Cont Ed Services 1,248,100 Cost of Progress-Tracker Services 2,160,000 General and Admin Expense 2,830,000 Cost of Goods Sold 578,259,200 Insert more account rows if necessary TOTAL 666,855,640 88,596,440 \"WIN (9 ACCTNG-3401 Excel Project Must be submitted online via Canvas Assigmnent by 1 1:59pm on the due date. It is January 2020 and you have just been hired as the Controller for Delta Therapy Supply (DTS), a corporation that began as a distributor of general purpose physical therapy equipment for hospitals and physical therapy centers. DTS has just nished an eventful year in which it launched a new software product and a new training service. DTS also entered into a therapy center construction venture. Your predecessor left the rm in a hurry. Your primary responsibility is to nish the 2019 year-end nancial statements. Specically, you must complete: 1) any necessary correcting journal entries 2) all the adjusting journal entries 3) the trial balances 4) the closing journal entries 5) a complete multi-step Income Statement for the year ended Dec. 31, 2019 6) a classied Balance Sheet as of December 31, 2019. You, of course, will include supporting documentation, including a brief description of the issue. For each issue, you must provide: 0 a brief description of the issue and your actionithat is, identify whether: 1) if an incorrect entry was made, what was the incorrect entry, why was it incorrect, and how did you correct it; or 2) if a normal adjusting entry is needed, why the adjustment is needed; or 3) if no change or adjustment is needed 0 detailed calculations to support any values you use in your journal entries The Unadjusted Trial Balance has been prepared (it is included in the \"3401 Excel Project workbookxlsx\"), showing only those accounts with a nonzero balance. You have gathered the following information that will be helpful in preparing any necessary journal entries (add any accounts necessary). Good luck! Additional Information About Normal Operations: a. Cost of Goods Sold: DTS uses a periodic FIFO inventory system for its normal restaurant equipment operations. A physical inventory count indicated 40,000 units on hand at the end of 2019. PURCHASES FOR 2019 (normal operations) Beginning units: 14,000 units @ $640.00 each Purchases: Apr - May 15,000 units @ $646.40 each Jun - Ju1 20,000 units @ $649.60 each Aug - Sep 18,000 units @ $652.80 each Oct 12,000 units @ $656.00 each Nov - Dec 14,000 units @ $659.20 each b. DTS uses straight-line depreciation and all xed assets were purchased at the beginning of 2017 and have a 5year useful life. No depreciation entries have been recorded yet during 2019. Page 1 of 3 ACCTNG3401 Excel Project Must be submitted online via Canvas Assignment by 11:59pm on the due date. contract. The general ledger accounts Construction-in-Progress and Billingson- Contract show the following before any 2019 year-end adjusting entries. Construction-in-Progress Billings-on-Contract 2013 cost 1,224,360 1,414,360 2013 2018 AJE 485.640 2019 cost 1,329,240 1,563,640 2019 Unadj. 3,039,240 3,078,000 Unadj. g. DTS's effective tax rate is 25%. Ignore all other taxes. h. There are 400,000 shares of common stock outstanding for 2019. Note: You must format your Excel solution so that I can print \"entire workbook\" and display your name in the header and follow your solution without dangling blank pages