You are a junior accountant of Titanic Bike Parts Limited (TBL) which purchases and resells bike...

Transcribed Image Text:

You are a junior accountant of Titanic Bike Parts Limited (TBL) which purchases and resells bike parts to other bike manufacturers. TBL started operations on January 1, 2020. You have been tasked in analyzing the company's accounts receivable. You have been given other three worksheets, Sales Data, Inventory Data, and Aging Group, in the downloaded Excel file: Sales Data - this worksheet includes all the sales data up to December 31, 2021 pulled from the sales system that TBL utilizes. Each inventory item sold has a unique sales ID and item ID assigned to it, even if multiple inventory items are sold within a bulk sale (ie., on same date and to same customer). This means that each row in this worksheet represents a sale of a unique inventory item. All sales are made on account to customers with a term of 2/10, n/30. If a customer have paid in full for a sale, the payment date is provided in this worksheet. The payment date is left blank if the payment is to be collected after December 31, 2021, as this worksheet is prepared on December 31, 2021. Inventory Data - this worksheet provides the bike part description of an item ID used in the Sales Data worksheet. Aging Group - this worksheet includes aging groups and estimated bad debt percentages TBL uses for estimating bad debt expenses. Required: 1. Calculate the balance of Accounts Receivable at December 31, 2020 and December 31, 2021 (round to the nearest whole number). Tips: If a sale occurs before or in 2020 but the payment is collected after 2020, the amount of the sale will appear in Accounts Receivable at 2020-12-31. Similarly, if a sale occurs before or in 2021 but the payment is collected after 2021, the amount of the sale will appear in Accounts Receivable at 2021-12-31. So, the IF function will be used. Please check this link for more information on the IF function. Answer is complete but not entirely correct. A/R balance at December 31, 2020 A/R balance at December 31, 2021 2 20 2. Use the aging method to determine the balance of Allowance for Doubtful Accounts at December 31, 2021 (round to the nearest whole number). Tips: For this requirement, you need to determine each Account Receivable's aging category based on its outstanding days. You can use either the IFS function or multiple nested IF statements. The IFS function is much easier to read with multiple conditions. Please check this link for more information on the IFS function. Please check this link for more information on the IF function with nested formulas. To obtain the subtotal of each aging category, the SUMIF function is handy. Please check this link for more information on the SUMIF function. Allowance for Doubtful Accounts balance at December 31, 2021 3. Assume that the balance of Allowance for Doubtful Accounts at January 1, 2021 was $100,000 and that there were no write-off and recovery during 2021. Determine the amount of bad debt expense for the year of 2021 (round to the nearest whole number). Bad debt expense for 2021 4. Which customer contributed the most sales in 2021? Please enter the number assigned to this customer. What is the amount of sales contributed by this customer (round to the nearest whole number)? Customer Bikes for Likes Crestfallen Horshoe Incorporated Motorcross Events OffRoad Vehicles Riders Ltd. Wheels on Coast X Games Assigned Number 1 Please enter the number assigned to this customer. Please enter the amount of sales contributed by this customer. 2 3 4 5 6 7 8 5. Which bike part contributed the most sales in 2021? Please enter the number assigned to this bike part. What is the amount of sales contributed by this bike part (round to the nearest whole number)? Bike Part Brakes Engine Radio Seats Tire Assigned Number 1 2 3 4 5 Please enter the number assigned to this bike part. Please enter the amount of sales contributed by this bike part. 7. The balance of Accounts Receivable at December 31, 2021 can be broken down to many smaller balances due from various customers for various inventory items. Each smaller balance corresponds to a specific row in the Sales Data worksheet and has two attributes: the dollar amount and the number of days passed from the sale date. In the following chart, these smaller balance are represented by scattered dots. Dollar Amount 0 80,000 100,000 20,000 40,000 60,000 0 : 50 100 3 150 Number of Days from Sales Date to 2021-12-31 200 If you are concerned about the collectability of Accounts Receivable, which quadrant of the above chart is worth the most collection effort? Quadrant 1 Quadrant 2 Quadrant 3 Quadrant 4 You are a junior accountant of Titanic Bike Parts Limited (TBL) which purchases and resells bike parts to other bike manufacturers. TBL started operations on January 1, 2020. You have been tasked in analyzing the company's accounts receivable. You have been given other three worksheets, Sales Data, Inventory Data, and Aging Group, in the downloaded Excel file: Sales Data - this worksheet includes all the sales data up to December 31, 2021 pulled from the sales system that TBL utilizes. Each inventory item sold has a unique sales ID and item ID assigned to it, even if multiple inventory items are sold within a bulk sale (ie., on same date and to same customer). This means that each row in this worksheet represents a sale of a unique inventory item. All sales are made on account to customers with a term of 2/10, n/30. If a customer have paid in full for a sale, the payment date is provided in this worksheet. The payment date is left blank if the payment is to be collected after December 31, 2021, as this worksheet is prepared on December 31, 2021. Inventory Data - this worksheet provides the bike part description of an item ID used in the Sales Data worksheet. Aging Group - this worksheet includes aging groups and estimated bad debt percentages TBL uses for estimating bad debt expenses. Required: 1. Calculate the balance of Accounts Receivable at December 31, 2020 and December 31, 2021 (round to the nearest whole number). Tips: If a sale occurs before or in 2020 but the payment is collected after 2020, the amount of the sale will appear in Accounts Receivable at 2020-12-31. Similarly, if a sale occurs before or in 2021 but the payment is collected after 2021, the amount of the sale will appear in Accounts Receivable at 2021-12-31. So, the IF function will be used. Please check this link for more information on the IF function. Answer is complete but not entirely correct. A/R balance at December 31, 2020 A/R balance at December 31, 2021 2 20 2. Use the aging method to determine the balance of Allowance for Doubtful Accounts at December 31, 2021 (round to the nearest whole number). Tips: For this requirement, you need to determine each Account Receivable's aging category based on its outstanding days. You can use either the IFS function or multiple nested IF statements. The IFS function is much easier to read with multiple conditions. Please check this link for more information on the IFS function. Please check this link for more information on the IF function with nested formulas. To obtain the subtotal of each aging category, the SUMIF function is handy. Please check this link for more information on the SUMIF function. Allowance for Doubtful Accounts balance at December 31, 2021 3. Assume that the balance of Allowance for Doubtful Accounts at January 1, 2021 was $100,000 and that there were no write-off and recovery during 2021. Determine the amount of bad debt expense for the year of 2021 (round to the nearest whole number). Bad debt expense for 2021 4. Which customer contributed the most sales in 2021? Please enter the number assigned to this customer. What is the amount of sales contributed by this customer (round to the nearest whole number)? Customer Bikes for Likes Crestfallen Horshoe Incorporated Motorcross Events OffRoad Vehicles Riders Ltd. Wheels on Coast X Games Assigned Number 1 Please enter the number assigned to this customer. Please enter the amount of sales contributed by this customer. 2 3 4 5 6 7 8 5. Which bike part contributed the most sales in 2021? Please enter the number assigned to this bike part. What is the amount of sales contributed by this bike part (round to the nearest whole number)? Bike Part Brakes Engine Radio Seats Tire Assigned Number 1 2 3 4 5 Please enter the number assigned to this bike part. Please enter the amount of sales contributed by this bike part. 7. The balance of Accounts Receivable at December 31, 2021 can be broken down to many smaller balances due from various customers for various inventory items. Each smaller balance corresponds to a specific row in the Sales Data worksheet and has two attributes: the dollar amount and the number of days passed from the sale date. In the following chart, these smaller balance are represented by scattered dots. Dollar Amount 0 80,000 100,000 20,000 40,000 60,000 0 : 50 100 3 150 Number of Days from Sales Date to 2021-12-31 200 If you are concerned about the collectability of Accounts Receivable, which quadrant of the above chart is worth the most collection effort? Quadrant 1 Quadrant 2 Quadrant 3 Quadrant 4