Instructions
Requirements and Specifications
- 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)
- After the manifest is updated, your program should read the updated manifest which includes:
- The car name (e.g., Red1969TriumphTR6)
- MSRP (manufacturer’s suggested retail price)
- KSM’s cost
- The code of the type of car (0 = domestic or 1 = import) for each car
- 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)
- 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.
- 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.
- 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)
- File locations referenced in the program should be:
- Original manifest text file (you will create this file yourself to test your code)
- Location - W:\MGMT58600\manifest.txt
- Layout (each field is separated by a single blank space and each record is on its own line in the file)
- The car name (e.g., Red1969TriumphTR6)
- MSRP (manufacturer’s suggested retail price)
- KSM’s cost
- The code of the type of car (0 = domestic or 1 = import) for each car
- 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)
- Excel spreadsheet (this will be created when your code executes)
- Location - W:\MGMT58600\commission.xlsx (the sheet name is commission)
- 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 |
Source Code
import pandas as pd
def menu():
"""
This function displays a menu to the user and then asks for a menu option.
If the option is invalid, the function will display the proper error
message and then reprompt
"""
print("1) Add new car")
print("2) Exit")
while True:
try:
option = int(input("Enter option: "))
if option in [1,2]:
return option
else:
print("Invalid option.")
except:
print("Please enter a valid menu option.")
def getnumber(message):
"""
This function is used to get a double input from user
If user enters an non-numeric value, the function
displays the proper error message and then reprompts
"""
while True:
try:
number = float(input(message))
return number
except:
print("Please enter a valid number.")
def getcommission(message):
"""
This function is used to get the correct code for
the expected commission rate
"""
while True:
code = input(message)
if code in ['A', 'B', 'C']:
return code
else:
print("Please one of the following options:", ','.join(['A', 'B', 'C']))
def gettype(message):
"""
This function is used to get the correct type of car
(0 or 1)
"""
while True:
try:
code = int(input(message))
if code == 0 or code == 1:
return code
else:
print("Please enter a valid type.")
except:
print("Please enter a valid option.")
def adjusted_potential_gross_profit(price, car_type):
"""
This function calculates the potential commission based on the type
of car (0 = domestic, 1 = import)
"""
commission_rate = 0.0
if car_type == 1:
commission_rate = 0.0175 # 1.75%
return price*(1.0-commission_rate)
if __name__ == '__main__':
# Define the locations of the input and output files
input_filename = 'W:\\MGMT58600\\manifest.txt'
output_filename = 'W:\\MGMT58600\\commission.xlsx'
# Define the dictionary that maps the code of expected commision rate to the value
commission_rate = {
'A': 0.35,
'B': 0.25,
'C': 0.15
}
# Define a list to store sublists of two elements, that will contains the name of
# the car and the total commission
commissions = []
# Begin program
while True:
option = menu()
if option == 1:
# Ask for car name
name = input("Enter car name: ")
# MSRP
MSRP = getnumber("Enter manufacturer's suggested reail price: ")
# KSM
KSM = getnumber("Enter KSM's cost: ")
# Code
code = gettype("Enter type of car (0 = domestic or 1 = import): ")
# Code for commission rate
code_commision = getcommission('Enter code for comission rate: ')
# Append to file
with open(input_filename, 'a') as f:
f.write(','.join(map(str, [name, MSRP, KSM, code, code_commision])) + '\n')
elif option == 2: # exit
break
# Now, process the file content
with open(input_filename, 'r') as f:
# Read all lines
lines = f.readlines()
# Now loop through lines
for line in lines:
line = line.strip()
# Split
data = line.split(',')
# Get name
name = data[0]
# Get MSRP
MSRP = float(data[1])
# Get KSM
KSM = float(data[2])
# Get type
car_type = data[3]
# Get commision code
code = data[4]
# Calculate potential gross profit
potential_gross_profit = MSRP
# Calculate adjusted potential gross profit
adj_gp = adjusted_potential_gross_profit(potential_gross_profit, car_type)
# Calculate expected commission
expected_commission = adj_gp*commission_rate[code]
# Calculate final cost
final_cost = MSRP + expected_commission
# Save car name and the commission
commissions.append([name, expected_commission])
# Now, write to the Excel file
data = {
'Column A': [x[0] for x in commissions],
'Column B': [x[1] for x in commissions]
}
df = pd.DataFrame(data)
df.to_excel(output_filename, sheet_name = 'sheet1', index = False)