Reference no: EM132800647

INFS 3150 Principles Of Structured Computer Programming And Problem Solving - University of Toledo

Project - Inventory Analysis

Instructions:

Create an Excel document that performs an analysis of a company's inventory management. You must write VBA code for this assignment.
Insert a code module and write a subroutine procedure named project1 in an Excel macro-enabled Workbook saved with the name of yourLastName_Proj1.xlsm. Your program must meet all the specifications described below to receive full credit.

Design a user interface on an Excel worksheet similar to what you see in the screen shot pictured to the left. (Translation: type into Excel the lables you see pictured in the screen shot.) Add two buttons, one to trigger the execution of your Project1 subroutine and another to execute a second subroutine that will clear the input data from the previous execution of your code.

In the first subroutine (Project1), create a solution that uses the InputBox()to prompt the user to enter the Company Name, Beginning Inventory Value, Year End Inventory Value, and Annual Cost of Goods Sold. Write the statements to calculate the Days to Turnover Inventory and Inventory Turnover Ratio and output the data to the Excel worksheet.

Your solution MUST use variables to receive and store the user's inputs and the program's calculations and then assign the values to the cells in column C.

The formulas needed for the subroutine include: Days to Turnover Inventory is calculated by taking the Year End Inventory Value and dividing it by the Annual Cost of Goods Sold and then multiply that value by 365. Inventory Turnover Ratio is calculated by dividing 365 by the Days to Turnover Inventory.

Additionally, you must display the current date in C9 for the Analysis Date. The current date can be accessed using the VBA keyword DATE - which returns the computer's system date.

A second subroutine, triggered by the user clicking on the "Clear Data" button, should clear the contents of the Column C data.

Your output should be formatted to look similar to the screen shot below:

Notice the BOLD, Blue Font for the labels in column B, the centering of the data in Column C. Also notice the numeric formatting applied to cells C3 through C5 to display the values with no decimal places.

Save your file as an Excel macro-enabled workbook named as specified above and upload the solution to Blackboard. Be sure to SAVE the document BEFORE submitting!

Attachment:- Inventory Analysis.rar