Microsoft Excel

Electric Car Design Optimization

Credit: this project was provided by Dr. Robert Ryan.

Overview

You are required to create an Excel workbook, supplemented with VBA sub and/or function procedures, which

will allow the user to simulate the acceleration of an electric car as it enters a freeway onramp. A summary of

the equations describing the forces on the car and the equations of motion are given in a following section. The

ultimate goal of the simulation is to identify the optimum design parameters in order to reduce the elapsed time

required to reach the end of the freeway onramp (acceleration distance 0.25 miles). We will consider three

design parameters: shift rpm, differential gear ratio, and number of gears in the transmission.

Assignment Requirements

You will be required to submit a report which contains the following items:

1. A hard copy of your Excel workbook, which will include:

A. User Interface Worksheet showing a program description, user instructions, inputs (design parameter values, car design constants, and other simulation constants), and outputs (elapsed time and final

velocity). The outputs should demonstrate that you have found the optimum values of the design

parameters.

B. Data sheet(s) showing proper graphs of car distance and velocity vs. time, and the engine dynamometer data (engine torque vs. RPM).

Note: Do not include hard copy of raw data sheets used for making the required graphs or other worksheets

you used (including any hidden sheets). These will be included in the workbook that you will submit to

Moodle.

2. A hard copy of the VBA code used to generate your results. Your code should include comments so that you could understand this code one year from now.

3. A written description using the following outline (2 pages maximum):

A. Introduction: Give a statement of the problem in your own words. Describe what the inputs are and the desired results. State the key equations governing the problem definition. Someone reading this section

should understand the purpose of your workbook without having to refer to the assignment handout.

B. VBA Code Description: Describe the general solution approach and the procedures used in its implementation. Describe your main sub procedure, and any VBA functions and user-defined functions

used in its implementation. You may find it useful to include a list of the key variable names to clarify

your discussion.

C. Optimized System Results: Describe the results obtained. What are the values of these parameters which produce the minimum elapsed time? Briefly discuss the sensitivity of the elapsed time to the

shiftrpm, differential gear ratio, and number of transmission gears (these parameters are defined and

discussed below). Does the elapsed time change significantly when these parameters are changed

slightly from the optimum? Graphs of the elapsed time vs. design parameter values help with this

discussion.

Definitions of Input Design Parameters

Symbol Definition

DGR Gear Ratio in Differential

Ngears Number of Gears in Transmission

SHIFTRPM Engine Speed for Shifting, rpm

Definitions of Principal Program Results

Symbol Definition

TRACE Elapsed Time to Finish Race, sec

VFINAL Final car velocity, miles/hr

Definitions of Car Design Constants

Symbol Value Definition

WRAD 14 Rear Wheel Radius, inches

TGRtop 1.0 Transmission Gear Ratio, Top Gear

TGR1 4.5 Transmission Gear Ratio, 1st Gear

CD 0.28 Car Body Drag Coefficient

A 30 Car Frontal Area, ft^2

WT 3000 Weight of Car, lbm

Definitions of Other Simulation Constants

Symbol Value Definition

RHO 0.00238 Air density, slugs/ft^3

DT 0.002 Time increment for calculations, sec

Definitions of Internal Program Variables

Symbol Definition

FDRAG Drag Force on the Car, lbf

ERPM Engine Speed, rpm

WRPM Wheel Speed, rpm

ETORQ Engine Torque, ft-lbf

WTORQ Wheel Torque, ft-lbf

WFORC Wheel Force, lbf

X Distance Traveled, ft

V Velocity of Car, ft/sec

TSR Transmission Shift Ratio (TGR2/TGR1, TGR3/TGR2, etc.)

TGR Transmission Gear Ratio

OGR Overall Gear Ratio

FNET Net force acting on vehicle, lbf

Assumptions

 The only forces on the car are due to the torque delivered to the rear wheels and the drag force due to air resistance.

 Friction forces, including rolling friction are negligible.

 Rotary inertia of drive train components are negligible.

 No slippage in drive train (engine-transmission-differential-wheels).

 Engine operated at constant (full) throttle setting.

 Gear shifts are instantaneous.

Governing Equations

