Instructions

Throughout this course we will continue to use the Excel spreadsheet program as a tool to solve and document typical business problems related to the week's topics.

We will continue to apply the 3-step method to set up and solve our business problems using an Excel spreadsheet. First, after reading the problem carefully all the known facts are recorded and documented. The second step includes setting up the spreadsheet to solve the problem and check the results. Finally, step 3 involves documenting the answer in sentence format. This method is described in the Week One resource videos.

Watch the following videos:

ExcelIsFun. (2009, April 3). Excel busn math 34: Bank checks [Video

file]. Retrieved from

https://www.youtube.com/watch?v=ZFXQ7FdNxL0&list=PLD76CCB03699AD30

ExcelIsFun. (2009, April 26). Excel busn math 38: Gross pay and overtime: 5 examples [Video file]. Retrieved from

https://www.youtube.com/watch?v=ZXcDFk--ToY&list=PL076413CD8ECCA745

ExcelIsFun. (2009, April 30). Excel busn math 43: Invoices & trade discounts

[Video file]. Retrieved from

https://www.youtube.com/watch?v=y6XQUu-nJBA&list=PLCA88BD4E141E5231

ExcelIsFun. (2009, May 8). Excel busn math 48: Basics of markup (cost & selling)

[Video file]. Retrieved from

https://www.youtube.com/watch?v=J506m51cP7Q&list=PL01FF454C75148AB1

In Excel, use the 3-step method outlined in the Week One Excel Busn Math 13: Math Word Problems #1 video to complete the following End of Chapter problems.

oChapter 4: Problems 4-14 and 4-15

oChapter 7: Problem 7-46

oChapter 8: Problem 8-40

oChapter 9: Problem 9-32

The Excel spreadsheet should meet the following requirements.

oEach problem should be on a separate spreadsheet tab.

oAll known values, intermediate results and final results should be labeled as described in the 3-step method.

oFormatting features should be used to clearly define the problem and enhance readability.

