MIS 111 Fall 2011 Project 4 111016
pdf
keyboard_arrow_up
School
University Of Arizona *
*We aren’t endorsed by this school
Course
111
Subject
Information Systems
Date
Feb 20, 2024
Type
Pages
4
Uploaded by MateSteelGerbil31
Lab Project 4 –
Inventory and Capacity Planning
MIS 111 –
Fall 2011 Page 1 Overview
For this assignment, you’ll analyze the inventory requirements needed to bake batches of cupcakes. As part of this exercise, you’ll determine whether you have excess inventory and create a replenishment worksheet of the ingredients that need to be ordered. As with most inventory planning problems, you won’t be planning for building just one cupcake, but rather one “lot” or “batch”.
Data Sources
1)
Excel Spreadsheet –
Inventory and item worksheets. 2)
Bill of Material you should use for this exercise (shown below). Task 1 –
Inventory Report
For this task, you will create an inventory report for the items in inventory for the group of inventory parts whose UPC Code’s last digit is the same as the last digit of your PIN (CatCard) number.
Your inventory rep
ort must include the UPC Code, the items’ description, the unit of measure (UOM) description, the quantity on hand, the expiration date of the inventory, and whether the current inventory level is below the Minimum QOH defined for the part in the Inventory worksheet. Note: Your inventory worksheet MUST
be on an independent worksheet, and you may only copy the list of UPC codes from Item worksheet as numeric values. All
other fields must determined using VLOOKUPS into the ITEM, INVENTORY, and UOM worksheets using cross-
sheet references. You MUST
determine the “Below Min QOH” using an IF statement
. The expiration date for an Inventory item is “Max Hold Days” (Item) after the Purchase Date (Inventory). An Item’s stock level is below the “Min QOH” if the QOH (I
nventory) is less than the Min Inventory (Item), so your IF statement will use two VLOOKUP statements to obtain the values you compare (i.e., IF(VLOOKUP(
…
)<VLOOKUP, “
Yes
”
, “
No
”
)). You should cut-and-paste a copy of your table from your Excel spreadsheet into your report, using “Paste Special” to insert the table as a picture (such as a metafile).
Lab Project 4 –
Inventory and Capacity Planning
MIS 111 –
Fall 2011 Page 2 Task 2 –
Costed Bill-of-Materials
For this task, you will extend the standard BOM (shown below) to create a new professionally-formatted worksheet with a costed Bill of Materials (BOM) for baking one lot (in this case a “lot” is one dozen cupcakes). For your costed BOM, for each UPC code shown below, you will use VLOOKUP to display the part description, the unit cost of the item, and the value of the materials in one “
lot
”
. To calculate the material value (cost of goods for each item in the Bill of Material), you will multiply the quantity per assembly (also given below) by the standard unit cost (from the Item worksheet) for the item. After you have calculated the value of each item, you will use the Excel SUM function to calculate the total cost of goods for the complete bill of material. UPC Code Quantity Required 6660 0.5 4958 2 5474 0.125 7408 3 1846 12 Use this BOM as the material list for one lot (a dozen) MIS 111 Cupcakes Note: As with task one, your Costed BOM worksheet should be on a separate worksheet and you can only enter the UPC codes and the quantity required (shown above) as constant values. You must use VLOOKUP and cross-sheet references to the appropriate worksheet to determine the values for the part description, Unit Cost, and calculated extended cost (Qty Required * Standard Unit Cost). You should cut-and-paste a copy of your table from your Excel spreadsheet into your report, using “Paste Special” to insert the table as a picture (such as a metafile).
Lab Project 4 –
Inventory and Capacity Planning
MIS 111 –
Fall 2011 Page 3 Task 3 - Replenishment Worksheet
For this task, you will create a shortage report for baking multiple lots of the cupcake recipe (BOM) in Task 2. For this exercise, your total lot size will be 10 + the last digit of your PIN (CatCard) number.
For example, if your PIN n
umber ends in “0”
, you should bake a batch of 10 lots (or 10 dozen cupcakes). Along with the UPC code and the Quantity required per dozen (given), your worksheet will use VLOOKUP to display the part description (Item) and quantity on hand (Inventory). In addition, you will calculate the total demand for each part to build your total demand (total lot size, see above), and then calculate which parts have insufficient quantity on hand to meet the demand. When calculating the shortages, be sure to use the MAX function
so that you do not have a negative demand (shortage), which would occur when the quantity on hand is sufficient to meet the demand for a part. Finally, each item that requires replenishment should be highlighted using Excel’s conditional formatting
(you may not
highlighting the cells by manually setting the background color). You should cut-and-paste a copy of your table from your Excel spreadsheet into your report, using “Paste Special” to insert the table as a picture (such as a metafile). Task 4 - 250 Word Mini-Research Report
Inventory management is a financial trade-off between inventory costs and stock-out costs. The more inventory your company holds, the more working capital is needed and the more depreciation you get. On the other hand if you do not have enough stock, you get inventory stock-outs, missing potential sales, possibility interrupting the whole production process. Inventory stock depends essentially of two factors: anticipated demand, which is the amount of items that we expect to be consumed or bought, and lead time, which is the delay between reorder decision and renewed availability. Yet those two factors are subject to uncertainties. Anticipated demand is often inaccurate because customer’s interest in a product can vary in rather unpredictable ways. Likewise, lead times are often inaccurate because suppliers or transportation companies may be faced with unplanned difficulties. For this task, you will write an original 250 word mini-research report
[range: 225-275 words] on how a company could limit the impact of these two challenges of inventory management (inaccurate anticipated demand and unexpected delays in purchase lead times). For you answer, you should consider strategies to lessen the impact of stock outs while still maintaining a prudent plan for financial management of your company’s capital. Your report should include at least one outside reference (excluding Wikipedia and the class textbook). As is the case when including material from any outside source, you must
include an appropriate citation to your source.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Lab Project 4 –
Inventory and Capacity Planning
MIS 111 –
Fall 2011 Page 4 Extra Credit
Using the inventory worksheet from task 1, extend the worksheet to include two additional fields: the amount
of excess inventory over a 30-day requirement (Average Daily Use * 30), and the value
of that inventory (your calculated excess QOH * standard cost for the item). Of course, if an item does not have any excess inventory (i.e., you have less than 30 days of inventory on hand), you should show the amount and value as zero (MAX function). Calculate the total value of your excess inventory, and then determine the monthly interest cost of carrying cost of that inventory investment for each month the inventory remains unsold (use the interest payment financial function IPMT), given a cost of capital of 5% (APR). From a more general perspective, how would a company decide if this is a good investment? In other words, why would a company benefit would a company gain from holding this excess inventory (also known as “safety stock”)?
As with all requirements, if you refer to an outside source when researching your answer, be sure to include an appropriate citation. Deliverables
Blackboard TurnItIn
: Report (PDF/RTF/Word .doc or .docx) document Blackboard Dropbox
: Spreadsheet (MUST be an .xls or .xlsx) document If you are using a Mac, you should save your final report as a PDF document to ensure that the “QuickTime” documents pasted by MacOS are available for grading.
Due Date
11:59pm, Thursday, October 27
th
, 2011. Revised October 16
th
, 2011