70.2K

Verified Solution

Link Copied!

can you help me whit this homework using excel what are the formulas that i can use to solve this questions it's for auditing class.

I just need the formulas so I can add on the excel thank you.

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 Deliveries Sales Orders Invoices Shipping Report AR Journal Customer List Customer Credit Limits S Product Delivery Carol Cake 55 22 355 SUS 25 IS 10 15 NII 43 12 102 40 SS CAL 42 152 112 UN Chocolate Gluten-free Cheate 93 N2 37 793 145 NO 41 INN 43 274 ss INO 85 $75 152 INI 47 8N1 1447 22) 55 IS 72 103 46 17 9 MI 44 95 MO 19) 55 22 12 125 77 30 21 267 145 167 22 100 200 57 SN DE 5 NL 00 5464 SU SIL.12 51930 SI5.24 537.50 $12.15 56013 $16.15 $17.23 59 5177 594 514.37 SIS 23 SE362 $2.53 $24.23 SIA S. 51402 5350 SIRAS 535 45 SE 9 11 5) 102 12 20 15 215 115 607 55 342 122 201 41 2 Nales Order Sale Order Darl Customer 5067 12/1/20 CTMC 8067891 12/1/20 CHIS 50%67892 12/1/20 C101 5067393 12/1/20 C1036 S067894 12/1/20 CHI S067295 12/1/20 CH2 50679 12/1/20 CH S0677 12/2/20 C1016 S0679 12/3/20 C1021 5067899 12/2/20 CHO S0600 12/2/20 CIOS S67901 12/3/20 C1071 06792 12/2/20 C112 5067903 12/2/20 CHI S0674 12/1/20 C1031 S067205 12/120 C1043 306706 12/1/20 C10 S067907 12/3/20 C1062 5067 12/1/20 CIO S067907 12/3/20 C1034 S067910 12/3/20 CIONS SC7911 12/1/20 CH10 9067912 12/1/20 C1106 S067913 12/3/20 C1114 S067014 12/4/20 C1044 5067915 12/4/20 CHOS S067916 CHON S067017 12/4/20 SON 12/4/20 C1147 S067919 12/5/20 C1045 S067920 1/1/21 C100 S067921 1/1/21 C1036 S067922 1/1/21 C104 S067923 1/1/21 C1057 5067024 1/1/21 C10 067925 1/1/21 CH 9067926 1/1/21 CION S067921 1/1/21 CHO 067923 CHIN 5067929 1/1/21 C1139 067930 1/2/21 C10 S062031 1/8/21 CH14 5067932 11/21 C1011 S067011 1/4/21 C1021 S062934 1/4/21 CION 067935 1/11 CIOS S067936 1/4/21 CIOS S067937 11/21 C1071 007938 1/4/21 CION 067019 1/4/21 CION 5067940 1/4/21 CION 5067941 1/21 CITI 5067042 1/4/21 C1122 067943 1/21 C110 S067944 1/8/21 CH S067945 15/21 C1027 5067446 1/5/11 C100 S067041 1/8/21 CHOS 5067941 1721 CH10 ONE SX LLE 2992299*98*25*33%9890882939%3AE33 43 Sales Orders Lema Red Velvet Spice Vanilla 66 1,19 44 107 4 352 440 139 76 55 45 116 61 44 196 77 102 152 89 207 3.30 111 406 92 12 255 357 122 263 121 330 77 145 7 9 31 NS 50 NS N9 107 216 127 59 197 15 21 13 24 90 vo IKS 15 14 15 175 145 140 32 45 322 202 229 437 69 KK 56 9 101 149 100 126 132 150 40 52 42 142 100 52 NO 11 47 20 75 707 415 453 1211 47 356 203 22 610 163 105 NO 22 12 14 3 137 9 236 26 14 16 44 245 10 155 420 100 57 64 171 10 31 216 141 XI 24) 464 265 29 79 134 71 80 214 54 164 16 106 120 320 79 09 215 174 90 111 29 257 16 168 56 165 150 16 451 45 52 139 200 12 160 9? 10 201 31 SS 155 616 352 19 1056 3 93 29 14 VI 1017 IS SE Total Order 51.227.75 594493 $6.6743 SL.1793) S161192 52.73.42 52.216 OK 55.407.50 51,790,75 S.92 52146 52.4083 51.433.0 525617 51.37S ON 52.05.17 $1.93.75 5316:53 51.474.42 536733 $3.521 ON 52,436.00 SN.700.00 52.0324 55. SO SOON 5.635 51.1620 S161192 57583 $12.10 S1416 17 5614095 52.815 42 5314 52.3633 561225 5423433 51.72067 SMO 56.535.00 52A142 SNORK Almad Total Vegan ordered 37 17 72 15 KR 40 45 917 15 2350 45 741 71 M7 971 104 $93 6 106 15 st 4 ME NAS 7 131 Jo NIT 152 x 1457 12 10014 12 33 41 14 2118 13 1527 21 20 12 314 265 5075 29 SH 127 3541 SR 1165 157 19 IN X7 1745 35 712 6 127 135 2700 50 TO 165 3314 44 4 1211 49 990 02 1241 1832 35 700 00 IN4 29 577 71 120 12 64 220 51 1034 19 377 216 4320 3 303 95 100 75 1011 12 334 345 3) 206 19 367 150 27 567 212 6.96 187 144 20 200 261 385 147 395 121 100 254 ,? 7 ;,, SINO $9340 USD 51,17120 SINO 360 S2 200 55.400.00 51.776 40 SUNDO NO 52100 $22.410 51.493.20 525440 SE365.60 2. MIND 531440 SIAMOND 53640 3.49 52419.30 $40.00 $2.011.40 5.03.20 52.614 IND SL. 15440 51.600D 5753.60 5121 $1,406.40 56.00.40 52.70 $3760 52.352.00 419.30 S.195.30 SIND SHO 56.40.00 52.4210 $7.9516 1360 S1.6.09.20 SI.1440 $23.00 52,976.40 S40 SIANO 54.50720 513540 51.44 2901 51.545.60 SIO S60.00 52.45160 5904 S1065 SI112 5523 $0.00 50.77 54235 $19.42 5262 OS 70 305 140 19 31 22 210 NS 15 324 121 SUN 107 TE VIS SIVES 166 41 173 XI 11 6N 13 122 30 0 19 70 22 62 4 3 11 EN 16 95 19 52913 SIUNT 2012 $45.00 SIA 55523 514.2 S11 S2218 $16.50 5306 S10.5 SILA7 5310 3942 00+ 10 190 IN 100 119 149 220 14 225 69 171 145 77 S2 124 45 SIR 27 123 49 131 40 100 84 45 ww 72 26 102 51.450.58 53.216 52.193.30 52.0 54,427.33 51.101.67 5450 51.34 534SK 52.92175 51.55633 $10,6333 52.49 5911.ON 510 4000 5211 06 934 217 29 OR SPE ES 5.2015 $10.73 52113 517.23 563 572.00 STE Sales Orders Invoices Customer 311 TOE SAD | z How > > FOR TO www RU ro rom row www HOW w w ACAS DE 316831101 www w w ww ww Sales Orders Inice Customer Shipping Report AR JE Bli 11 . - Sales Orders Immo Shing AR w TER UN 14! * PARETE AR Journal Customer List Customer Credit Sales Orders Invoices Shipping Report CORPORATE CUSTOMER LIST Customer Address City State CO C1001 C1106 C1031 C1062 C1147 C11 C1071 C1057 C1016 CION C1076 C1096 C1052 C1105 C1089 C1002 C1084 C1021 C1014 C1086 C1114 C103 C1053 C1137 C1113 C1122 C1115 C1128 C1036 C1138 C1045 C1027 CIOLI Customer Name since 6/14/10 Alpine Cupcakes Storefront 9/18/18 Bon Appetito Restaurants 3/23/16 Boulder Tea house 59/17 Broken Eggs Restaurant 2/920 Brown's Tavern 12/17/18 Bubba's Fish House 7/20/17 Buckhead Restaurants 2/16/17 Chavez Cantina 923/15 Country Barrel Restaurants 11/6/17 Denver Bakery Cafe 8/12/17 Denver Sirloin Restaurants 5/22/18 Elkhorn Coffee Shops 11/27/16 Fontana Catering & Cafe 6/10/18 Granny's Cafe 5/7/18 High Country Coffee 5/1/15 Julie's Wraps 12/30/17 Little's Grill 11/12/15 Luigi's Bistro 927/16 Mile High Steakhouses 2/W/IN Mountain Lion Restaurant 3/29/19 Mountain Trout Fish House 9/6/16 Nora's Cafe 12/16/16 Papa's Restaurant 10/15/19 Pebbles Inn 2/2619 Scotty's Tavers 5/13/19 Smokey's Barbeque Pit 5/10/19 Southside Cafe 7/12/19 St. Francis Hotel and Spa 627/16 Steinberg Delis 11/13/19 The Breakfast Place 10/11/16 The Sandwich Place 2/13/16 UC Boulder Food Service 8115 UC Denver Food Service 1000 16th Street 4990 Washington 1700 13th St 2400 S. Parker Road 950 Lincoln St 5050 E 28th St 2700 Welton Street 2351 Champak Street 1400 Poplar Street 321 17th Street 320 E Colfax Avenue 400 S Broadway 1776 5th St 442 E Colfax Avenue 390W 32nd Street 1300 S Clarkson 3600 S Monaco Parkway 1500 Arapahoe St 1730 19th Street 37000 Boulder Canyon De 6- 30th Street 1 700 Federal Blvd 2300 S. Colorado Blvd 8000) Foothills Highway 600 Sherman St 7700 E Hampden Ave 670 S. Broadway 800 Walnut Blvd 1700 Broadway 8110 Powiato Mile Road 1741 Broadway 1669 Euclid Ave 13001 E 17th Place Denver Denver Boulder Denver Denver Denver Denver Denver Denver Denver Denver Denver Denver Denver Denver Denver Denver Denver Denver Boulder Boulder Denver Denver Boulder Denver Denver Boulder Boulder Denver Denver Denver Boulder Aurora CO CO CO CO CO CO CO CO CO CO CO CO co CO CO CO CO CO CO CO CO CO CO CO CO CO CO CO CO CO CO CO Zip Phone Number 20202 (702) 555-5013 80202 (303) 555-0104 803027201 335.313 80231 (720) 555.1078 80203 (303) 555-2791 80207 (7201 555-4051 80205 (720) 555-2106 80222 (303) 555-9886 80220 (303) 555-5341 30202 (720) 555-1747 80203 (720) 555-6912 80209 (720) 555-1185 80204 (7201 555-4668 80220 (7027555-0238 90212 (103) 555.3833 80210 720) 555-8995 90237 720) 555-8253 80202 (702) 555-5959 80202 (303) 555-5325 20302 (303) 555-2662 80303 (303) 555-2233 80204 (303) 555-1772 80222 (720) 555.5965 *0302 (720) 555-2618 80203 (720) 555-3424 80113 (7201 555-7649 0305720) 555-7312 80302 (702) 555-5402 80290 (303) 555.4454 80249 (702) 555.9820 90210 (303) 555-616% 20309 (720) 555.2035 80045 (303) 558-40016 Sales Orders Shipping Report AR Journal Customer List Customer Credit Limits Invoices Customer Credit Limits Customer Company Name Credit Limit Notes C1106 Bon Appetito Restaurants $15.000 C1031 Boulder Tea house 55,000 C102 Broken Eges Restaurant $3.000 C1147 Brown's Tavom $5.000 CUM Bubba's Fish House 85.000 C1071 Buckhead Restaurants 55.000 C1057 Chavez Cantina 55.000 C1016 Country Barrel Restaurants $10.000 CIO Denver Bakery Cafe 95.000 C1076 Denver Surfin Restaurants $5.000 C109 Bihor Coffee Shops $10.000 Recently extended C1052 Fontana Catering & Cafe $5.000 C1105 Granny's Cafe $5,000 CIGNO High Country Coffee $10.000 C1002 Julie's Wraps 55.000 CIOS Little's Grill $5,000 CICI Lop's Bistro $5.000 C1014 Mile High Steakhouses SI5.000 C166 Mountain Lion Restaurant $5.000 C1114 Mountain Trout Fish House $5.000 C1043 Nora's Cafe $5,000 C1051 Papa's Restaurant 85.000 C1137 Pebbles Inn $5,000 C113 Scotty's Tavers $5.000 C1122 Smokey's Barbeque Pet $5.000 CHIS Southside Cafe 55.000 C11 St. Francis Hotel and Spa $5.000 CI6J6 Stemberg Delis 33.000 C1138 The Breakfast Place 55.000 C1045 The Sandwich Place 55.000 C1627 UC Boulder Food Service $15.000 C1011 UC Denver Food Service SI5.000

Answer & Explanation Solved by verified expert

See Answer