please show calculations, thank you
The following specifies the requirements for Case Assignment 91. This assignment using Excel must be submitted to the Pilot drop box at the beginning of the class period (11:00 am) on Tuesday, March 23. Any assignment submitted late will incur a substantial penalty Each case submitted must be each student's own work collaboration among students is not permitted (see the course syllabus for a further description of the Academic Dishonesty policy). Each case solution must prepared using Excel. You must submit your solution in an Excel spreadsheet to the Pilot drop box which includes all supporting calculations performed with appropriate Excel formulas. Failure to do so will result in a reduction in points. Be sure to format your spreadsheet so that your work is clearly presented and easy to review. Troy Supply Corporation Troy Supply Corporation is a privately held company operating in Ohio since 1983. Troy sells to all of its customers on a credit basis, requiring payment within 30 days. Troy uses the allowance method to estimate the amount currently uncollectible for its accounts receivable. During 2020. Troy recorded a monthly provision of 1% of credit sales of as an estimate for uncollectible accounts receivable. However, at year-end, an aging of accounts receivable is prepared and the allowance for uncollectible accounts is adjusted based on an analysis of that aging At December 31. 2019, the adjusted balance of the allowance for uncollectible accounts was $21.300, and the balance of accounts receivable was $140.700. During 2020, Troy wrote-off 12.100 of customer accounts that were deemed to be uncollectible due to customers declaring bankruptcy or experiencing financial difficulties so severe that extensive collection efforts were not successful. One customer's account with a $4.100 balance. which had been written-off in April 2019, was subsequently collected from the customer in November 2020 Troy maintained the same monthly provision of 1% of credit sales throughout all of 2020. Monthly credit sales for 2020 are as follows: January $ 38,800 February 44.700 March 27,600 April 20.000 May 23.700 June 32.000 July 49.400 August 46.100 September 43.900 October 41.400 November 42.100 December 40.300 Total cash collections of accounts receivable during 2020 (not including the collection of the previously written-off account) were $430,600 In preparation for its year-end closing process. Troy's controller prepared the following aging of accounts receivable as of December 31, 2020. assigning probabilities of collection based on discussions with Troy's credit manager: Age of Account Receivable 0-30 days past due 31-60 days past due 61-90 days past due Greater than 90 days past due Percentage of Accounts Receivable Probability of Collection 70% 95% 15% 70% 10% 50% 5% 10% Requirements a) Prepare an analysis computing the unadjusted balance in the allowance for uncollectible accounts as of 12/31/20. b) Prepare the year-end adjusting journal entry to record bad debt expense based on the December 31, 2020 aging of accounts receivable
