Assessment Brief
You work as a junior accountant and financial analyst for Alset plc in London. You have been
working for the firm for six months. Your line manager has been pleased with your work to
date and is looking to assign you more responsibilities. She has asked you to complete a
series of tasks to test what you can do.
All your solutions are to be done on one excel file. Please download the “MBF Summative
data.xlsx” excel file from the summative assessment area of the Hub. Please complete your
details on the first tab of the spreadsheet and then answer the task questions. The
spreadsheet has a Cover Sheet tab and then ten other tabs, one tab per question. Each task
is worth ten marks and will have one or more parts associated with it. Where relevant, data
has been pre-loaded into the relevant spreadsheet tab to save you time. For written parts
please just enter your textual answer as needed in the tab. Make sure that all your work is
visible and steps in your answer are appropriately labelled.
You must submit your answer spreadsheet with the following name format:
[Student number]-MBF-Summative.xlsx
For example: BP0123456 – MBF Summative.xlsx
Please note that if you do not use this format then your paper may not get marked. Please
do not submit in any other form than Microsoft Excel, otherwise your paper will not be
marked and it will be considered as a non-submission. Ensure also that you explain your
step-by-step working in your spreadsheet: Do not assume that your marker is able to access
the underlying formulae in your spreadsheet. Unless otherwise advised provide answer to
two decimal places.
Please note: All names used in this paper are purely fictitious. No identification with actual
companies or people is intended or should be inferred.

Task 1 – Investment (1)
Scenario: American investment fund, Muskman plans to lend $1m at an annual interest rate
of 9.5% for five years to a UK gene tech start-up company called Enegone Limited.
Muskman has fixed its initial exchange rate to 1.1 dollars to the pound and the initial
principal will be converted into pounds at that rate. There are no stage payments involved
in the deal. Give your answers below to the nearest penny.
(a) What will be the pound-value of the loan at the start of the loan period?
(1 mark)
(b) What will be the pound-value of the loan at the end of the loan period?
(3 marks)
(c) If Muskman had fixed its forward exchange rate at the end of the loan period again to 1.1
dollars to the pound what would be the nominal dollar value of the loan be to Muskman?
(2 marks)
(d) If Muskman had not fixed its forward exchange rate, in what exchange rate circumstance
would their actual loan value go up?
(1 mark)
(e) Again, if Muskman had not fixed its forward exchange rate, in what exchange rate
circumstance would their actual loan value go down?
(1 mark)
(f) Identify two other situations in which Muskman’s loan return might be worth less than
originally anticipated.
(2 marks)

Task 2 – Percentages and Loan appraisal
(a) The data for this task is preloaded into your Excel workbook. Boldman Bachs Bank is
deciding whether to grant a loan facility to a new customer. As part of the analysis, calculate
the percentage change for each of the headline numbers in the summary table in your
workbook. Give your answer to the nearest percentage point.
(7 marks)
(b) Based on the percentage changes should Boldman consider lending to this new
customer (yes, no)?
(1 mark)
(c) The government has released the latest retail price index which suggests inflation was
8.6%. A typical basket of food at the supermarket this week costs £130.34. Calculate the
cost of that typical basket (i) a year ago and (ii) one month ago (assuming inflation occurs
evenly throughout the year).
(2 marks)

Task 3 – Algebra
The data for this task is preloaded into your Excel workbook. Ladil Supermarket has ordered
three own-brand health-food cereals from a particular supplier over the last month, as listed
in the workbook.
(a) Complete the table by filling in the cells marked in yellow.
(3 marks)
(b) A new Chinese supplier has approached the supermarket, offering the same items at
35% less than the overall price that the supermarket has paid. If the supermarket accepts
the offer what would be the equivalent price from the new supplier?
(1 marks)
(c) Expand the following expression:
(3x – 5y) (2x + 7y)
(2 marks)
(d) Factorise the following expression:
36a
3b2 +6a2b3
-18a
2b
(2 marks)
(e) Factorise the following expression:
AxBx + AxBy + AyBx – AyBy
(2 marks)

Task 4 – Statistics
The data for this task is preloaded into your Excel workbook. This data relates to the weight
(in grams) of a sample of 100 chocolate cakes produced on a cake factory production line
during a daily batch run of 10,000 items produced.
(a) Use the Excel functionality to calculate the statistics below for the cakes:
i. Heaviest, lightest and range of weights of the sample
ii. The mean of the sample
iii. The standard deviation of the sample
(3 marks)
(b) Aggregate this data into a suitable frequency table using the COUNTIFS function and
using an interval of 4
(3 marks)
(c) Plot a histogram of the data.
(2 marks)
d) The production manager is concerned that the machine is not working properly as the
items of output vary in weight. Cakes should weigh an average of 100 grams (with a +/- one
gram tolerance) with a standard deviation of a maximum of 2 grams. Comment briefly if the
production manager is right to be concerned. Use the statistical data calculated in part ‘c’
above to support your answer.
(2 marks)

Task 5 – Visualising and charting data
The data for this task is preloaded into your Excel workbook. Snowdonia Cycle Trekking
(SCT) provides a range of cycle tours in North Wales. Clients come from across the world for
different reasons. Sometimes they come to see the sights of North Wales, others come to
experience traditional Welsh food, and others want to use the mountain trails as an
extreme mountain-biking sports and fitness training. SCT caters to these different markets.
The table in your workbook show the bookings for September 2022.
(a) Produce a stacked vertical bar chart to show the number of bookings by home country
(bars) across the service types (bar segments)
(3 marks)
(b) Produce a clustered horizontal bar chart to show the relative popularity of the different
services (bars), broken down by country (bar segments).
(3 marks)
(b) Produce a pie chart to show the percentage of bookings by service. Include percentage
data labels on your pie chart, to the nearest percentage point.
(2 marks)
(c) Interpret your results. Which is the most popular service? Which country is the best
market for SCT?
(2 marks)

