Create an input area on the transaction worksheet

Assignment Help Visual Basic Programming

Reference no: EM132811157

Project

Capital Plastics is a small third tier automotive supplier that manufacturers injection molded trim parts used in the interior of several models of Hondas and GM automobiles. They currently use Excel to keep track of their inventory, but the system is riddled with errors and is not a reliable way to know how much inventory they are carrying.

You have been asked to create a prototype of an Inventory Management System. The system is simple - and will use VBA code to track inventory levels. For the purpose of the prototype, you will test the feasibility of the system with 4 high volume inventory items.

Create an Excel Workbook - with two worksheets. Name the first worksheet "Inventory", and the second worksheet "Transactions". (You may want to change the sheet's object name (VBA side) for easier reference.

Populate the Inventory Worksheet to match the data shown in the screen shot to the right that contains information about the 4 high volume inventory items your system will begin to track. The Current Quantity on Hand (QOH), Unit Price, and Planned Inventory level is included in the table of data.

Next, create an input area on the Transaction Worksheet that looks similar to the following:

Use Excel's Data Validation functionality by creating a dropdown list in cell C2 to display the List of Item Descriptions (located on the Inventory Worksheet in cells B2:B5) and another dropdown list in cell C3 to display the List of Transactions (Receive, Issue and Order Status) in C3.

The Data Validation tools are located on the Data Tab - in the Data Tools section, identified by the Icon circled in the image on the right->

To run the program, the user will select an Item Description, a Transaction type and Qty to process - and will then click a button to process the transaction, as selected.

Processing Steps
- Step 1: capture the worksheet inputs: selected Item Description from C2, selected Transaction from C3, and Qty from C4
- Step 2: "find" the Item to update by comparing the select Item Description to each of the Item Descriptions on the Inventory Worksheet (in Column B).
o When the match is found, save the Row# where it was found to a variable (like intRow)
o For example - if the user wants to process a transaction for the Left Door Trim, when you compare that to data on the Inventory worksheet in column B - you will find the value in B3 matches the selected item. So, you would save the row# (3) to a variable.
- Step 3: Using the Row#, assign the selected Item's QOH, UnitPrice and Planned Qty to variables, for use in later processing
- Step 4: Determine which process to perform: Issue, Receive, or Order Status
o A "Receipt" transaction receives inventory and increases the QOH of the Inventory Item by the Qty indicated on the transaction worksheet - and updates the QOH for that item (on the Inventory worksheet).

o An "Issue" transaction tries to release inventory. Your code should check to ensure there is enough QOH to cover the Qty requested. If there is, the QOH of the Inventory Item is reduced by the transaction worksheet Qty - and then the QOH for that item is updated (on the Inventory worksheet).
o The "Order Status" transaction checks to see if the Item's inventory level (QOH) needs replenished by comparing the item's QOH to its Planned Qty. If the QOH is less than the Planned Qty, the Status should report an Order Qty equal to Planned Qty - QOH.
• Step 5: After processing the transaction, Display the Updated QOH, Value of On Hand, and Order Qty (for the Order Status transaction) formatted as illustrated in the examples below.

Examples:
• Receipt: When the user selects Receive, your solution should update the QOH by adding the transaction Qty (25) to the Inventory QOH (170), update the Inventory worksheet with the updated QOH value (195) and display the QOH (Current QOH) and Value of On Hand inventory (QOH * Unit Price).

• Issue: - When the user selects Issue, your solution should verify that there is enough inventory (QOH) to issue the selected Qty.
o If the Quantity entered exceeds the Item's QOH, a message box should pop up with an appropriate error message, and the QOH value should be unchanged and the program should terminate with no further action. Exit Sub is a line of code that can be placed in your program to terminate the subroutine's execution by "Exiting".
o Otherwise, your solution should update the QOH by subtracting the transaction Quantity (100) from the Inventory QOH (912), update the Inventory worksheet with the updated QOH value (812) and display the QOH (Current QOH) and Value of On Hand inventory (QOH * Unit Price).

• Order Status: When the user selects Order Status, your solution should compare the Item's current QOH (meaning if it was changed by other transactions - the QOH column should reflect those changes) to Planned Qty and if the Planned Qty is greater than the QOH, calculate the Quantity to Order (aka Order Qty). Order Qty = Planned Qty - QOH. The Value of the Inventory is calculated by taking the QOH and multiplying it by the Unit Price. All three values should be presented to the user similar to the display below.

Processing Requirements:
• Create a new workbook with the 2 worksheets as defined (and named) above
• Create a clean and attractive input area on the transaction worksheet using the Data Validation functionality to enforce the user to select "Good" data for the Item Description and the Transaction type.
• Your program must "Look Up" the Item data by comparing the selected Item Description to the list of Items in Column B of the Inventory Worksheet.
• When processing a Receipt, your code must add the Quantity entered to the QOH value, update the Item's
Inventory QOH value on the Inventory worksheet and display the new info on the Transaction worksheet.
• When processing an Issue, your code must check to see if there is enough inventory (QOH) to cover the issue amount. If not, display an error message using the MsgBox and Exit the program. If there is sufficient inventory, update the Item's Inventory QOH value on the Inventory worksheet and display the new info on the Transaction worksheet.
• When processing a request to check the Order Status, display the QOH, the calculated value of the inventory on hand and the Quantity to Order.
• Clear all output areas before performing a processing cycle, to eliminate any "leftover output"!
• Use at least One (1) Select Case and One (1) IF statement in your solution to demonstrate your understanding of each keyword.

Coding Requirements:
• Follow good coding practices - insert and write your code in a Code Module (not a sheet or workbook module) and declare variables for ALL calculations needed for your solution.
• Format your CODE - by using indentation and comments to make your code more readable.
• Organize your code into sections:
o Declare your variables
o Get your Inputs from the Worksheet (Item, Transaction and Qty)
o Find the Inventory Item and store inventory data to variables
o Process the Transaction
o Display the calculated outputs
o Format output as needed
- Test your solution multiple times ensuring that it returns the correct answers given different inputs.
- Before Submitting your solution, reset your Inventory Items' QOH values to those

Attachment:- VbaProject.rar

Reference no: EM132811157

Questions Cloud

Compute how much did b receive from the settlement : Find how much did B receive from the settlement of his interest in the partnership? ?20,000 was received for the entire inventory.

Why is an eye for an eye an attractive view : Why is "an eye for an eye" an attractive view according to Nathanson? What are the two main problems he raises for this view, and what are the possible replies.

Conducting an environmental analysis : Determine two specific forces in the external environment that will have the most impact on your organization. Provide a rationale for your decision.

Identify appropriate principal and provisional dsm diagnoses : Identify the appropriate principal and provisional DSM diagnoses for "Johnny" and the rationale for each diagnosis given. What additional information do you.

Create an input area on the transaction worksheet : Create an input area on the Transaction Worksheet - Create a clean and attractive input area on the transaction worksheet using the Data Validation functionalit

What the correct cost of the building should be : What the correct cost of the building should be? On June 1, XYZ Co. acquired a real property by issuing 35 360 shares of its P100 par value ordinary shares.

How much should the property be initially recognized : How much should the property be initially recognized? On July 1, XYZ traded in an old machine with a book value of P10 000 for a similar new machine

How does lack of school funding affect classrooms : How many of the nation's schoolchildren are in "racially concentrated districts, where over 75 percent of students are either white or nonwhite"?

Prepare the process accounts for process : Prepare the process accounts for Process 1 and Process 2. The company uses unit basis of apportionment for common process costs.

User Account

All Pages