Question 1

Mike has started a food catering business. He faces many business challenges. This assignment requires you to practise principles of correct spreadsheet construction and design to help Mike to improve decision making in solving business problems.

Mike needs a van for food delivery. There are two components of the initial cost of owning a van: The Van Price charged by the vehicle seller and the Certificate of Entitlement (COE) price to be bid and paid to the Land Transport Authority (LTA).

Note: you must follow the information given in this TMA case and ignore other LTA and Monetary Authority of Singapore (MAS) rules and regulations concerning vehicle loans, the COE and vehicle usage.

(a) The Singapore Manufacturing Purchasing Managers’ Index (PMI) is a key barometer of the manufacturing business. Mike has observed that successful COE price every month seems to correlate with the PMI released early in that month. For his business to be sustainable, he must do his financial planning carefully. Hence, he wants to predict the COE price accurately based on the PMI. He collected the past four pairs of PMI and prices of “Category C” COE which is used for buying goods vehicle.

Compose a spreadsheet model shown above that will allow Mike to predict the COE price for a PMI. This will solve the current problem of potential vehicle buyers underbidding the COE hence not able to buy the vehicles or overbidding which will drive up the COE prices. Implement Excel function(s) to predict the COE price based on a given PMI by assuming a linear relationship between them.

Mike just read from the news that the PMI is 50.5. Using the model, write down the predicted COE price (to the nearest dollars).

Relate how the COE prices change with the PMI values by creating a scatter plot with a Trend Line. Verify your answers from your model and the Trend Line relationship are almost similar for PMI = 50.5.

(b) Mike has shortlisted his preferred van to the following three models:

Mike discussed with his bank relationship manager for a vehicle loan package. Mike will pay the vehicle seller a down-payment of 20% of Van Price inclusive of the COE, and loan the remaining amount from the bank. The bank will charge him the annual rate of interest at 3.5%. Mike must pay a fixed amount yearly at the end of each year for 8 years.

Using the template shown below, construct a spreadsheet model that will help Mike to calculate the yearly payment to the bank. Enter data into the shaded cells based on the loan plan described above. Your model must include Excel formulae and/or functions in the non-shaded cells which are not to be hardcoded with data.

Using the calculator for each van state the various monthly payments.
You shall highlight any special or advanced features in your model in Q1.

Question 2

To decide which van model to purchase, Mike must consider the revenues generated and expenses incurred. He collected more information and made further estimations as listed below:

(i) To reduce pollution caused by old vehicles, LTA encourages vehicle owners to scrap their vehicles by providing a rebate payable at the end of 10 years according to the engine capacity. If the van’s engine capacity is less than 1600cc, the net rebate will be 12% of initial Van Price excluding the COE. Otherwise, it will be 18%. Mike intends to use the van for only for 10 years.

(ii) Mike estimates that the Year 1 expenses (includes road tax, insurance, petrol, maintenance, etc.) will be 6% of the Van Price including the COE. The expenses will increase at the rate of 3% annually for subsequent years.

(iii) He also estimates that the Year 1 revenue generated due to the van will be 9% of the Van Price including the COE in the Payment Calculator. The revenue will increase at a rate of 6% annually for subsequent years.

(iv) He expects an annual investment return rate of 10% which would be used when calculating the Net Present Value.

You are required to fill in numbers in the following tables according to the information above and use them to develop your model for Question 2:

(a) Create a spreadsheet model that will help Mike to determine the Net Present Value (NPV) of buying the van.

In your model, Mike shall have the flexibility to enter any van model. You need to include Excel formulae and functions in the other parts of the model: these are not to be hardcoded with data.

The model shall find the cash net flow on a yearly basis from Year 0 to Year 10 and eventually calculate the NPV of the van.

Note: There shall be only one Excel model used for any van, instead of three models.

Using your model, list the NPV of each van. Interpret the results to acquire insights from the NPV derived and make appropriate suggestions to Mike.

(b) Mike wishes to carry out a sensitivity analysis to examine how the changes in various key performance parameters will affect the NPV. Design a Data Table by linking it to the NPV in Q2(a), with “Year 1 Expenses in % of Van Price” as the row of the table and Year 1 Revenue in % of Van Price” as a column of the table. The Expenses shall vary from 4% to 8% and the Revenue shall vary from 7% to 11%. To aid follow-on analysis, apply conditional formatting to the table and show the current NPV with a blue background.

Enter “Berlingo” into the NPV model in Q2(a). Then, analyse the Data Table of Q2(b) in terms of the two key performance indicators of business Expenses and Revenue. Hence, infer the most appropriate decisions that Mike may take.

You shall highlight any special or advanced features in your model in Q2.

Question 3

(a) Using your experience of running a virtual company in MonsoonSIM, identify one method by which Mike can reduce the cost of his food catering business (without sacrificing quality) for each of the following departments:

(i) Logistic and warehousing,
(ii) Maintenance,
(iii) Marketing

(b) Explain how Mike can use best practices in Leadership to improve his business. You must support your answer by quoting examples from the virtual company in MonsoonSIM, or any company that you have worked for. Alternatively, you may justify your answer using Enterprise Singapore’s Business Excellence Framework.

(Limit your answer to 500 words for both parts).