The overall gear ratio (OGR) is the product of the differential gear ratio (DGR) and transmission gear ratio

(TGR):

OGR = DGR * TGR (EQN 1)

The transmission gear ratio depends on what gear is selected. The ratios for first and top gear (TGR1 and

TGRTop) are inputs to the program, as well as the number of gears (Ngears), which can be 2, 3, 4, or 5. It will

be assumed that the transmission is designed so that the ratio between gears is fixed by the total number of

gears, such that:

𝑇𝐺𝑅2

𝑇𝐺𝑅1 =

𝑇𝐺𝑅3

𝑇𝐺𝑅2 = … =

𝑇𝐺𝑅𝑛

𝑇𝐺𝑅(𝑛−1) = (

𝑇𝐺𝑅𝑡𝑜𝑝

𝑇𝐺𝑅1 )

1/(𝑁𝑔𝑒𝑎𝑟𝑠−1) (EQN 2)

Since there is no slippage in the drive train, the ratio of the engine speed (ERPM) to the wheel rotational speed

(WRPM) is determined by the overall gear ratio:

RPM RPM

E W

OGR  (EQN 3)

Frictional losses in the transmission and differential are ignored, so the torque delivered to the rear wheels

(WTORQ) can be found as a function of the engine torque (ETORQ):

WTORQ = ETORQ * OGR (EQN 4)

The engine torque is known as a function of engine rpm from a dynamometer test (see Table 1). A polynomial

function relating ETORQ to ERPM can be found by plotting torque vs. rpm in EXCEL and inserting a

polynomial trendline (use a polynomial fit of degree 2 or 3). Format the trendline to display the equation on

your graph in scientific notation (2 decimal places precision).

The force exerted by the wheels on the pavement is (ft-lbs and the wheel radius is in inches):

TORQ

FORC

RAD

W W

W 12

      

(EQN 5)

The velocity of the car (in ft/sec) is related to the wheel speed (in rpm) by:

RAD RPM

W 2 V W

12 60

      

   (EQN 6)

Table 1: Dynamometer Test Results for Engine at Full Throttle

Engine Speed, rpm Engine Torque, ft-lbf

0 166.60

500 212.10

1000 242.40

1500 282.15

2000 315.00

2500 328.30

3000 335.55

3500 322.20

4000 273.05

4500 224.75

5000 184.30

Thus the car’s motion can be described with the following differential equations:

=V dx

dt (EQN 7)

  F

WT

32.2

net dV

dt  (EQN 8)

Where: Fnet = WFORC – FDRAG (EQN 9)

𝐹𝐷𝑅𝐴𝐺 = 1

2 𝐶𝐷 𝐴 𝜌 𝑉

2 (EQN 10)

In Equation 10, 𝐶𝐷 is the car body drag coefficient, CD, and 𝜌 is the density of air.

To evaluate the differential equations, we can write the derivatives in finite difference form, where subscript

"i+1" refers to new value at each iteration of our simulation, and subscript "i" refers to the value at the previous

time step:

1

i i i i

i

dx x x x V

dt  



      

 

 

  1

=

32.2

net i i i i

i

FdV V V V

WTdt

 



       

In these equations, 𝛿 is the iteration time step, DT.

At the beginning of each simulation run, the car is at 𝑋 = 0, with an initial velocity of 𝑉 = 0. The throttle is increased to "full throttle", and Equations 1 through 10 are used to determine the increase in speed and distance

at each time step. Away we go!

Figure 1 shows a flowchart for the program logic. Basically the car accelerates from its initial position and

shifts to the next gear whenever ERPM reaches SHIFTRPM. When the total distance traveled is 1320 feet (1/4

mile), the elapsed time (TRACE) is output by the VBA code.

The goal of the project is to find the values of SHIFTRPM, NGEARS, and DGR which minimizes TRACE. It

is probably simplest to optimize one parameter at a time. That is, vary parameter 1 while keeping parameters 2

and 3 constant until you find the optimum for parameter 1. Then vary parameter 2, while keeping parameter 1

and 3 constant, etc. It may take more than one pass through the design parameters to ensure that you have

found the combined optimum set of parameters.

The output that you include in your report should prove that you have found the optimum values.

Figure 1: Program Flowchart