Create a chart that plots profit, Strategic Management

Excel is often used to perform "what-if" analyses. In these, a model that depends on a number of variables is constructed, and the outcomes predicted by the model are determined for a a range of values of the input variables.  The following matematical model describes the way in which the profit of a trucking company depends on a number of variables, including the price of petrol.

The input variables are:

1. the price of petrol in cents per litre, P;

2. the price the company charges its customers in dollars per km-tonne, T;

3. the volume of business carried out in a year, in km-tonnes, V ;

4. the rate of petrol consumption, R;

5. maintenance costs, in dollars, M;

6. the number of employees, E;

7. the average salary of employees, in dollars per year, S.

The company has no control over the price of petrol. It can decide what it will charge its customers and what the average salary of employees will be.  The volume of business carried out in a year will depend on the price charged to customers. The volume of business will determine the total price of petrol purchased during the year, the maintenance costs and the number of employees. The volume of business is given by

Vid should be the nal four digits of your student number, treated as an integer in the range [0, 9999]. So if your student number is 12345678 you should use,

 

for every 2.5 x 106 km-tonnes, or part thereof. This in turn determines maintenance costs, $12,000 per truck per year, and the number of employees, which is N + 4.

The volume of business also determines the amount of petrol consumed by the company, at the rate (R) of 0.2 litres per km-tonne.

The revenue collected by the company is VT.

The costs incurred by the company are the price paid for petrol, the costs of maintenance, salary costs and overhead costs of $60,000 per year.

The profit is the difference between the revenue and the costs. To perform the "what-if" analyses, you will need to do the following:

(a) Write a VBA function that will calculate the costs incurred by the company given P, T and S.

(b) Create a spreadsheet with cells where the values of T and S can be entered.  Beneath them construct a table with the column headings Price of petrol, Volume of business, Revenue collected, Costs incurred and Profit. In the first column, insert prices going from $1.00 to $2.00 in increments of five cents. Set up formulae in the remaining columns that will perform the required calculations.

(c) Create a chart that plots profit against the price of petrol.

(d) Test the spreadsheet using the values of  T = 0.40 and take the value of S from the first five digits of your student number. So if your student number is 12345678 take S = 12345 as the salary amount to use in your test.

Posted Date: 3/12/2013 1:28:26 AM | Location : United States







Related Discussions:- Create a chart that plots profit, Assignment Help, Ask Question on Create a chart that plots profit, Get Answer, Expert's Help, Create a chart that plots profit Discussions

Write discussion on Create a chart that plots profit
Your posts are moderated
Related Questions
If in a given year a company spends $2 million on new product development, design, and engineering for its entry-level camera line; $4 million on new product development, design, a

Q. Managing value based management? Strategic selection of projects that create high shareholder wealth. Resource allocation and funding should have a recognised oppor

The group consolidated financial results would normally be effected, if an internal buyer uses their autonomy and makes a decision to buy outside the group, rather than buy interna

Q. Behaviour of divisional senior executives? EVA can affect the behaviour of divisional senior executives in the following ways. 1.  They would concentrate their investment

1 -  Develop a plan for an e-strategy that  make sure  an organisation retains its competitive  benefit in a global market. 2 - Specify the technical infrastructure needed in an

Choose an academic article and produce an academic review in which you analyse, criticise, and reflect on the paper, of no more than 1200 words. The summary should include some def

What are the three important reasons why a GIS implementation might fail, according to Eason (1994)?   Ans) Organizational mismatch Non-usability User acceptability

what is the relationship between business strategy and operation strategy?

caselet in strategic management :National Competitive Advantage of IKEA Group, a Swedish company founded in 1943 with its headquarters in Denmark, is a multinational operator of a

Tatra is the world's third oldest car company, and produced a number of rather advanced streamlined cars during the course of its production run. Because spare parts and manuals ar