In

Research Question

How will the bookstore manager minimize on costs involved in purchase of books?

Objectives

To determine the top five publishers to open the accounts with in order to save on costs and expenses involved in purchasing books.

Conditions

There are two possible sources, book publishers, or a major distributor that has books from many publishers.

Assumptions

• The book publishers offer 50 percent discount when an order is above 30 units (one unit each of thirty titles or 30 units of the same title or any combination) and free shipping. However, if an order is less than 30 units, the publisher charges shipping charges that can amount to 15 percent or more.
• The major book distributor offers a 40 percent discount and free shipping when the order is above 10 units.

Logic

The bookstore can minimize its costs if it places orders direct from the publishers as opposed to major book distributors. This is because publishers offer bigger discounts that distributors.

Execution

The bookstore manager will identify the top five publishers that the company will open accounts with. Since the quality of data available in the company’s database is not very clean, the manager will first correct the data by ensuring that the names of all publishing companies are spelt correctly. After getting the correct spelling of the publishers’ names, the manager will then create a new excel sheet, where he will list the quantity of all orders made during the last few months from every publisher, quote price from each publisher, price charged, and discount allowed. There after, the manager will utilize the sorting and filtering feature of excel in order to arrange the publishers in a descending order (the publisher who the bookstore has place the highest number of books with is listed first and the publisher with the lowest number of books ordered is listed last in the table). This will form a basis of preparation of a pivot table. In the pivot table, the manager will then list the sum of total quote price for every publisher, then compute the actual buying price after being allowed the 50 percent discount, and do the same for the distributors after being allowed the 40 percent discount. Lastly, the manager will compute the difference between the purchase price from the publishers and from distributors. Below is a pivot table illustrating the manager’s computation in order to determine the top five publishers:

 Publisher Quantity ordered Quotation price Order from publishers Order form distributors Money saved Random House 2293 \$18076.88 \$9238.44 \$10846.128 \$1607.688 Harper Collins 2148 \$15178.27 \$7789.135 \$9106.962 \$1317.827 Penguin Group 1106 \$9282.54 \$4841.335 \$5569.602 \$728.267 Houghton Mifflin 717 \$6587.23 \$3293.665 \$3952.338 \$658.673 Scholastic 1016 \$8419.54 \$4409.77 \$5051.724 \$641.954 Imagination Games 488 \$7863.6 \$4131.8 \$4718.16 \$586.36 Simon and Schuster 1046 \$7686.12 \$4043.06 \$4611.672 \$568.612

Analysis

The table above shows the top seven publishers in terms of cost saving. Houghton Mifflin comes before Scholastic, and Simon and Schuster publishers, even though the bookstore has placed a lower quantity of books with it compared to the other two publishers. The same case is observed in Scholastic, which comes before Simon and Schuster, despite the higher quantity of books ordered from Simon and Schuster than in Scholastic. Even though the amounts saved from the Scholastic, and Simon and Schuster are lower as compared to what is saved from Houghton Mifflin and Imagination Games, the quantity ordered from the former is higher than what is ordered from the latter. The store manager should then choose Scholastic, and Simon and Schuster to allow the store to avoid incurring more ordering costs as it makes frequent orders from Houghton Mifflin and Imagination Games just because of mere differences in the amounts saved.

Recommendation

The store manager should open accounts with Random House, Harper Collins, Penguin Group, Scholastic, and Simon and Schuster.