LESSON 6
Assignment
Question 1 (12 marks)
Computer question
Before you attempt this question, you should work through Computer illustration 62 in
the Lesson Notes.
Description
Yales Jewellers Inc. purchases 30,000 onequartercarat diamonds each year for
mountings in various settings. Pertinent information relating to the diamonds is given
below:
Purchase cost per diamond
€200
Cost to carry one diamond in inventory for one year
5
Cost of placing one order to the company’s supplier
40
The maximum order that the insurance company will permit is 1,500 diamonds. The
minimum order that the supplier will permit is 300 diamonds, with all orders required to
be in multiples of 300 diamonds. The company has been purchasing in the maximum
allowable volume of 1,500 diamonds per order.
Required
Use the procedure described below to complete worksheet L6Q1. From this worksheet,
answer the following questions:
a.
(6 marks)
What is the optimum quantity for the company’s diamond orders?
b.
(4 marks)
What is the annual cost savings that will be realized if the company purchases in the
volume you have determined in (1) above, as compared to its present purchase policy?
c.
(2 marks in total)
Use the completed worksheet to perform the following whatif analysis:
(i)
(1 mark)
What is the EOQ if the annual quantity used is 60,000 units instead of 30,000 (in
cell C7)? Keep the order sizes unchanged.
(ii) (1 mark)
What is the EOQ if the cost of placing an order decreases to €15 (in cell C11), with the
annual quantity unchanged at 30,000 units?
Management Accounting Fundamentals
Assignment 6
•
1
This preview has intentionally blurred sections. Sign up to view the full version.
View Full Document
Procedure
You have been provided with a partially completed worksheet L6Q1. Complete the
worksheet by following these steps:
1.
Open the file MA1L6Q1.
2.
Examine the layout of the worksheet.
3.
Note that rows 5 to 13 have been used as a data table. Cells C7, C9, and C11 in the
data table need to be filled in.
4.
After filling in the data table, display the table for part 1 (rows 14 to 24). Note that
the table is completely empty with the exception of labels and lines. Also note that
cells B18 to B21 are filled with xxx. Replace these xxx with the corresponding
formula labels and centre them. See cell B17 for an example. Note that the formula
labels you enter in these cells are not intended to produce computation results but
merely serve as labels for ease of reference.
5.
Complete the EOQ table (in rows 17 to 21). Also enter the appropriate formula in
cell B23. (
Hint:
Use the MINA function.)
6.
Enter the appropriate formulas (not values) in cells B29, B31, and B32. Use the
HLOOKUP function in cell B29, looking up the total annual cost for the current order
size in cell B27 in the EOQ table in cells C16 to G21.
7.
Save your completed worksheet.
8.
Copy and paste cells A1 to G33 into your assignment Word document.
9.
Display the formulas in your completed worksheet. Copy and paste the formulas in
cells A1 to G33 into your assignment Word document.
This is the end of the preview.
Sign up
to
access the rest of the document.
 Spring '11
 none
 Management Accounting Fundamentals, Hallas Company

Click to edit the document details