+1 (315) 557-6473 

Python Program to Create Business Applications Assignment Solution.


Instructions

Objective
Write a python homework program to create business applications.

Requirements and Specifications

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:
    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)
  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.
    1. 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.
    2. 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:
    1. Original manifest text file (you will create this file yourself to test your code)
      1. Location - W:\MGMT58600\manifest.txt
      2. 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)
    2. Excel spreadsheet (this will be created when your code executes)
      1. Location - W:\MGMT58600\commission.xlsx (the sheet name is commission)
      2. Car name will go in Column A and potential commission will go in Column B

Grading Criteria

ElementPoints
Proper use of function with return for calculating commission15
Proper calculation of commission15
Proper use of spreadsheet (including the writing of the car name and commission)15
Program properly allows new car info to manifest20
Program executes correctly and effectively20
Comments15
Total Points Possible100

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)