Complete the following case studies from Ch. 8 & 9 of Business Math: 

  Case Study 8-2, p. 300

  Case Study 9-3, p. 345

Use  Microsoft Excel template for the assignment and your answers.(I dont Have the Template)

Note: Show all work and calculations. (The use of Microsoft Excel software is required.)

8.2 McMillan Oil & Propane, LLC

Rob McMillan finished reading the article in the local paper, “Fuel Prices Expected to Increase into Summer.” The article cited major factors in the crude oil spike such as Iran’s nuclear program and overall Mideast instability. Operating as an independent fuel oil and propane distributor in rural Virginia, this wasn’t good news for Rob. It had been a moderate winter, but wholesale fuel prices were higher than normal. Rob grabbed the last invoice from his supplier and saw that fuel oil was priced at $1.964 per gallon, with trade discounts of 7/5/2.5 available. It seemed like those discounts were not as good as in the past. McMillan Oil offers its own customers credit terms of 2/15, net/30, with a 1% service charge on late payments. Of the $25,000 in average fuel oil sales per month, normally half of Rob’s sales are paid within the discount period, and only 5% incur the monthly service charge. Rob is concerned because a number of his fuel oil customers are behind in their payments, and he is considering some changes.

1. Using the starting price of $1.964 per gallon, what is Rob’s net price after applying the 7/5/2.5 trade discount series using the net decimal equivalent?

2. Rob is considering purchasing his fuel oil from a new supplier offering fuel oil at $2.086 per gallon, but with a better trade discount series of 10/7/4. Compared to your answers in Exercise 1, which supplier would be a better deal for his company?

3. Using the average monthly sales of $25,000, what is the total savings enjoyed by those fuel oil customers who normally pay within the discount period? What is the total penalty paid by those that are delinquent over 30 days?

4. Currently, only 25% of the sales volume is paid by customers who are taking advantage of the discount, and 20% of the sales are over 30 days. Using these figures, how does that change your results from Exercise 3 above? Because your answers show that Rob is presently making more money (at least he should), why should he be concerned about the current situation? What suggestions do you have?

9-3 Deer Valley Organics, LLC

With an original goal of selling fresh apples from the family orchard at a roadside stand, Deer Valley Organics has become a unique operation featuring a wide variety of locally grown organic produce and farm products that include their own fruit as well as products from the area’s finest growers. A number of different products are available, including apples, strawberries, and raspberries as either prepackaged or pick your own; assorted fresh vegetables; ciders, jams, and jellies; and organic fresh eggs and free-range chicken whole fryers. Prepackaged apples are still the mainstay of the business, and after adding all production and labor costs, Deer Valley determined that the cost of these apples was 64 cents per pound.

1. What would be the selling price per pound for the prepackaged apples using a 30% markup based on cost? A 40% markup? A 50% markup?

2. Based on the national average for apples sold on a retail basis, Deer Creek sets a target price of $1.10 per pound for the prepackaged apples. Using this selling price, compute the percent of markup based on cost for the prepackaged apples. Then, compute the percent of markup based on selling price.

3. Deer Valley allows customers to pick their own apples for $8 a bag, which works out to approximately 47 cents per pound. How is that possible given the cost data in the introductory paragraph? Would the orchard be losing money? Explain.

4. Deer Valley receives a delivery of 1,250 lb of tomatoes from a local supplier, for which they pay 18 cents per pound. Normally, 6% of the tomatoes will be discarded because of appearance or spoilage. Find the selling price needed per pound to obtain a 120% markup based on cost.