BUS100 Business Skills and Management Tutor-Marked Assignment January 2018 Presentation BUS100 Tutor-Marked Assignment TUTOR-MARKED ASSIGNMENT (TMA) This assignment is worth 20% of the final mark for BUS100 Business Skills and Management. The cut-off date for this assignment is 09 March 2018, 2355 hrs. Instructions Read the following Case Study. Create appropriate Excel models to analyse it. You need to save your models in an appropriate xlsx Excel file. All questions will need to be answered in your Excel 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. For your submission, you will need to upload a single Excel workbook (xlsx) to Canvas. 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. For Q3, inline citation in compliance with SUSS Referencing Guide is required. Note that for take-home assignment, students are expected to research beyond the school textbook and course materials. Important Note: Grading of TMA Submissions Marks awarded to your assignment are based on the following guidelines: 1. 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. 2. 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; SINGAPORE UNIVERSITY OF SOCIAL SCIENCES (SUSS) Page 2 of 6 BUS100 Tutor-Marked Assignment  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. 1 You can find a short tutorial on the APA formatting and style guidelines here: https://is.gd/mgEOnC . Additional details (pertaining to tables and figures) can be found here: https://is.gd/O4vDdT . SINGAPORE UNIVERSITY OF SOCIAL SCIENCES (SUSS) Page 3 of 6 BUS100 Tutor-Marked Assignment “TrueMe” Online shop E-commerce has been prevailing in recent years. Most people have set up online websites to sell their goods online. The advantage of online shopping is that there is no rental of retail space which is quite expensive in Singapore where land is very scarce. Traditional retail scene in Singapore is also performing poorly over the past few years. The government is trying every means to improve the performance of this sector. The revenue from the “Wholesale and Retail Trade” sector contributed about 14.2% to the overall GDP in 2016 (Refer to SingStat data). The revenue contributed by the “Wholesale and Retail Trade” sector has been dropping over the years. It was 19% in 2012, followed by 18.5% in 2013, 16.3% in 2014, and 14.8% in 2015. However, e-commerce firms are thriving in Singapore. Come 2025, Singapore’s e-commerce market is expected to exceed S$7 billion which means the forecasted revenue is set to double by 2025. Among them, there are a few online retail giants who are dominating the online marketplace. They are Qoo10, Lazada, Zalora, Ezbuy, Redmart, Shopee, Carousell, ebay, Amazon Prime Now etc. Despite the competition, Ivan is interested and determined to set up an online website to sell small popular movie collectibles and memorabilia. He thinks he can succeed in his niche market compared to the big players who are focused on a broader range of products. Question 1 Since the launch of the new online website “TrueMe” last year, the number of visits is growing at 1.5% per year. Last year, there were 4.2 million visits and 71,400 of these visits or 1.7% have at least one order. Of these visits with sales, the average sales amount was $16.50. Sales revenue is growing at 2.4% per year. The profit margin is 20% of the revenue. (a) Practise principles of correct spreadsheet construction and design a suitable spreadsheet Excel Model for the website’s visits and sales for the next 10 years. It should provide the relevant proportion and growth results. Sketch the Influence diagram of your model. (b) Compute the revenue and profit at the end of 10 years. (c) What should be the sale revenue growth rate if the shop owner wants $2,000,000 (Two million) revenue at the end of 10 years? Explain how you derive at the solution. (d) What is the revenue growth as compare to part (b) if the number of visits grows at 5% per year, the proportion of visits that results in sales is 2% and the sales growth rate is 8% per year? (e) Comment on how the results change with time and how does the average sales per order vary from year to year with other input rates? (40 marks) SINGAPORE UNIVERSITY OF SOCIAL SCIENCES (SUSS) Page 4 of 6 BUS100 Tutor-Marked Assignment Question 2 This question requires you to demonstrate how to work with changes in the value of money over time. Ivan has taken a loan of one million ($1 million) dollars from the bank to start this online business. The setup cost is $400,000 which he will incur in the first year and it is depreciated over time. He can choose one of the four plans to repay his loan. Plan A: annual interest 3.80%, Tenure 8 years Plan B: annual interest 4.20%, Tenure 6 years Plan C: annual interest 5.50%, Tenure 5 years Plan D: annual interest 6.00%, Tenure 3 years He expects a first year revenue of $500,000 and, thereafter, revenue will grow at 6.5% per year. His non-loan expenses inclusive of operational charges are initially $420,000 per year and this grows at 4% per year. (a) Apply a spreadsheet model to allow him to choose the plan based on his financial status. Your model should allow the user to choose a plan (A, B, C, D), then look up and display the corresponding interest rate and tenure. Compute the loan payment and total interest paid by Ivan according to each plan chosen. Hint: use PMT to find payment. (b) Construct a spreadsheet model to examine the financial status of Ivan’s business for 10 years. Your model should include respective financial figures such as revenue, non-loan expense, loan repayment, cash on hand, annual cashflow and cumulative cashflow amounts for each of the 10 years. Make your own assumption(s) for the model. These assumptions need to be explicitly stated as comments in your model. (c) Which plan should he choose, so that the business is viable within the first 10 years? Justify your answer using the model. (d) What if the first-year revenue is $450,000, growing at 7%; set up cost is $350,000, firstyear expense is $300,000, growing at 5%; and loan repayment is based on plan D? How will his decision change? Justify your answer. (40 marks) SINGAPORE UNIVERSITY OF SOCIAL SCIENCES (SUSS) Page 5 of 6 BUS100 Tutor-Marked Assignment Question 3 This question requires you to conduct a literature search and comment on research papers. First, login to your SUSS 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 Business Risks and/or Environmental Uncertainties. Note: You may also use other digital resources such as those from the National Library, only use the academic journals as reference. To demonstrate your management research proficiency, and using the journal articles you have selected, create a table with the following information: S/N Name of Database Name of Journal Title of Article Date Author(s) (Year/Volume) Write a summary of what you have learnt from any one (1) of the two articles, and discuss to show how you would apply it to Ivan’s business situation. Your answer for both parts should not exceed 500 words. Inline citation is REQUIRED. (20 marks) ---- END OF ASSIGNMENT ---- SINGAPORE UNIVERSITY OF SOCIAL SCIENCES (SUSS) Page 6 of 6 This is to clarify on Q1 in the TMA. "Since the launch of the new online website “TrueMe” last year, the number of visits is growing at 1.5% per year. Last year, there were 4.2 million visits and 71,400 of these visits or 1.7% have at least one order. Of these visits with sales, the average sales amount was $16.50. Sales revenue is growing at 2.4% per year. The profit margin is 20% of the revenue." Do take note of the following clarifications: a) Only some visits result in a sale. Each visit with a sale is called an order. 1.7% of all visits a year result in an order. This 1.7% may be assumed to be the same every year. b) Sales Revenue refers to Total Sales per year. c) For the first year (ie Last Year), the average sales amount was $16.50 per order. However, for subsequent years (years 2, 3, 4, etc), the average sales amount per order can be DiFFERENT. d) Growth rate of visits (1.5%), Sales Revenue growth rate (2.4%), and Profit Margin (20%) may all be assumed to be constant for each of the 10 years. This is to clarify on Q2 in the TMA. "Your model should include respective financial figures such as revenue, non-loan expense, loan repayment, cash on hand, annual cashflow and cumulative cashflow amounts for each of the 10 years." Do take note of the following clarifications: a) Annual Cash Flow = Net Cash flow = Cash Inflow - Cash Outflow Annual Cash Flow is about tracking how money flows in and out from year to year while doing business. Cash Inflow happens when money is being earned by the business. Cash Outflow happens when money is spent or paid out by the business. Initially, before the business starts, the Annual Cash Flow is zero. In the first few years, Annual Cash Flow may be negative. b) Cumulative Cash Flow for Current Year = Cumulative Cash Flow for Previous Year + Annual Cash Flow for current year In other words, Cumulative Cash Flow is simply Accumulated Annual Cash Flow. c) Cash On Hand refers to how much money Ivan is holding onto. It starts with the initial loan amount. And it will increase or decrease from year to year as money is being spent or earned.