Ohio Christian University X Er Connect - Class: BUS2050 Businex M MHE Reader X Ask a Question X + X player-ui.mheducation.com/#/epub/sn_f172#epubcfi(%2F6%2F70%5Bdata-uuid-edf47206345040acad523e3ce11bd6c5%5D!%2F4%2F2%5Bdata-uuid-f29a251ad0c44... 18 : it Apps V Crossroads Weddin. Other bookmarks lamvis agiveu w prepay tie Vov ivigil. HI TVHIV1 pays tie Involve on June , what WII TUITVol pay . HI TUITVOL pays on June 20, what will Forrest pay? LU 7-1(2), LU 7-2(1) Page 7-43. Sam's Snowboards.com offers 5/4/1 chain discounts to many of its customers. The Ski Hut ordered 20 snowboards with 207 a total list price of $1,200. What is the net price of the snowboards? What was the trade discount amount? Round to the nearest cent. LU 7-1(4) 7-44. Majestic Manufacturing sold Jordans Furniture a living room set for an $8,500 list price with 35% trade discount. The $100 freight (FOB shipping point) was not part of the list price. Terms were 3/10, n/30 ROG. The invoice date was May 30. Jordans received the goods on July 18 and paid the invoice on July 20. What was the final price (include cost of freight) of the living room set? LU 7-1(1, 2), LU 7-2(1) 7-45. Boeing Truck Company received an invoice showing 8 tires at $110 each, 12 tires at $160 each, and 15 tires at $180 each. Shipping terms are FOB shipping point. Freight is $400; trade discount is 10/5; and a cash discount of 2/10, n/30 is offered. Assuming Boeing paid within the discount period, what did Boeing pay? LU 7-1(4) 7-46. Verizon offers to sell cellular phones listing for $99.99 with a chain discount of 15/10/5. Cellular Company offers to sell its cellular phones that list at $102.99 with a chain discount of 25/5. If Irene is to buy six phones, how much could she save if she buys from the lower-priced company? LU 7-1(4) 7-47. Living Ornaments is offering a special for wedding planners. Wedding flower orders totaling over $500 receive a 10% discount, over $750 a 15% discount, over $1,000 a 20% discount. All orders $1,500 and above receive a 25% discount. The delivery charge is $75 on weekdays and $125 on weekends. Terms are 2/10 EOM. WeddingsRUs placed an order for Thursday, June 1, delivery. The list price on the chosen flowers totals $848.50. Calculate the trade discount and the net price for the flowers to be delivered. How much does WeddingsRUs owe if it pays the invoice on July 10? (Round to the nearest cent.) LU 7-2(1) e a WE 9 11:40 PM A 915/18/2020Ohio Christian University X Connect - Class: BUS2050 Businex M MHE Reader X Ask a Question X + X - C A player-ui.mheducation.com/#/epub/sn_f172#epubcfi(%2F6%2F52%5Bdata-uuid-37c50956539147548111e1462e245d16%5D!%2F4%2F8%5Bdata-uuid-af9c290dc3ce... Apps V Crossroads Weddin. Other bookmarks ATA CHALLENGE PROBLEMS Page 4-14. Carolyn Crosswell, who banks in New Jersey, wants to balance her checkbook, which shows a balance of $985.20. The 113 bank shows a balance of $1,430.33. The following transactions occurred: $135.20 automatic withdrawal to the gas company, $6.50 ATM fee, $8.00 service fee, and $1,030.05 direct deposit from the IRS. Carolyn used her debit card five times and was charged 45 cents for each transaction; she was also charged $3.50 for check printing. A $931.08 deposit was not shown on her bank statement. The following checks were outstanding: No. 235, $158.20; No. 237, $184.13; No. 238, $118.12; and No. 239, $38.83. Carolyn received $2.33 interest. Prepare Carolyn's bank reconciliation. LU 4-2(3) excel 4-15. Melissa Jackson, bookkeeper for Kinko Company, cannot prepare a bank reconciliation. From the following facts, can you help her complete the June 30, 2020, reconciliation? The bank statement showed a $2,955.82 balance. Melissa's checkbook showed a $3,301.82 balance. Melissa placed a $510.19 deposit in the bank's night depository on June 30. The deposit did not appear on the bank statement. The bank included two DMs and one CM with the returned checks: $690.65 DM for NSF check, $8.50 DM for service charges, and $400.00 CM (less $10 collection fee) for collecting a $400.00 non-interest-bearing note. Check No. 811 for $110.94 and check No. 912 for $82.50, both written and recorded on June 28, were not with the returned checks. The bookkeeper had correctly written check No. 884, $1,000, for a new cash register, but she recorded the check as $1,069. The May bank reconciliation showed check No. 748 for $210.90 and check No. 710 for $195.80 outstanding on April 30. The June bank statement included check No. 710 but not check No. 748. LU 4-2(3) Page 114 e a WE 11:37 PM 5/18/2020Ohio Christian University X Er Connect - Class: BUS2050 Businex M MHE Reader X Ask a Question X + X C A player-ui.mheducation.com/#/epub/sn_f172#epubcfi(%2F6%2F80%5Bdata-uuid-14197c3562294e5codcd670a8897ad66%5D!%2F4%2F2%5Bdata-uuid-53212324a122... 18 : it Apps V Crossroads Weddin.. Other bookmarks 8-36. Kathleen Osness purchased a $60,000 RV with a 40 percent markup on selling price. (a) What was the amount of the dealer's markup? (b) What was the dealer's original cost? LU 8-2(4) 8-37. John's Smoothie Stand at Utah's Wasatch County's Demolition Derby sells bananas. If John bought 50 lbs. of bananas at $.23 per pound expecting 10% to spoil, how should he price his bananas to achieve 57% on selling price? LU 8-2(4) 8-38. Page 240 excel Arley's Bakery makes fat-free cookies that cost $1.50 each. Arley expects 15% of the cookies to fall apart and be discarded. Arley wants a 45% markup on cost and produces 200 cookies. What should Arley price each cookie? Round to the nearest cent. LU S-3(2) 8-39. Assume that Arley (Problem 8-38) can sell the broken cookies for $1.40 each. What should Arley price each cookie? LU 8-3(2) 8-40. An Apple Computer store sells computers for $1,258.60. Assuming the computers cost $10,788 per dozen, find for each computer the (a) dollar markup, (b) percent markup on cost, and (c) percent markup on selling price, to the nearest hundredth percent. LU S- I(I), LU 8-2(1) Prove (b) and (c) of the above problem using the equivalent formulas. 8-41. Pete Corporation produces bags of peanuts. Its fixed cost is $17,280. Each bag sells for $2.99 with a unit cost of $1.55. What is Pete's breakeven point? LU 8-4(2) CHALLENGE PROBLEMS 8-42. Nissan Appliances bought two dozen camcorders at a cost of $4,788. The markup on the camcorders is 25% of the selling price. What was the original selling price of each camcorder? LU 8-2(3) 8-43. On July 8, Leon's Kitchen Hut bought a set of pots with a $120 list price from Lambert Manufacturing. Leon's receives a 25% trade e a 11:41 PM 5/18/2020Ohio Christian University X Er Connect - Class: BUS2050 Businex M MHE Reader X Ask a Question X + X C A player-ui.mheducation.com/#/epub/sn_f172#epubcfi(%2F6%2F86%5Bdata-uuid-e806709a730f4d51a6fe610341967d7c%5D!%2F4%2F2%5Bdata-uuid-ef596c670afb4.. 18 : it: Apps V Crossroads Weddin. Other bookmarks Sarah's sales this month (four weeks) are $20,000. How much does Sarah earn this month? LU 9-2(2) excel 9-31. Tiffani Lind earned $1,200 during her biweekly pay period. She is married and claims 4 deductions. Her annual earnings to date are $52,521. Calculate her net pay. LU 9-2(1) CHALLENGE PROBLEMS 9-32. The San Bernardino County Fair hires about 150 people during fair time. Their hourly wages range from $6.75 to $8.00. California has a state income tax of 9%. Sandy Denny earns $8.00 per hour; George Barney earns $6.75 per hour (assume this is the current minimum wage). They both worked 35 hours this week. Both are married; however, Sandy claims 2 exemptions and George claims 1 exemption. Assume a rate of 6.2% on $128,400 for Social Security and 1.45% for Medicare. (a) What is Sandy's net pay after FIT (use the tables in the text), Social Security tax, state income tax, and Medicare have been taken out? (b) What is George's net pay after the same deductions? (c) How much more is Sandy's net pay versus George's net pay? Round to the nearest cent. LU 9-2(1) 9-33. Bill Rose is a salesperson for Boxes, Inc. He believes his $1,460.47 monthly paycheck is in error. Bill earns a $1,400 salary per month plus a 9.5% commission on sales over $1,500. Last month, Bill had $8,250 in sales. Bill believes his traveling expenses are 16% of his weekly gross earnings before commissions. Monthly deductions include Social Security, $126.56; Medicare, $29.60; FIT, $189.50; union dues, $25.00; and health insurance, $16.99. Calculate the following: (a) Bill's monthly take-home pay, and indicate the amount his check was under- or overstated, and (b) Bill's weekly traveling expenses. Round your final answer to the nearest dollar. LU 9-2(1) Page 266 SUMMARY PRACTICE TEST e M a M 11:44 PM 5/18/2020