BUS100 Business Skills and Management: Tutor-Marked Assignment (TMA) Assignment Question

Question 1

Most retail shops have closed their physical stores and set up websites to sell their goods online. The advantage of online shopping is that there is no retail space rental, which is quite expensive in Singapore, where land is scarce. E-commerce firms are thriving around the world. Ecommerce revenue reached 5.8 trillion US dollars in 2023. Tim is a recent university graduate who has started his own business selling cosmetics online.

Since the launch of the new online website “BeautyMe” last year, the number of visitors to his website is 500,000 (half a million visitors), denoted as year 1, and expected to grow at 1.5% per year. Among the visitors, 2% of the visitors will place at least one order. The average sale amount is $35 per visitor. The cost of goods sold (COGS) is 40% of the revenue. Tim will rent the space to store his cosmetics each year, costing him $10,000. The rental will grow by 5% each year. The manpower cost is $60,000 yearly, which is expected to increase by 3% yearly.

1. Construct a spreadsheet model of the website’s visits and sales for the next ten years (years 1 to 10). Identify the total expenses, such as COGS, rental, and manpower costs, as a percentage of the overall cost for ten years.

2. Compute the total revenue, accumulated profit, and profit margin (%) at the end of 10 years. Draw an appropriate graph to show the company’s finances for ten years.

3. What should the visitor growth rate per year be if the shop owner wants $2,000,000 (Two million) in total profit at the end of 10 years? Explain how you derive the solution.

4. Tim has a $60,000 budget to buy cosmetics and sells four products. The table below shows the cost and selling price for each product. Based on his experience, he wanted to buy at least 100 quantities of each product, and each product’s cost should not be more than 30% of the overall cost.

• Product Unit cost ($) Selling price ($)

• Facial care 50 80

• Makeup 100 150

• Lip care 30 45

• Shampoo 20 30

Develop a model to help him determine the quantity of each product he should purchase for his shop to maximise his profit. Present the constraints and decision variables. If he wants to maximise the total quantity purchased, how will the number of quantities to buy for each product change?

(Hint: Select GRN Nonlinear Solving Method.)

Presentation

Your models for Q1 must be neatly laid out and presented. You must apply appropriate colours, alignment, labels and formatting of numbers. Documentation is required.

Question 2

Simon plans to retire at the end of twenty years. His annual salary is $56,000 now and is expected to grow at 3%. His expenses will be $25,000 yearly and are expected to grow at 4% yearly. He will save the remaining money in the bank account at the end of each year and will only earn interest at the end of the next year. He currently has $70,000 in his bank account at the beginning of year 1, and he will earn an interest rate of 3.5% annually. Assume he will earn a constant interest rate based on the bank interest rate.

1. Solve Simon’s twenty-year retirement plan by developing a Spreadsheet model. Each year, you should have a row showing his finances, such as deposits, withdrawals, interest earned and total end balance at the bank. You may add more columns as you require; you need to ensure that the formulas can be filled down the rows for each column. Calculate the total balance in the bank account at the end of 20 years.
   Use your worksheet to compute the total amount of money he will have at the end of 20 years for the inputs below and tabulate your results:
   (i) a salary growth rate of 5%, an expense growth rate of 3% and an interest rate of 4.5% per year,
   (ii) a salary growth rate of 2%, an expense growth rate of 8% and an interest rate of 6% per year,
   
   Use your worksheet to answer the questions below:

2. How much should the salary growth rate be yearly to have exactly $1,500,000 in 20 years, assuming he earns 3.5% interest annually and the expense growth rate is 4%? Explain how you get the answer. Show your answer in two decimal places.
   
   Parts (c) and (d) are independent of parts (a) and (b).

3. With an initial bank balance of $70,000 at year 1, Simon wants to withdraw $15,000 from the bank at the start of the fifth, tenth, and fifteenth years for a good holiday with his wife. For a deposit of $30,000 at the end of each year and an interest rate of 7% per year, determine his bank balance at the end of 20 years.

4. Instead of saving the money in the bank, Simon’s friend asked him to invest in his business adventure. Simon needs to invest $150,000 initially, and his friend will give him $20,000 in the first year, an increase of $2000 from the previous years for the next nine years, and an end-of-term bonus of $5000 in the tenth year. Using the discount rate of 8% and the bank interest rate of 4%, would you recommend Simon to save his money in the bank or invest in his friend’s business adventure? Justify your answer using the model.

Presentation

Your models for Q2 must be neatly laid out and presented. You must apply appropriate colours, alignment, labels and formatting of numbers. Documentation is required.

Question 3

Using your experience of running a virtual company in MonsoonSIM,

1. Describe three ways you can increase her revenue through retail, B2B, and e-commerce by analysing some key performance indicators such as sale volume, production, and market share.

2. You may refer to the virtual company in MonsoonSIM or any company that you have worked for as a reference. Discuss how the organization can use data analysis to improve inventory management and give a detailed explanation of your recommendations.