mark_b2410 said:
I have a project to complete. I need to find the best allocation of
planes to maximise profit
There are 6 routes & 6 types of plane (3 Boeing, 2 DCs, 1 Airbus)
Can Excel automatically calcualte the best allocation?
Each flight has fixed and variable crew costs & variable fuel costs
Many people here correctly view the benefits of such assignments coming from
you figuring out how to do it yourself. The business world already has a
sufficient number of people who only know who to ask to get answers.
Be that as it may, I'll try to give help without giving away the solution.
First, profits = revenues - costs. You seem to have costs covered, but
revenues seem unspecified. Either your assignment also specified costs, but
you didn't include them, or part of the assignment involves coming up with a
pricing scheme. No way to help you with that without doing your work for
you.
Once you have a pricing scheme, convert it into formulas.
Then you could use brute force, calculating the profit from all possible
combinations of planes and routes. There are 60 such combinations, and as I
see it it's up to you to figure out what they are, but it's easy enough to
do using Excel. Put Route1 through Route6 in A1:F1, move to A2, Windows >
Freeze Panes, then enter 1, 1, 1, 2, 2, 3 in A2:F2, and enter the formula
=SUMPRODUCT(A2:F2,10^(6-COLUMN(A2:F2)))
in G2. Then make each row the next largest number using three 1's, two 2's
and one 3. So G3 would be 111232.
Once you have an exhaustive listing, apply the revenue and cost formulas in
each row, and calculate profits as revenues minus costs. You shouldn't need
help finding the largest profits figure.
Warning: this isn't the elegant solution, and you may lose points for not
figuring out the elegant solution. But that's up to you to find.
