Practice Problems for Exam

1. Ryan wants to find out the value of his investment after three years. He is planning to invest in bonds ($5000), balanced funds ($5000), growth funds ($5000) and common stocks ($10000). The expected annual rates of return for this investment are 2.5% (bonds), 2.5% (balanced funds), 4% (growth funds) and 5% (common stocks). The other option that he has is to invest all the amount in a 3 year CD which can give him 4% return (compounded monthly). Calculate the investment value of both of these options after 3 years and determine which of these options Ryan should select.
2. Archer foundation is offering a scholarship of $5000 to the non-traditional female students living in St Joseph county, Indiana with the average GPA is greater than or equal to 3.5 and annual household income less than $50000. Use an appropriate Excel function to fill the Amount column in the Scholarship worksheet. (The cell value should be the eligible scholarship amount.)
3. Worksheet ConsultRates provides the billing rates of each IT consultant in the consultants@work company. Each consultant charges differently for commercial businesses and charity organizations. ConsultRates2 provides detail information about various organizations where some consultants have worked along with the type of those organizations (i.e. charity Vs no-charity). Fill the cells in Charge/ hour column in this worksheet using appropriate Excel functions. Remember, charges are dependent on the consultant and the type of organization.
4. Mitch and Ian are planning to start an online venture for selling Microsoft Excel based spreadsheet solutions to the small business firms in the Michiana area. In order to promote their sales, they have distributed discount coupons through local newspapers. As shown below, each coupon offers a specific dollar off discount based on some minimum order amount purchase requirement. For example, customers who redeem a 50Off coupon will receive $50 discount if the total order value is more than $200.

Coupon code Discount (Face value) Minimum order value

50Off $50 $200

30Off $30 $150

10Off $10 $100

Create a worksheet that calculates the total payment due for any order value. Total payment due is determined by adding 6% tax to the net order amount (i.e. order value minus eligible coupon discount). Worksheet users should be able to enter an order value and coupon codes in cells and your excel formula should be able to apply appropriate discount only if the minimum order value condition is met. For demonstration purpose, use coupon code 30Off and Order value - $151. However, your solution should work for any coupon code or formula.

5. John is planning to buy a new Honda CR-V. The price of the car is $30000 and he has been offered a 6.0% loan for six years. How much in total will be he be paying for the car over six years?

Charge/Hour Organization Does Chari Consultant Jiant Shrimp Organization No Brian Campbell Pygmy Giraffe Crafts Yes Joseph Hackett Brown Indian Arts No Susan Moore Simple Maze Life Yes Angela Hall Authentic replica mart No Tina Hunter Boneless Ribs Food Yes Laurie Baker Consultant Aaron Cole Angela Hall Brian Campbell Cliff Sellers Derek Yen Hector Martin Joanna Sellis Joseph Hackett Karen Watson ! Kelly Rosen Laurie Baker Susan Moore i Tina Hunter Regular Charity Chargel hour Charge/hour $150 $50 $85 $45 $95 $70 $80 $70 $120 $95 $110 $80 $120 $85 $1001 $55 $110| $45 $85 $75 SRO $60 $100 $55 $75 $45 GPA Tina Mark Matt John Danny Ivan Lee Tracy Beth 1 Peter Sheena Carly Rita Gender Female Male Male Male Male Male Male Female Female Male Female Female Female Type Residency State non-Traditional Laporte Indiana Traditional Elkhart Indiana Traditional Elkhart Indiana non-Traditional St Joe Michigan non-Traditional St Joe Michigan Traditional Marshall Indiana Traditional Marshall Indiana non-Traditional St Joe Indiana non-Traditional St Joe Indiana Traditional Marshall Indiana Traditional Marshall Indiana non-Traditional St Joe Indiana non-Traditional St Joe Michigan H H Income 3.6 45000 3.9 55000 3.9 120000 4 30000 2.2 60000 2.85 80000 4 150000 3.45 45000 3.7 35000 3.7 25000 65000 3.75 48000 4 48000 2.5 Charge/Hour Organization Does Chari Consultant Jiant Shrimp Organization No Brian Campbell Pygmy Giraffe Crafts Yes Joseph Hackett Brown Indian Arts No Susan Moore Simple Maze Life Yes Angela Hall Authentic replica mart No Tina Hunter Boneless Ribs Food Yes Laurie Baker Consultant Aaron Cole Angela Hall Brian Campbell Cliff Sellers Derek Yen Hector Martin Joanna Sellis Joseph Hackett Karen Watson ! Kelly Rosen Laurie Baker Susan Moore i Tina Hunter Regular Charity Chargel hour Charge/hour $150 $50 $85 $45 $95 $70 $80 $70 $120 $95 $110 $80 $120 $85 $1001 $55 $110| $45 $85 $75 SRO $60 $100 $55 $75 $45 GPA Tina Mark Matt John Danny Ivan Lee Tracy Beth 1 Peter Sheena Carly Rita Gender Female Male Male Male Male Male Male Female Female Male Female Female Female Type Residency State non-Traditional Laporte Indiana Traditional Elkhart Indiana Traditional Elkhart Indiana non-Traditional St Joe Michigan non-Traditional St Joe Michigan Traditional Marshall Indiana Traditional Marshall Indiana non-Traditional St Joe Indiana non-Traditional St Joe Indiana Traditional Marshall Indiana Traditional Marshall Indiana non-Traditional St Joe Indiana non-Traditional St Joe Michigan H H Income 3.6 45000 3.9 55000 3.9 120000 4 30000 2.2 60000 2.85 80000 4 150000 3.45 45000 3.7 35000 3.7 25000 65000 3.75 48000 4 48000 2.5