Need help in Python program and excel
MGMT 58600 Programming for Business Applications Lab 4 Lab Purpose: The purpose of this lab is to work with data files and Excel along with Python. Lab Instructions: Description: In building on a lab experience that you have had, your task will be to design, write, and implement a Python program that will check-in the cars that are being delivered in this week’s shipment and also calculate potential gross profit (and adjusted based on type of car), as well as totals for cost, MSRP (manufacturer’s suggested retail price), potential gross profit, and potential adjusted gross profit. In addition, you will also use a dictionary to match a commission code from the inventory shipping list to calculate a potential commission and then write the calculated amounts to a commissions spreadsheet (details will follow). Krannert Specialty Motors is a unique auto dealer that sells both brand new and “lovingly driven” high-end automobiles. Shipment check-in is an important part of the business operations as well getting a quick overview as to the profitability of the shipment and potential commissions that may be paid out. Your task will be to design, write, and implement a Python program that will check-in the cars that are being delivered in this week’s shipment and also calculate potential gross profit (and adjusted based on type of car). As your program executes, it should: 1. Prior to checking in the cars, your program should prompt the user to enter in any new cars that are not part of the original manifest (this could be the result of a newly added car at the last-minute and the info was sent via an e-mail or phone call). Your program should handle any number of added cars. The original manifest is stored in a file (see below for file location info) 2. After the manifest is updated, your program should read the updated manifest which includes: a. The car name (e.g., Red1969TriumphTR6) b. MSRP (manufacturer’s suggested retail price) c. KSM’s cost d. The code of the type of car (0 = domestic or 1 = import) for each car e. The code of the expected commission rate (A = 35% of adjusted potential gross profit, B = 25% of adjusted potential gross profit, and C = 15% of adjusted potential gross profit) 3. You should then process the file’s contents to determine the expected commission based upon the expected commission rate and the adjusted potential gross profit. a. Recall from an earlier program that the adjusted potential gross profit is the potential gross profit adjusted downward based upon the code of the type of car (for domestic cars, there’s no expected adjustment, but for imports we expect to have to adjust the gross profit downward by 1.75% based on last year’s margins). This must be calculated in a function where at least one parameter is passed to the function and a value is returned from the function. b. After calculating the potential commission, write the car name and potential commission amount to an existing, empty Excel spreadsheet (see below for file location info) 4. File locations referenced in the program should be: a. Original manifest text file (you will create this file yourself to test your code) i. Location - W:\MGMT58600\manifest.txt ii. Layout (each field is separated by a single blank space and each record is on its own line in the file) 1. The car name (e.g., Red1969TriumphTR6) 2. MSRP (manufacturer’s suggested retail price) 3. KSM’s cost 4. The code of the type of car (0 = domestic or 1 = import) for each car 5. The code of the expected commission rate (A = 35% of adjusted potential gross profit, B = 25% of adjusted potential gross profit, and C = 15% of adjusted potential gross profit) b. Excel spreadsheet (this will be created when your code executes) i. Location - W:\MGMT58600\commission.xlsx (the sheet name is commission) ii. Car name will go in Column A and potential commission will go in Column B Grading Criteria Element Points Proper use of function with return for calculating commission 15 Proper calculation of commission 15 Proper use of spreadsheet (including the writing of the car name and commission) 15 Program properly allows new car info to manifest 20 Program executes correctly and effectively 20 Comments 15 Total Points Possible 100