Task 6 – Manipulating data in Excel
The data for this task is preloaded into your Excel workbook. A major car manufacturer has
collated data on three of its main competitors – BMW, Cadillac and KIA. An extract of the
data is provided in your workbook. Using this data, complete the following tasks:
(a) Use the “COUNTIF” function to count the number of cars manufactured by BMW
(2 Marks)
(b) Use the “COUNTIFS” function to determine the number of Kia cars that are sold only in
Russia.
(3 marks)
(c) Using the XLOOKUP function, determine what the body type is for the 8-Series model
manufactured by BMW
(3 marks)
(d) Using the XLOOKUP function, determine what the production years were for the STS
model manufactured by Cadillac.
(2 marks)

Task 7 – Perpetuities
YourHomeNow rents out various newly built properties to low-income families. It has
recently rented a flat to a client for £12,000 per year. A discount rate of 7% is used by
YourHomeNow.
(a) Calculate the present value of this rental income assuming it is expected to continue in
perpetuity and there will be no growth in annual rental income
(5 marks)
(b) Calculate the present value of this rental income assuming it is expected to continue in
perpetuity and there will be 4% growth in annual rental income
(5 marks)

Task 8 – Net Present Value
The data for this task is preloaded into your Excel workbook. Methuselah clothes is planning
to expand its clothing business by opening a factory overseas. The initial year 0 investment
will be £35,000,000. It is expected to generate net revenues of £12,500,000 each year if the
project goes ahead. Additional costs for the project will be £6,000,000 per year. [Cashflows
occur at the end of each year]
The company’s weighted average cost of capital is 9% and the project will have a lifetime of
15 years.
(a) Calculate the net present value (NPV) of the above proposal showing your workings in an
excel spreadsheet including formulas. You should complete your answer on the “Task 8 –
Data” tab of the “MBF Summative data.xlsx” excel file.
(5 marks)
(b) Use the NPV function in Excel to confirm your answer.
(1 marks)
(c) Based on your answer above, should the company proceed with the investment?
(Yes/No)
(1 marks)
(d) Describe two advantages of using the NPV method over the Payback method. Describe
one disadvantage of the NPV method.
(3 marks)

Task 9 – Break-Even Analysis
The data for this task is preloaded into you Excel workbook. Cantor-Hilbert LLP (CL) is a
medium-sized audit, tax and consultancy firm. So far, the firm has operated primarily in the
UK working with technology companies. It now sees an opportunity to start operations on
the west coast of America, with a new US headquarters in San Francisco. It will go ahead
with the project if the payback period is under six years.
(a) Calculate the payback period for the project in years and months assuming no
discounting.
(4 marks)
(b) Calculate the payback period for the project in years and months assuming a constant
project risk-adjusted discount rate of 6%
(4 marks)
(c) Based on your calculations above and other considerations, should the project go ahead
in your view? Give two points.
(2 marks)

Task 10 – Internal Rate of Return (IRR)
The data for this task is preloaded into your Excel workbook. Metaverse Magic Limited
(MML) is evaluating whether to build a new instance for its popular 19th century London
product line. This instance will cover popular London-based artists, poets and writers of the
time, with a focus on the visual arts. MLL will need to employ part-time and full-time staff
from a range of disciplines and professions.
Most of the MML’s development and operational infrastructure is cloud-based so is
expensed monthly as an operational expense. However, it will also need some capital
expenditure on some new computer equipment. The project is being analysed over a tenyear business management and investment analysis timeframe. The company is currently
funded by private equity and as such its base-level cost of capital hurdle-rate is quite high,
at 11%.
Product-marketing is leading this project and the team has come up with some initial
estimated ‘back-of-envelope’ cashflows as set-out in your workbook.
(a) Use the IRR interpolation formula to calculate the IRR for this proposed business
investment project based on the initial cashflow estimates. Ensure here that you choose
appropriate ‘above the line’ and ‘below the line’ interest rate figures to calculate the zero
Net Present Value interest rate figure that yields your IRR answer.
(2 marks)
(b) Should the company pursue the project based on your answer above
(1 mark)
(c) Confirm your answer above by using the Excel IRR function.
(1 mark)
(d) Which approach do you think is more accurate? Why?
(2 marks)
(e) Why do you think the company might apply a higher hurdle rate? Give two brief points.
(2 marks)
(f) Explain one advantage and one disadvantage of the IRR technique of investment
appraisal. (2 marks)

Marking Guide (for students)
The assignment is marked out of 100 and comprises ten tasks, each with ten marks. Each
task is further broken down into sub-tasks. In providing your answers, take account of
the following points:
• For each sub-task, take note of the marks allocated to that sub-task: For example,
if a sub-task asks for two advantages of a technique and two disadvantages and
carries a total of four marks, then make sure to provide four points to cover the
four allocated marks.
• Explicitly show your steps and working in coming to an answer and label the steps
in your working 1, 2, 3 etc. Note that marks will be awarded for a correct
approach and logical steps even if there is an error in your numerical calculations.
• Unless otherwise instructed give answers to two decimal places.
• Always check that your answers look sensible from a common sense and logical
perspective.
• Please ensure you use Excel as part of your workings.