Homework 4 Download the "Homework 4 Data" spreadsheet data. For the following tasks show your work in details (submit with excel file), so instructor can understand what steps of data manipulation you performed. If necessary, leave comments in the worksheet. The suggested templates are provided in the data file. Requires Part for all Students You are auditing the small bakery as the owner is applying for a loan to be used for a business expansion. As a part of auditing the revenue and collection cycle, you decided to run few analytical procedures. 1. Carefully learn structure of the provided data, try to deduce insights about accounting and recordkeeping practices of the client on revenues and receivables. Answer following questions: a. What is the product price per unit? Is the per unit price same for all product types. Explain your reasoning. b. Based on what, the delivery fee is charged? What is the delivery fee rate? Are there any exceptions on delivery fees? If yes what might be a possible reason for that? Show your reasoning. c. On the shipping report, how many Shipping #s do you find for each Sales Order #. Why do you think that is the case? 2. Perform a three-way match between sales orders, shipping report and sales invoice (compare dollar amount and quantity) for the year of 2021. If you find any problems, explore the problem and see if you can determine what happened to cause the problem. 3. If auditor expect the possible existence of following circumstances, explain what special considerations should be taken into account while performing a three-way match: a. Split or partial Invoices/Payments b. Incomplete DeliveriesA B C D E F G CORPORATE CUSTOMER LIST Customer 2 Customer # since Name Address City State Zip 3 C1001 6/14/2010 Alpine Cupcakes Storefront 1000 16th Street Denver CC 802 4 C1106 9/18/2018 Bon Appetito Restaurants 4990 Washington Denver CO 8020 5 C1031 3/23/2016 Boulder Tea house 1700 13th St Boulder CO 8030 6 C1062 5/9/2017 Broken Eggs Restaurant 2400 S. Parker Road Denver CO 802 C1147 2/9/2020 Brown's Tavern 950 Lincoln St Denver CO 802 8 C1111 12/17/2018 Bubba's Fish House 5050 E 28th St Denver CO 8020 9 C1071 7/20/2017 Buckhead Restaurants 2700 Welton Street Denver CO 802 10 C1057 2/16/2017 Chavez Cantina 2351 Champak Street Denver CO 802 11 C1016 9/23/2015 Country Barrel Restaurants 1400 Poplar Street Denver CO 8022 12 C1083 11/6/2017 Denver Bakery Cafe 321 17th Street Denver CO 802 13 C1076 8/12/2017 Denver Sirloin Restaurants 320 E Colfax Avenue Denver CO 802 14 C1096 5/22/2018 Elkhorn Coffee Shops 400 S Broadway Denver CO 802 15 C1052 11/27/2016 Fontana Catering & Cafe 1776 5th St Denver CO 802 16 C1105 6/10/2018 Granny's Cafe 4902 E. Colfax Avenue Denver CO 802 17 C1089 5/7/2018 High Country Coffee 3900 W 32nd Street Denver CO 8021 18 C1002 5/1/2015 Julie's Wraps 1300 S Clarkston Denver CO 802 19 C1084 12/30/2017 Little's Grill 3600 S Monaco Parkway Denver CO 802 20 C1021 11/12/2015 Luigi's Bistro 1500 Arapahoe St Denver CO 802 21 C1044 9/27/2016 Mile High Steakhouses 1730 19th Street Denver CO 8020 22 C1086 2/8/2018 Mountain Lion Restaurant 37000 Boulder Canyon Dr Boulder co 8030 23 C1114 3/29/2019 Mountain Trout Fish House 640 30th Street Boulder Co 803 24 C1043 9/6/2016 Nora's Cafe 1700 Federal Blvd Denver CO 8020 Sales Orders Invoices Shipping Rep AR Journal Customer List Customer Cre +D E F G H K M N O Sales Orders Car Gluten- S Almo Total rot free Red pi nd # 12 Ca Choco Chocolat Lemo Velve C Vani Vega order Total $ ke late e F n t F e lla n ed Product - |$ Delivery F Order 3 55 93 45 66 52 50 139 37 537 $1,288.80 $8.95 $1,297.75 22 82 37 44 51 31 107 17 391 $938.40 $6.52 $944.92 5 355 793 145 466 352 440 139 72 2762 $6,628.80 $46.03 $6,674.83 6 51 89 41 76 55 45 116 15 488 $1,171.20 $8.13 $1,179.33 7 43 188 43 52 61 44 196 40 667 $1,600.80 $11.12 $1,611.92 8 128 274 55 126 77 82 402 45 1189 $2,853.60 $19.82 $2,873.42 9 102 180 85 152 89 80 207 22 917 $2,200.80 $15.28 $2,216.08 10 40 575 152 389 320 333 406 35 2250 $5,400.00 $37.50 $5,437.50 11 82 181 47 92 55 57 182 45 741 $1,778.40 $12.35 $1,790.75 12 482 881 347 492 255 357 782 71 3667 $8,800.80 $61.12 $8,861.92 13 152 223 55 127 60 59 263 32 971 $2,330.40 $16.18 $2,346.58 14 112 158 77 113 121 103 320 30 1034 $2,481.60 $17.23 $2,498.83 15 58 102 46 85 77 60 145 20 593 $1,423.20 $9.88 $1,433.08 16 9 17 9 7 9 18 31 6 106 $254.40 $1.77 $256.17 17 11 81 44 78 85 50 205 15 569 $1,365.60 $9.48 $1,375.08 18 53 95 80 174 89 107 216 48 862 $2,068.80 $14.37 $2,083.17 19 102 193 65 127 60 59 197 32 825 $1,980.00 $13.75 $1,993.75 20 12 22 17 15 21 13 24 7 131 $314.40 $2.18 $316.58 21 96 125 77 113 90 98 188 30 817 $1,960.80 $13.62 $1,974.42 22 15 30 21 15 14 15 35 7 152 $364.80 $2.53 $367.33 Sales Orders Invoices Shipping Rep AR Journal Customer List Customer Cre +A B C D E G H K Alpine Cupcakes, Inc., Shipping Reports Custom er PO # (B is Ship Sales Ship Blanket Carro Red ping Orde ping Customer PO for Chocolat Gluten-free Lemo Velve Van # r # Date year) Cake e Chocolate n t Spice a 3 SH985432 SO67904 12/1/20 C1031 551B 3 20 11 20 21 13 4 SH985433 S067901 12/1/20 C1071 1305B 28 40 19 28 30 26 5 SH985434 SO67890 12/1/20 C1002 7537B 14 23 11 17 13 13 6 SH985435 S067903 12/1/20 C1137 1008B 2 4 2 2 2 5 SH985436 S067919 12/1/20 C1045 4899B 6 19 9 12 5 6 8 SH985437 SO67907 12/2/20 C1062 357B 3 6 4 4 5 3 9 SH985438 SO67900 12/2/20 C1057 1970B 38 56 14 32 15 15 10 SH985439 SO67915 12/2/20 C1052 4431B 31 69 21 37 25 22 11 SH985440 SO67909 12/2/20 C1084 302B 4 8 5 4 4 4 12 SH985441 S067913 12/2/20 C1114 705B 14 52 14 16 22 14 13 SH985442 SO67905 12/2/20 C1043 843B 13 24 20 44 22 27 14 SH985443 S067894 12/2/20 C1113 1158B 11 47 11 13 15 11 15 SH985444 S067896 12/2/20 C1138 3579B 26 45 21 38 22 20 16 SH985445 S067918 12/3/20 C1147 804B 21 25 13 25 13 20 17 SH985446 SO67897 12/3/20 C1016 478B 10 144 38 97 80 83 18 SH985447 SO67911 12/3/20 C1105 6401B 29 42 21 44 11 23 19 SH985448 SO67898 12/3/20 C1021 488B 21 45 12 23 14 14 20 SH985449 SO67895 12/3/20 C1122 1234B 32 69 14 32 19 21 21 17002 Sales Orders Invoices Shipping Report AR Journal Customer List Customer Cre +Accounts Receivable Journal, Alpine Cupcakes Inc. AR Created Sales Date Customer #1 Order # Date Due Date Paid 12/31/20 $067890 $1,288.80 1/30/2021 1/25/2021 12/31/20 $067890 $8.95 1/30/2021 1/29/2021 12/31/20 $067892 $6,628.80 1/30/2021 1/29/2021 12/31/20 5067892 $46.03 1/30/2021 1/29/2021 12/31/20 $067897 $5,400.00 1/30/2021 2/1/2021 12/31/20 5067897 $37.50 1/30/2021 1/28/2021 12/31/20 $067898 $1,778.40 1/30/2021 1/29/2021 12/31/20 5067898 $12.35 1/30/2021 1/29/2021 12/31/20 $067899 $8,800.80 1/30/2021 1/26/2021 12/31/20 5067899 $61.12 1/30/2021 2/1/2021 12/31/20 $067904 $1,365.60 1/30/2021 1/29/2021 12/31/20 $067904 $9.48 1/30/2021 2/1/2021 12/31/20 $067893 $1,171.20 1/30/2021 1/29/2021 12/31/20 $067893 $8.13 1/30/2021 1/27/2021 12/31/20 $067905 $2,068.80 1/30/2021 2/2/2021 12/31/20 5067905 $14.37 1/30/2021 2/1/2021 12/31/20 $067914 $5,083.20 1/30/2021 1/25/2021 12/31/20 5067914 $35.30 1/30/2021 1/25/2021 12/31/20 5067919 $753.60 1/30/2021 1/29/2021 12/31/20 $067919 $5.23 1/30/2021 2/1/2021 12/31/20 $067915 $2,661.60 1/30/2021 2/1/2021 12/31/20 5067915 $18.48 1/30/2021 1/29/2021 A B C D E G H Alpine Cupcakes Sales and Delivery Invoices Customer Total Sales PO # (B is Order Amount 2 Blanket Qty ($2.40/cupcake Sale Order AR Created Sales PO for Cupcakes & $0.20 per doz Invoice # Date Date Order # Customer # year) Date Due invoiced delivery) 3 IN35705 12/1/20 12/31/20 5067890 C1002 8698 1/30/2021 537 $1,288.80 IN35706 12/1/20 12/31/20 5067890 C1002 7537B 1/30/2021 537 $8.95 5 IN35707 12/1/20 12/31/20 5067894 C1113 1300 1/30/2021 667 $1,600.80 6 IN35708 12/1/20 12/31/20 5067894 C1113 1158B 1/30/2021 667 $11.12 IN35709 12/1/20 12/31/20 5067895 C1122 1421 1/30/2021 1189 $2,853.60 8 IN35710 12/1/20 12/31/20 5067895 C1122 1234B 1/30/2021 1189 $19.82 IN35711 12/1/20 12/31/20 5067891 C1115 1512 1/30/2021 391 $938.40 10 IN35712 12/1/20 12/31/20 5067891 C1115 1386B 1/30/2021 391 $6.52 11 IN35713 12/1/20 12/31/20 5067893 C1036 3001 1/30/2021 488 $1,171.20 12 IN35714 12/1/20 12/31/20 5067893 C1036 2532B 1/30/2021 488 $8.13 13 IN35715 12/1/20 12/31/20 5067896 C1138 4972 1/30/2021 917 $2,200.80 14 IN35716 12/1/20 12/31/20 5067896 C1138 3579B 1/30/2021 917 $15.28 15 IN35717 12/1/20 12/31/20 5067892 C1011 7003 1/30/2021 2762 $6,628.80 16 IN35718 12/1/20 12/31/20 5067892 C1011 6544B 1/30/2021 2762 $46.03 17 IN35719 12/2/20 12/31/20 5067901 C1071 1437 1/30/2021 1034 $2,481.60 18 IN35720 12/2/20 12/31/20 S067901 C1071 1305B 1/30/2021 1034 $17.23 19 IN35721 12/2/20 12/31/20 5067900 C1057 2219 1/30/2021 971 $2,330.40 20 IN35722 12/2/20 12/31/20 S067900 C1057 1970B 1/30/2021 971 $16.18 21 IN35723 12/2/20 12/31/20 5067897 C1016 568 1/30/2021 2250 $5,400.00 22 12 15 /20 12/21 /20 coc7007 c1010 470D 1 /20 /2051 paro caz ro Sales Orders Invoices Shipping Rep AR Journal Customer List Customer Cre +B C D E G H Customer Credit Limits N Customer # | Company Name Credit Limit | Notes C1106 Bon Appetito Restaurants $15,000 4 C1031 Boulder Tea house $5,000 5 C1062 Broken Eggs Restaurant $5,000 6 C1147 Brown's Tavern $5,000 7 C1111 Bubba's Fish House $5,000 18 C1071 Buckhead Restaurants $5,000 9 C1057 Chavez Cantina $5,000 10 C1016 Country Barrel Restaurants $10,000 11 C1083 Denver Bakery Cafe $5,000 12 C1076 Denver Sirloin Restaurants $5,000 13 C1096 Elkhorn Coffee Shops $10,000 Recently extended 14 C1052 Fontana Catering & Cafe $5,000 15 C1105 Granny's Cafe $5,000 16 C1089 High Country Coffee $10,000 17 C1002 Julie's Wraps $5,000 18 C1084 Little's Grill $5,000 19 C1021 Luigi's Bistro $5,000 20 C1044 Mile High Steakhouses $15,000 21 C1086 Mountain Lion Restaurant $5,000 22 C1114 Mountain Trout Fish House $5,000 23 C1043 Nora's Cafe $5,000 24 C1053 Papa's Restaurant $5,000 25 C1137 Pebbles Inn $5,000 E Sales Orders Invoices Shipping Rep AR Journal Customer Lis Customer Credit Limits +