+1(978)310-4246 credencewriters@gmail.com
  

Requirements: Students must choose an individual product or service ITEM (not a line or company) and undertake an analysis of the following:

1. A competitor and consumer behaviour analysis (20%)

2. Marketing mix analysis (45%)

3. Segmentation, targeting and positioning analysis (35%)

The analysis must be based in 2021/2022 and you must support your analysis with evidence from current academic and industry literature.

When choosing the product or service item, students must:

? Choose one country in which to base their analysis

? Ensure the product or service item is currently for sale in the chosen country

? Ensure there is sufficient supporting evidence available in the public domain to undertake the required analysis

Generic Feedback on MGT6046 Operations Management Exam
Question 1
Part (a) Critically evaluate the main strategic issues of Operations Management explaining why each
issue is important for a business.
[12 marks]
Part (b) What effect does innovation, diversification and product proliferation have on Operations and
Operations Management?
[13 marks]
[Total 25 marks]
Answers to this question tended to be descriptive. Passing answers tended to list many of the
relevant issues, however they failed to engage with these in a critical manner or highlighting the
inherent trade-offs that the performance objectives generate.
Question 2
Part (a) Why are Operations Managers interested in costs?
[12 marks]
Part (b) A company produces 1 million (i.e. 1,000,000) frozen pizzas per annum on a continuous
production line and sells the pizzas for £5.00 each.
The company has fixed costs of £250,000 per annum.
The variable cost per pizza is £3.75.
You are required to:
(i) Calculate the Break-even points as follows:
(a) In pounds Sterling (£s)
(b) In number of pizzas
(c) As a percentage of total sales per annum.
(ii) Calculate the expected profit for the year.
(iii) Calculate the margin of safety.
(iv) What would the unit variable costs need to be for each pizza for the company to make £1.5 million
profit? Comment on your answer.
Note: The Variable costs per pizza are given in this question.
[13 marks]
[Total 25 marks]
Answers to Part A tended to replicate elements from the lecture notes without integration of these
into a coherent argument as to why managers should be interested in costs.
Answer to Part B tended to be good however sometimes comments were limited
1
Question 3
Part (a) Discuss the concepts used in measuring the operational performance of a company and
explain why they are important for managing operations.
[10 marks]
Part (b) A small electronics company operates a single production line producing dashboard cameras
for the automotive market. The production line has 5 employees, working 5 days a week for 8 hours
a day at £12 per hour and the production line produces 30 dashboard cameras per hour.
The company sells the dashboard cameras per hour for £35 each.
Assume the company sells everything it makes and retains zero finished goods stock.
Table 1. Dashboard Camera Manufacturing Information
Item
Number of Employees
Working days per week
Working day length (hours)
Pay rate (£s per hour) per employee
Production rate (dashboard cameras per hour)
Sales price per Dashboard camera (£s)
Raw materials and assemblies cost (£s per week)
Overheads costs (£s per week)
Energy cost (£s per week)
Planned Downtime (hours per week)
Unplanned downtime (hours per week)
Details
5 employees
5 days per week
8 hours per day
£12 per hour
30 dash cameras per hour
£35 per dashboard camera
£20,000 per week
£4000 per week
£200 per week
11 hours per week
7 hours per week
Note: all calculations should be per week.
Calculate the following:
(i) The Design Capacity of the dashboard camera production line.
(ii) The Effective Capacity of the dashboard camera production line.
(iii) The Achieved Capacity of the dashboard camera production line.
(iv) The Single Factor Productivity:
(a) In dashboard cameras produced per Labour hour and;
(b) In dashboard cameras produced per pound of labour costs.
(v) The Multifactor Productivity for the production line.
(vi) Comment on the Profitability of this business and the sensitivity of the business to improved
downtime.
Answers to Part A should have included discussions on the Three Es of Efficiency, Effectiveness and
Economy. Many answers concentrated on utilisation and capacity issues which are only part of the
answer.
Answers to Part B in some cases contained errors in the calculations and the comments were often
limited or missing altogether. Some comments had no meaning for the business.
2
Question 4
Part (a) Critically evaluate the major differences between manufacturing operations and service
operations.
[10 marks]
Part (b) Discuss the concepts and ideas about Service Sabotage.
[15 marks]
Answers to this question tended to be descriptive. Passing answers tended to list many of the
relevant issues as presented in the lecture notes, however they failed to engage with these in a
critical or discursive manner. These answers showed some knowledge of the topics however they
did not develop arguments as to why services and manufacturing operations are different.
Question 5
Part (a) Identify and explain some of the major factors (not models) that affect location decisions. Use
examples to illustrate your answer.
[10 marks]
Part (b)
A distribution company delivers parcels for online shopping web sites. They have just been
approached by one company to deal with distribution in South Yorkshire and West Yorkshire. The
distribution company would like to set up a warehouse and distribution centre and have gathered the
data in the table below. The data shows the town locations and the weekly delivery demand in van
loads. Your job as the Operations Manager is to carry out the following:
(i) Draw a graph with all the locations in the table clearly shown. Label each point with their
coordinates.
(ii) From the data in the table determine the current most suitable location for the new distribution
centre using the centre of gravity method and add this point to your graph.
(iii) Determine the most suitable location for the new distribution centre if the weekly demand for
location A, Huddersfield, increases to 35 van loads to be delivered. Comment on your answer.
(iv) What would you recommend the company do?
Answers to Part A tended to identify some of the major factors affecting location decisions,
however many presented these as lists without explanation or reference to examples as the
question asked.
Answers to Part B were generally well done. Some answers lacked comments or in-depth
recommendations as to what the company should do. Some answers had no comments at all.
3
Question 6
Part (a) A company manufactures a device for household security using a five stage process shown in
the table below:
Stage
Processing time (minutes)
1
3
2
10
3
15
4
8
5
6
(i) Which stage in the process is the bottleneck?
(ii) What is the overall processing time for the item being manufactured?
(iii) What is the Idle Time on each stage of the process?
(iv) What is the Balancing Loss for this manufacturing process?
(v) If you could invest in another machine at the bottleneck stage in the process with a processing time
of 10minutes, what would be the new balancing loss for this manufacturing process?
(vi) Comment on your answer to question (v) above.
[12 marks]
Part (b)
A mail-order clothing company in Sheffield receives order forms; types in the customer details; checks
the information provided from the customers and checks that the products are in stock; confirms
payment and processes the order. The exact process can be listed as follows:
Order Processing in the Office
•
•
•
•
•
•
The order arrives by post and is opened by an operative in the office
The operative completes the company electronic order form with the customer’s details and
makes sure that all the details are correct and updates the company database
The operative then looks into the stock information kept on a database
If the item(s) are in stock then the order is processed through to the next stage with a debit
or credit card payment system.
If the item is not in stock then the operative checks the delay from the suppliers which is held
on another database and generates a message sent by email to the customer advising of any
delay in receiving the stock items. The operative then goes on to process another order.
Suggest some improvements to the process flow diagram
Answers to Part A were generally well done. In some cases, the comments lacked depth or were
missing altogether.
Answers to Part B were generally well done however some contained errors in the Process Flow
Diagram. In some cases, the comments were limited or missing altogether.
4
Question 7
Part (a) A company manufactures a hand-held blending machine for domestic kitchen use and sells
11,000 units per annum, given the following information about the hand-held blending machine you
should advise if the company should make or buy the hand-held blending machine. Please show all
your workings in calculations and comment on your answer.
VC = Variable cost/unit if produced by the manufacturing company = £22.00 per unit
P = Purchase Price/unit, if purchased from a supplier = £25.00 per unit
FC = Fixed costs associated with producing the part or item = £120,000
Q = Quantity produced per annum (forecast) = 11,000 units per annum
[8 marks]
Part (b) A manufacturer produces three types of outdoor walking boots in its factory: Weekend
Walker, Westwood and Peak Walker (see the table of data below). There are 900 assembly hours
available each week. Note: a unit is a pair of boots.
(i) How long does it take to assemble 14 pairs (units) of boots?
(ii) How many pairs (units) in total are produced each week?
(iii) How many pairs (units) of each type of boot (i.e. Weekend Walker, Westwood and Peak Walker)
are made each week?
(iv) Comment on your answer.
Boot Type
Weekend Walker
Westwood
Peak Walker
Assembly Time (minutes)
3
4
5
Current Demand
Ratio
7
4
3
[9 marks]
Part (c) A manager of a company providing a printing and copying service for local businesses has
collected the following data:
Utilisation (U) = 70%
Service Rate (SR) = 8 printing jobs per hour per employee
Demand Rate (DR) = 30 jobs per hour
Calculate the following:
(i) The number of employees required to achieve a utilisation of 70%.
(ii) The number of employees required to achieve a utilisation of 80%.
(iii) If the number of print jobs required per hour increases to 50 jobs per hour during the busiest time
of year and the utilisation remains at 80% how many employees are required?
(iv) Comment on your answer.
[8 marks]
Answers to this question tended to follow the format laid out in the Tutorial examples and were
generally well done. Some answers contained errors in calculations. Comments were sometimes
limited in their depth or missing altogether. The use of critical evaluation was generally missing and
there were few comments, analysis or recommendations for the businesses.
5
Question 8
Part (a) Why are some projects regarded as failures?
[10 marks]
Part (b) A Project has been proposed according to the following table.
Table of activities, durations and resources required.
Activity
Duration (days)
Predecessors
A
B
C
D
E
3
5
4
2
7
Start
A
A, B
B
C, D
People required for each
activity per day
2
4
5
6
2
Given the information in Table (above) carry out the following:
(i) Draw a precedence diagram for the project and list all the paths through the project.
(ii) Using the activity on node method calculate the earliest start, earliest finish, latest start, latest
finish and the total float on each activity. What is the overall duration of the project?
(iii) Determine the critical path(s) for the project.
(iv) Draw a Gantt chart with activities starting as late as possible.
(v) Draw a resources (manpower) histogram for the project.
(vi) Comment on your answer.
Answers to Part A tended to list some the factors discussed in class and course notes. Most answers
would have benefitted from using appropriate examples to support the points being made.
Answers to Part B tended to be well done. Some answers missed out the full Critical Path Analysis
diagram and had limited or no comments.
Overall Comments for Students:
The use of an ‘open book’ examination does not mean that students can copy and paste verbatim
directly from course notes or other sources and achieve high marks. This is a use of unfair means (i.e.
cheating on the examination).
The markers did not wish to read their own lecture notes and course notes (without proper citation)
in the submitted examination file.
What was expected was that students would engage critically with the materials provided, extend,
and develop their own learning from reading recommended textbooks, guided reading and other
research and then write in their own words what they thought constituted an answer to the
question(s) set. Such an answer would have been welcomed and marked with pleasure.
It should be noted that students made a declaration that the work submitted was their own work.
However, in a very large number of cases it was instantly apparent to the markers that course
materials had be copied and pasted verbatim. Normally, this would have resulted in a disciplinary
action over the use of unfair means (i.e. plagiarism).
6
The Numerical Questions: This was not a mathematics test and simply obtaining the right numerical
answer was not enough for full marks or even half marks.
The module leader emphasised throughout the module that comments and analysis were necessary
to explain what the implications were for the organisation and that decisions and recommendations
should be made for improving and changing the business operations. No one ever presents a
calculation on its own to a senior manager, some explanation is always required.
The markers wanted to know what your thoughts were on the situation in the examination question.
Again, the markers did not wish to read standard answers without comments copied from course
materials. Such answers copied from course materials did not add value or demonstrate
understanding of the results of the calculations and the implications for the business.
Recommendations and decisions for the business were not properly developed in most answers. The
implications of those decisions were not commented upon in many instances.
The Short Essay Style Questions: These were an area where students could engage critically with the
material and expand upon the materials taught (by using textbooks and other sources). This would
have gained a very good mark if the student had written a decent answer in their own words. Again,
the markers did not wish to read standard answers copied and pasted from course materials. Such
answers were seen as a short-cut and missed the whole point of the examination and were necessarily
given low marks.
The point of the examination was to see how the student had engaged with the material taught and
had critically evaluated the materials and written in their own words.
Conclusion
Students have all now received an email with a warning letter about the use of unfair means on this
module.
It is regrettable that students chose to use unfair means by copying and pasting verbatim without
attribution from course materials.
This is a point to take away from this module: It is wholly unacceptable to copy and paste verbatim
from course materials and other sources and then state that this is your own work when clearly it is
not your own work.
Please try to learn from this experience and try to be more careful in the future. Use your own words
and critically engage with the course materials for each module.
7
How to do Numerical Questions and get a Good Mark
Read the question, what is it asking you to do?
What data does the question provide? Does this give you a clue to the type of analysis
to be done and the equations to be used? If not, read the question again paying
particular attention to what you need to do.
Choose the right equation(s) from the formulae sheet.
Write down the equation(s), write down the data available in the question (e.g. variable
cost VC = £35)
Manipulate the formula to obtain the thing you are trying to calculate.
Add in the data, do the calculation on a calculator, write down the answer.
Does it look correct? Do the calculation again to check. Is that correct? Have you used
the right data in the right way?
What do the figures obtained tell you about the business? What do they mean? This
is where the critical evaluation comes in and where the theory can be applied to the
question.
Are there any frameworks or theories that can guide you on what to say? If so, write
the relevant bits down and demonstrate how it applies to this question answer.
What are the trade-offs between the ideal figures and the numbers obtained in the
calculation? Is the company improving its performance? If so, why? If not, why? What
are you going to recommend to the management of the business?
Are there any examples from the news, the literature, the class examples, or your own
experience to explain some aspect of the business?
Can the question answer be enhanced with an additional calculation (not necessarily
explicitly asked for) that will tell you something useful? If so, do the calculation and
critically comment on the answer in the context of the business.
Write down what you have found out as if reporting to the company or a superior in
the company. Point out problems, issues and potential solutions based on your
analysis. Do not assume a number on its own will be good enough. The numbers will
need to be placed in context.
If the analysis requires a year 1 and year 2 comparison (or comparison across several
years or several production lines) then suggest why the figures have changed from
year 1 to year 2 or are different between production lines etc. Have the costs changed?
Have the number of items made changed? Have there been some other changes (e.g.
change of equipment, better marketing, more or fewer staff, more raw materials used
and stored or more finished goods stock, increased/decreased waste etc.) explain
these changes and any operational issues associated with the changes.
What are you recommending the business do? Write this down in clear English
perhaps using bullet points.
The following example provides some insight into the thinking processes required to
obtain a good mark on a numerical question. There are side notes to illustrate how the
above points are considered at appropriate points in the calculation. This is the general
format/approach of the Homework Questions with Answers and students are
encouraged to use these questions.
1
Worked Example 1.
A company produces a premium priced decorative cake topping (e.g. silver balls). The
major inputs used in the production process are labour, raw materials and energy. For
year one, labour costs are £15,000; raw materials costs £2,000 and energy costs
amount to £500. Labour costs for year two are £25,000; raw materials cost £3,000 and
energy costs amount to £600. The company produces 9,000 kg of decorative cake
topping in year one and 13,000 kg of decorative cake topping in year two. Calculate
the productivity of the company over the two years for each year. Comment on your
answer.
Table of Data
Labour Costs
Raw Materials Cost
Energy Costs
Production Amount
Year 1
£15,000
£2,000
£500
9,000 kg
Year 2
£25,000
£3,000
£600
13,000 kg
Note: sometimes the data is written in the question or shown as a table. If it is written
in the question, it is useful to produce a table of your own like the one above.
Answer:
Note: The question is explicitly asking you to calculate the productivity of the company
over the two years for each year. Start with single factor productivity as this is easiest
to do. Use Amount made per £ of labour cost (other data may allow amount per labour
hour). Write the equation down from the formulae sheet.
Single Factor Productivity
Single factor productivity = Output from the operation
One input to the operation
Year 1
Amount of product in kg made per pound of labour cost is: [Write the equation down]
Single factor productivity = Output from the operation = kg of Product made
One input to the operation Labour costs
Note: add in the numbers from the table for year 1
Single factor productivity = 9,000kg = 0.6 kg of product per £ labour cost
£15,000
Note: does the calculation look right? Redo the calculation if unsure.
Year 2
Single factor productivity = Output from the operation = kg of Product made
One input to the operation Labour costs
Single factor productivity = 13,000kg = 0.52 kg product per £ labour cost
£25,000
Note: does the calculation look right? Redo the calculation if unsure. There is an
obvious difference between year 1 and year 2, what has happened? Costs have
risen disproportionately compared to the amount produced.
2
Note: There is enough information in the question to do both single factor and multifactor productivity. The figures are already in £s so no conversion required.
Multifactor Productivity
For Year 1.
Note: Using Multifactor productivity, all inputs must be converted into a common unit
of measure, typically cost (in £s). Thus, we have [Write down the equation]:
Multifactor productivity = Output from the operation (in kg)
All inputs to the operation (in £s)
Productivity = Quantity of Output =
Quantity of all Inputs
=
Output in kg
(Labour + Raw Materials + energy in £s)
9,000 kg
(£15,000 + £2,000 + £500)
= 0.514 kg per pound (£)
Note: does the calculation look right? Redo the calculation if unsure.
For Year 2.
Productivity = Quantity of Output =
Quantity of all Inputs
=
Output in kg
(Labour + Raw Materials + energy in £s)
13,000 kg
(£25,000 + £3,000 + £600)
= 0.454 kg per pound (£)
Note: sometimes the “Critically Evaluate your answer” terminology will be used instead
of “Comment on your answer”. It is generally accepted that these mean the same thing
to English people. What is the point of commenting on your answer if it is not critically
evaluating the situation with the numbers you have calculated? Critical evaluation is
the default position and is expected at Masters level.
Comments [The Critical Evaluation]:
Changes from Year 1 to Year 2 are:
Single Factor Productivity: Down by 13.3% (i.e. minus 13.3%)
(New – Old)/Old expressed as a percentage of the year 1 is:
(0.52 – 0.6)/0.6 = -13.3%
Multi-factor Productivity: Down by 11.67% (i.e. minus 11.67%)
(New – Old)/Old expressed as a percentage of the year 1 is:
(0.454 – 0.514)/0.514 = -11.67%
•
•
•
•
Labour costs have increased: + 66.67% expressed as a percentage of Year 1
(i.e. (£25,000 – £15,000)/£15,000 = 0.6666 = 66.67%
Labour costs have increased as a proportion of overall costs: Year 1 is 85.71%
and Year 2 is 87.41%
Raw materials costs have increased: +50% expressed as a percentage of Year 1
Energy costs have increased: +20% expressed as a percentage of Year 1
We can see that single factor productivity has declined by 13.3% (minus 13.3%) from
year 1 to year 2 and multi-factor productivity has declined by 11.67% (minus 11.67%)
from year 1 to year 2 expressed as a percentage of year 1 multifactor productivity.
This might warrant an investigation. [Note: we can say “These figures warrant an
3
investigation” in our short report to management. This is a perfectly reasonable thing
to say when something has been reduced to less than the previous year’s figures (e.g.
profit, productivity, efficiency, utilisation, quality, etc.)].
Note: We can do much of the investigation ourselves:
Such an investigation would reveal that the reason for the relatively higher increase in
costs compared to the amount of output produced is that the labour costs have
increased disproportionately (up by 66.67% on year 1 costs), while raw materials costs
have increased by 50% (on year 1 costs) and energy costs have increased by 20%
(on year 1 costs). However, labour costs are the major component of the costs of the
inputs and the increased labour costs have reduced overall productivity for the
manufacture of decorative cake topping (silver balls).
Note: this is an excellent answer. Can we do better? Yes, we could give an example
from a similar situation in the news. For example, we know that the price of butter (a
raw material) went up by about 4 times in 2017 and this influenced the productivity
figures of biscuits and croissants manufacturers as well as the profit at the companies
(Obviously it must be a real business and have real business problems. See:
).
How are the Marks arrived at?
•
All calculations are correct, using the correct equations, will get about 50% of the
marks.
•
Further numerical analysis, with explanations, as part of the Critical Evaluation will
get about 25% of the marks.
•
Changes and analysis from one year to the next, with suitable critical evaluation
and comments, conclusions and recommendations will get about 15% of the marks.
•
A good example properly explained might add 5% to the marks.
•
An insightful comment or recommendation to management will get the rest of the
marks.
•
Everything presented must be clear, clean, and tidy.
Note: Conclusion: Now we know what is expected and where the marks come from
we can set about making our answers better to numerical questions in examinations.
Worked Example 2.
A pizza making machine is designed to operate 24 hours a day, 6 days a week, but
has planned downtime of 16 hours per week due to product changeovers and
maintenance. In addition, there is a further loss of output due to quality concerns and
material shortages totalling a further 15 hours per week. Calculate the following for the
pizza making machine:
(i) Design capacity
(ii) Effective capacity
(iii) Achieved capacity
(iv) Machine utilisation
(v) Efficiency
4
Answer
Note: This looks like a typical part (a) of a longer question. Usually about half of the
marks are given in part (a) and half of the marks are given in part (b) so we must divide
our time appropriately in the examination.
Note: The question does not specifically ask for ‘Critical Evaluation’, but we are going
to do that to obtain as many marks as we can in the limited time available.
Note: the calculations can be done in hours and achieve the same figures for efficiency
and utilisation. The data is easy to handle so no need for a table in this instance.
(i) Design capacity = 24hours x 6 days per week = 144 hours per week
(ii) Effective capacity = Design capacity (144 hours) – Planned downtime (16 hours)
Effective capacity = (144 – 16) = 128 hours per week
(iii) Achieved capacity = Effective capacity (128 hours) – Avoidable downtime (15
hours)
Achieved capacity = (128 – 15) = 113 hours per week
Therefore:
(iv) Machine utilisation = Achieved capacity/Design capacity
Machine utilisation = (113/144) x 100% = ~78.5% (78.47%)
(v) Efficiency = Achieved capacity/Effective capacity
Efficiency = (113/128) x 100% = ~88.3% (88.28%)
Note: the calculations are quite easy and quick to do but do not stop there.
Comment [The Critical Evaluation]
Both figures, utilisation (78.4%) and efficiency (88.3%) are relatively high for a
manufacturing business. This is good news for the business given that the unplanned
or avoidable downtime seems to be relatively high (15 hours per week for quality
concerns and material shortages). The planned downtime (product changeovers and
maintenance) spread over six days is reasonable at approximately 2.7 hours per day
(11.25% of the time) when running a machine or operating a business twenty-four
hours a day with three shifts.
However, the figure for the avoidable downtime is quite high (15 hours per week) but
could be better. Specifically, the quality issues should be addressed and corrected,
and the purchasing of raw materials done in such a way that the facility does not have
shortages of raw materials or stock outs. There should be a raw materials store which
should have a re-order quantity, re-order level system with a safety stock to allow the
production to be continuous. This would improve the machine utilisation and the
efficiency of the operation and increase the productivity and profit for the business.
Note: An excellent short answer question which should gather most if not all the marks
for a simple part (a) question. Look how little space this answer takes up and yet we
have just earned 12 or 13 marks out of 25 marks available.
Marking: The calculations are correct (say 6 marks out of 13 marks available); the
comments and critical evaluation are very good (another 6 marks out of 13 marks
available). No example given, no marks. Total score is 6 + 6 = 12/13 marks. Repeat
this performance in part (b) and it is a potential 24/25 marks answer. Excellent!
5
MGT137 & MGT138
Excel practice exercise A –step by step guide.
The exercise is to enter the raw data below into a spreadsheet …
… and then to use Excel’s capabilities to produce the output …
Document1
Page 1 of 18
MGT137 & MGT138
INTRODUCING EXCEL
To practise some Excel techniques you will create a workbook. An Excel
workbook can contain several worksheets. The default number of
worksheets within one workbook is usually three. The instructions on the
next pages take you through the setting up step by step. Sometimes there is
more than one way to achieve the desired result, or even a quicker way, but
this tutorial is designed to cover several different techniques.
Your worksheets within the workbook do not have to look exactly the same
as the pictures shown, but for ease of following the instructions you would
be advised to use the same cell addresses.
A worksheet layout will vary depending on the contents. A good design is to
have a title and perhaps some text to indicate the purpose of the worksheet.
Below that is a block containing any parameters required, and below that is
the main calculation area where the formulas refer to the parameter block by
absolute referencing.
Title
Parameter block
Calculation section
Document1
Page 2 of 18
MGT137 & MGT138
1.0 Example 1
Useful Techniques
In this section you will be introduced to some useful Excel techniques. In
order to practice these you need to enter a few details into a worksheet.
They are to do with a fictitious hot drink vending machine.
1.1 Enter Details
Firstly you need to enter these given details into your first worksheet.
Any numbers that will be used in a calculation must be entered into their own
cell and not mixed with text.
1 Cell A1 should already be selected (with a border around it). If not, point
to cell A1 and click the left mouse button to select the cell.
2 Type
Drinks
and press Enter or click the enter button
3 Now enter the other details as shown in the picture.
(Note Some text will overflow the cell. You will learn how to deal with this in the
next section.)
1.2 Alter Column Width
Sometimes text overflows from one cell into another. If there are no details
in the adjacent cell then this can be OK, but in our example you need to alter
the width for all columns.
Column reference
letter
Document1
Border line
Page 3 of 18
MGT137 & MGT138
1 Move the pointer to the border line between column references A and B.
The pointer changes to a ï‚«
| .
2 Depress the left mouse button and drag the border across to the right
until the text in column A fits into the cells.
3 For best fit you double-click the border line, so use this method to widen
columns B, C and D.
1.3 Simple Formulas
Now you need to enter the formulas to calculate the amount received for all
drink types.
To enter a formula you must first select the cell in which you want the result
to be displayed. The formula must begin with an equals sign (=) and may
contain cell references, numerical constants, or pre-defined Excel functions.
Remember that in order to use spreadsheets properly
use cell references rather than the numbers.
For InformationOnly Some examples of formulas
Addition
=A1+A2
Subtraction
=A1-A2
Multiplication
=A1*A2
Division
=A1/A2
Power
=A1^2
Functions
=SUM(A1:A4)
(use the caret symbol ^)
=AVERAGE(A1:A4)
(Note Excel uses the colon symbol to express a range e.g. =SUM(A1:A4) is A1+A2+A3+A4)
1 Select cell D2 into which you want to enter the formula to calculate
amount received for tea.
2 Type the following
3 Click the enter button
=B2*C2
or press Enter. The result will be shown in cell
D2.
1.4 Using the AutoFill Handle to copy to adjacent cells
Rather than enter a similar formula for the other drink types, Excel has
provided a way to copy a formula quickly to adjacent cells.
The AutoFill
Handle at the bottom right hand corner of a selection is used to do this.
1 Select cell D2.
Document1
Page 4 of 18
MGT137 & MGT138
2 Point to the AutoFill Handle that appears in the bottom right hand corner
of D2
(the pointer changes to a + ) depress the left mouse button and drag the
handle down to cell D5. Release the mouse button.
3 Select cell D3 and look at the formula in the formula bar. Notice that
Excel has dealt with the relative addresses while copying, and the correct
formula of
=B3*C3 is there,
giving the correct result of £95.00.
1.5 Calculate total amount received using the SUM Function
You will now calculate the total amount received. You could do this with the
formula =D2+D3+D4+D5. However, it is better to use the Excel function
“SUM” – especially if you are adding a large number of values. This uses a
function called SUM.
1 Click the insert function button fx and search for SUM
Document1
Page 5 of 18
MGT137 & MGT138
Click OK and choose the range of cells to sum:
1.6 Functions
There are a lot of pre-defined functions within Microsoft Excel. When you
click on the arrow beside the AutoSum button you are offered the most
common functions (Average, Count, Min and Max) in the list, and a
connection to More Functions.
Calculate the average Number Sold
You can now calculate the average number sold using the function Average.
1 Select cell C8 where the average sold result needs to be displayed.
2 Click the insert function button again and choose Average.
Excel will show a moving border around the cells that it thinks you want to
use for the average calculation. Empty cells are ignored so it does not matter
if the range is C2:C7.
3 Click the Enter key and the result is shown in cell C8.
Document1
Page 6 of 18
MGT137 & MGT138
1.7 Dynamic Nature
You will now change the contents of a significant cell (say B2), and then the
formulas that refer to that cell respond immediately and automatically update
their displayed results.
•
Alter the value in cell B2 to £0.40
Notice that the result in cell D2 has changed and also the total received in
cell D7.
1.8 Save the workbook
It is a good idea to save your workbook regularly.
1 Click the Save button on the toolbar
You are offered the Save As dialog box where the default file name is book1.
2 You need to type in a suitable name such as
MGT138_myfilename_15_Oct_2014
3 Check that you are saving in the correct folder.
4 Click the Save button.
1.9 Moving and Copying Data
When moving a large number of data cells or transferring data between
worksheets or applications you should use the Windows Clipboard. Select
the cells and click the Copy button. Then you need to select just the top left
cell where you want to paste the selection, and click the Paste button. If the
moving dotted line continues to show press the Escape key on the keyboard.
For a small amount of data the easiest way to move it is by using the Drag and Drop
technique.
1 Select the cells A1 to D8 and point to the border of the selection.
2 When the pointer changes to a 4-headed arrow, drag the selection and drop it
into
cells A7 to D14.
(Note:
You can also copy a small amount of data by holding down the CTRL key while using drag and
drop.)
Document1
Page 7 of 18
MGT137 & MGT138
1.10 Absolute References
In section 1.4 you learnt how to copy formulas to adjacent cells using the
AutoFill handle. In that example Excel intelligently altered the cell references
in the formulas according to the new position on the worksheet (relative
reference).
If part of a formula must reference the same cell no matter where on the
worksheet the formula is copied, then those cells need to be made absolute
references.
To create an absolute reference, you need to insert a $ sign
before the column letter and/or row number you need to fix (e.g. $D$4
$D4 D$4).
(Note: To quickly get the dollar signs, place the cursor within the cell reference and press
the Quick key F4.)
To practice using absolute referencing (and the techniques covered in the
next sections) you need to add some more details to your worksheet.
The supplier of the vending machine also supplies the packs of plastic cups,
each of which already contains the relevant drink powder or tea bag. The
cups come in packs of 20.
You need to calculate how many packs are
required for each drink type.
1 In the last section you should have moved all of the details entered so far
down to start in row 7 as shown in the picture.
2 Enter the details shown in rows 1, 3, 4 and 5. Also the two extra titles in
cells E7 and F7. Widen columns as necessary.
Now enter the formulas to calculate the number of packs required for each
drink type as number sold divided by number of cups per pack.
Document1
Page 8 of 18
MGT137 & MGT138
3 In cell E8 enter the formula =C8/$B$3
The reference to cell B3 must be made absolute by using the dollar signs as
shown before you copy the formula down for the other drink types.
4 With cell E8 selected copy the formula down for the other drink types
using the AutoFill handle in the bottom right corner of the cell.
(Note Your results may not be a whole number but do not worry. There is a function called
ROUNDUP that could be used to round a number up to the nearest integer, but you
do not need to try it now.)
Document1
Page 9 of 18
MGT137 & MGT138
1.11 Insert a Row and Sort
To insert select the row number you wish to insert before, or the column
letter.
(Note: If you want to add a row or column of details to a list of data you are advised to
insert a row/column within the list rather than at the beginning or end. This is so
that the new details are included in any totals etc. When you insert within the list,
Excel will intelligently deal with the cell referencing.)
You should insert a row before the one for coffee, which should be row 9.
1. Right click on a cell in row 9, and from the Insert menu, choose Row.
2. Enter details for a new type of drink as
Drink Type
Cost
per Number
cup
Sold
Lemon tea
£0.50
80
3. Copy the formulas down for Amount Received and Packs Required.
To sort a list quickly select the “Data” tab and then you select a cell in the
column you want to sort by and then click the Sort Ascending
Descending
button.
or Sort
Excel will sort all the rows in the list, except
column labels.
4. Select just one cell within the list of drink types in column A (say A9) and
click the Sort Ascending button. Remember the Undo button is there if
you need it.
1.12 Decision Functions
There are some functions in Excel that are useful when a decision is required.
They include the IF function, and conditional functions COUNTIF and SUMIF.
The IF function is explained here.
Document1
Page 10 of 18
MGT137 & MGT138
The IF function is used when you want a formula to return different values
depending on the value of a condition.
A condition is constructed using the comparison operators
=
Equal to
< Less than >
Greater than
=
Greater
than
or
Not equal to
equal to
The
syntax
for
a
formula
using
the
If
function
is:
=IF(condition,value_if_true,value_if_false)
For example
a)
=IF(C120,$B$1,$B$2)
Returns the value stored in cell B1 if value
of C12 is greater than zero, otherwise
returns the value stored in cell B2.
c)
=IF(D8
Purchase answer to see full
attachment

  
error: Content is protected !!