Service: Case Study Writing
Style: APA
Number of pages: 3 pages (825 words)
Number of sources: 6
Topic of your paper: BUSiness skill and management
Subject: – Accounting
Comment:
Instructions Read the following Case Study.
Create appropriate Excel models to analyse it. You need to save your models in an xlsx (or xls) file. All questions will need to be answered in your xlsx file.
The answer for each question must be in its own worksheet. Organise your workbook with worksheets as follows: Cover, Q1, Q2, Q3. The Cover worksheet should contain
your Name, PI Number, Seminar Group, Assessment Name and Module Code.
Documentation in the form of graphical arrows, bubbles and comments to explain how your models are created and how they are to be used is expected for Q1 and Q2.
Answers to questions must be typed into appropriately formatted cells in your worksheet.
Marks awarded to your assignment are based on the following guidelines:
⦁ 80% of the marks are allocated to the content of your answers:
The marks awarded to what your answers cover depend on the extent to which they cover the key points that correctly and comprehensively address each question.
The key points should be supported by evidence drawn from course materials and, wherever relevant, from other credible sources.
⦁ 20% of the marks are allocated to the presentation of your answers: Wherever applicable, the marks awarded to how your answers are presented depend on the
extent to which your answers:
form a sound reasoning by developing those key points in a clear, logical and succinct manner;
provide proper and adequate in-text citations and referencing to content drawn from course materials and other credible sources;
strictly follow APA formatting and style guidelines1 , in particular for:
• in-text citations and end-of-report references;
• the identification of figures and tables;
use, wherever relevant, the specialised vocabulary and terminology commonly used in discussions about the topic(s) covered by each question;
provide a reference or bibliography at the end of the main report;
include the less relevant details in an Appendix;
use sentence constructions that are grammatically and syntactically correct;
are free from spelling mistakes; present the workings, numerical formulations and results in a logical manner that follows the APA formatting and style guidelines;
design and present graphs, diagrams and plots that follow the APA formatting and style guidelines;
are highly original;
have proper formatting, which may:
• include a properly formatted cover page;
• respect the answer length/word count set out in the assignment guidelines, if any is prescribed;
• present answers in paragraphs with proper spacing and page margins;
• include page numbers and appendices, if necessary.
The Funtastic Preowned Cars Case Study
The Funtastic Preowned Cars Case Study Adrian Chan runs a used car business called Funtastic Preowned Cars Pte Ltd. The company has two outlets in Singapore. It hires
6 car sales persons and has 3 administrative and finance staff. Its headquarters are at the Ubi Megacentre while its other branch outlet is at Pasir Panjang. Business
has improved over the years as the company had established a name for itself by being professional and customer-friendly. Its professionalism stems from its focus on
only Honda, Toyota and Mazda cars. It is also one of the few car companies that offer low-interest loans over the full life of a used car.
Adrian is a very hands-on businessman. He tracks his business closely by diligently compiling his own Profit and Loss statement on a monthly basis using traditional
pen, paper and calculator. In the month just ended, he noted the following financial details:
Sales of Toyota, Honda and Mazda cars were $250,000, $120,000 and $90,000 respectively. The corresponding costs of cars sold were $220,000, $90,000 and $60,000. These
costs include the commissions paid to the sales persons. To cover their expenses, all sales persons are paid a basic allowance of $800 per month. Office-cum-shop
rentals at Ubi and Pasir Panjang were $8,000 and $6,000 per month respectively. Administrative overheads, including admin and finance staff salaries, total up to
$12,000. Miscellaneous expenses stood at $7,000 and these included utilities, stationery and other office consumables. All these are monthly figures.
Adrian smiled at the company performance for last month. But he frowned as he analysed his business for the current month. Things did not look good:
The landlords at both outlets have asked for increases of $2,000 and $3,000 monthly rental and Adrian has previously agreed to their demands.
Beginning this month, all sales persons will have their allowances increased by 15%.
Admin overheads are expected to increase by 10%. Fortunately miscellaneous expenses will remain stable.
But business outlook is bleak. To sustain the same level of business (with the same relative number of cars sold and similar cost of cars), Adrian plans to offer a
price discount. His survival instinct tells him that he needs to take a profit hit in order to ride out tough times.
At the same time, he wants to improve the operational efficiency of his business in order to control administrative overheads. Currently the sales persons have to look
up information on existing car stocks in order to answer customer queries. The admin staff have to help them to prepare hire-purchase plans. Depending on the used-car
price, the hire-purchase plan will have to show the down-payment and monthly instalment amount that the customer needs to pay. The initial down-payment is standardised
at 12% of the car price. The remaining amount is to be paid at the end of each month until the end of life of the car, which occurs when the Certificate of Entitlement
(COE) expires. The company charges a compound interest rate of 2.8% per annum.
Adrian has recently learnt of the power of Excel in data analysis and automation. Instead of relying on the admin staff, Adrian plans to equip each sales person with
an Excel workbook that can automatically call up used-car information and compute the necessary hire-purchase plan amounts. For himself, he also wishes for a tool that
can help him to prepare his monthly profit and loss statements.
The next several months will be difficult. Adrian is prepared to help his sales persons on the ground. He is also determined to push ahead with his automation plans.
As a seasoned business person, he keeps reminding himself that when the going gets tough, the tough gets going!
Question 1
Using Excel and the figures provided in the case study, prepare a Profit and Loss Statement for the previous month.
Your Profit and Loss statement should reflect the following structure:
Sales
Toyota xxxx
… …
… … xxxx
Cost of Goods Sold
Toyota xxxx
… …
… … xxxx
Gross Profit xxxx
Operating Expenses
Sales Staff xxxx
Shop Rental xxxx
… …
… … xxxx
Net Profit Before Tax xxxx
Apply your spreadsheet modelling knowledge to answer the following questions:
⦁ What is the company’s net profit before tax for the previous month?
Make a copy of your Profit and Loss Statement model to analyse the second month. Update the affected rows in Operating Expenses to reflect the new information given
for the current month. Wherever possible, you should use a formula to include the changes.
In your second model, add a cell called “Percentage Change in Price”. The figure in this cell should be a percentage formatted with 2 decimal places. As an initial
value, enter “0%” into the cell. Use this cell to adjust the total sales amount in your model.
(b) What is the net profit before tax in the current month if no discount is offered?
(c) What is the maximum discount percentage that the company can offer? Explain how you get this figure.
(40 marks)
Question 2
Create a model that can serve as an automation tool for the case study.
You may design your model to look like the following:
Choose Car item: <>
Today’s Date: << show date today in the format dd/mm/yyyy >>
Car Information Make : << depends on Car item above>>
Model : << depends on Car item above>>
COE Expiry Date : << depends on Car item above >>
Price : << depends on Car item above >>
Hire Purchase Plan
Downpayment : << depends on Car item above>>
Number of Months : << depends on Car item above>>
Annual Interest Rate : << input cell>>
Monthly Payment : << depends on Car item above>>
<
Used car information…
For the purpose of testing and demonstrating your model, you may use the information below, which is a subset of what the company currently has in stock.
(a) Using your model, show what the downpayment and monthly instalment are for a customer who is interested in the Mazda hatchback 3.
(b) Financial Consultants have advised that one should not spend more than 15% of the monthly income on paying the monthly instalment. If you earn $90,000 a year,
which car(s) in the above stock can you consider buying?
(40 marks)
Question 3
This question requires you to conduct a literature search and comment on research papers or books or publications by reputable organisations. Note that articles from
newspapers and popular magazines and blog sites are not permitted.
First, login to your UniSIM Student Portal and access the Library E-Resources. Using any of the databases listed (such as ABI-INFORM Complete, Business Source
Complete, ScienceDirect and others), select two (2) articles that discuss “Management by Wandering Around (MWA)”.
Note: You may also use other digital resources such as those from the National Library.
To demonstrate your academic research proficiency, and using the journal articles you have selected, create a table with the following information:
Write a summary of what you have learnt from ANY ONE of the 2 articles. Explain how you would apply it to Funtastic PreOwned Cars Pte Ltd, particularly in relation to
how its company management can better manage its business and/or operations using the concept of MWA. Your answer for both parts should not exceed 500 words. Inline
citation is required. (20 marks)
