Create a chart that plots profit, Strategic Management

Assignment Help:

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.


Related Discussions:- Create a chart that plots profit

Describe packaging or labelling features, 1. Describe the brand you will es...

1. Describe the brand you will establish across as many dimensions as you think are relevant 2. What will your brand association be?  i.e.  what is the one thing, above al

Open group architecture framework, Discuss the following quotation and ...

Discuss the following quotation and please provide examples. Enterprise Architecture is an organising logic for managing change within an Information Systems environ

Explain the different types of personalisation systems, Question: (a). ...

Question: (a). With the help of appropriate examples of your choice explain the different types of personalisation systems? (b). Distinguish between the micropayments and

Open communication , 1. Encourage open communication among team members to ...

1. Encourage open communication among team members to support team development 2. Demonstrate ways of resolving conflict among team members

Management strategic planning, You are an asset manager for a large public ...

You are an asset manager for a large public utility organisation. The organisation has a range of assets, ranging from major distributors to minor assets directly serving users (fo

SWOT, #quIf a SWOT analysis reveals that an objective is unattainable, apar...

#quIf a SWOT analysis reveals that an objective is unattainable, apart from changing the objective, what other changes might make the goal attainable? • Discuss the limitations of

Discuss the rationale for tfx company, TFX is a multinational company which...

TFX is a multinational company which manufactures and retails branded designer clothing with business units in a number of different countries globally. Up unless now, each of the

Calculate the dividend yield and total shareholder return, The following de...

The following details are available for company Z  · Share price at the beginning of the year £3.87 Share price at the end of the year £4.23 Dividend proposed and pa

Dissemination process to gain commitment, a)  Make measures for evaluating ...

a)  Make measures for evaluating a strategic plan. b)  Make a schedule for executing a strategy plan in an organization. c)   Make appropriate dissemination process to gain comm

What makes a strategy a winner, What Makes a Strategy a Winner? 1.   T...

What Makes a Strategy a Winner? 1.   Three questions are used to examine the merits of one strategy versus other & distinguish a winning strategy from a losing or mediocre str

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd