3 questions/problems, for each of them, create appropriate Excel models to analyse it (including some comments and documentation). Attached is the project brief, sample problems & solutions.

BUS100 Business Skills and M anagement Tutor -Marked Assignment J UL Y 2022 SEMESTER SINGAPORE UNIVERSITY OF SOCIAL SCIENCES (SUSS) TMA – Page 2 of 6 TUTOR -MARKED ASSIGNMENT (TMA) This assignment is worth 30% of the final mark for BUS100 Business Skills and Management. The cut -off date for this assignment is 2 Sep 20 22 , 2 3:55 pm . Note to Students: You are to include the following particulars in your submission: Course Code, Title of the TMA, SUSS PI No., Your Name, and Submission Date. Instructions Read the instruction carefully before you attempt any question. For each of the following cases, create appropriate Excel models to analyse it. You need to save your models in an appropriate xlsx Excel file. You should only submit one workbook for this TMA. It is individual assignment and there is a severe penalty to refer to others work . All questions will need to be answered in you r Excel file. Refer t o the TMA templat e given as refere nce. The answer for each question must be in its own worksheet. Organise your workbook with worksheets as follows: Cover, Q1, Q2, and Q3. The Cover worksheet should contain your Name, PI Number, Seminar Group, Assessment Name and Module Code. Eac h model should fit into one worksheet (A4 page), with user -friendly formatting and layout, clear documentation and print -ready state. You must provide answers to the individual questions posed in the problems. Note that for take -home assignment, students are expected to research beyond the school textbook and course materials. Please cite any references that you used properly in the assignment. Refer to Sample TMA and Sample TMA solutions as your guide to the standard expected. See also the rubric below for the mark allocation of each question . For your submission, you will need to upload a single Excel workbook (xlsx) file for grading via Canvas. Please remove any VBA or Macro from your workbook. DO NOT PROTECT your workbook with an y password, otherwise, it won’t be marked. SINGAPORE UNIVERSITY OF SOCIAL SCIENCES (SUSS) TMA – Page 3 of 6 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 conten t 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;  strictly follow APA formatting and style guid elines 1, 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 questio n;  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, wh ich 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 n ecessary. 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) TMA – Page 4 of 6 Question 1 (40 marks) Mindy operates a company called “Fruity Juice” via e -commerce website. It sells apple juice, orange juice and guava juice in boxes. She rents a warehouse to store these items and engages a third -party logistics vendors to ship these items to the customer. The cost of goods sold (COGS), selling price and daily demand for each item is given in the table below: Description COGS ($) Unit Selling price ($) Demand per day Apple Juice $ 10.00 $ 1 2.00 45 0 Orange Juice $ 11.50 $ 1 3.00 250 Guava Juice $ 1 2.50 $ 1 5.0 0 300 The warehouse rental is $8000 per month. Mindy has hired 3 staff to help her with the operation and each staff cost $3000 per month and she gets a salary of $5000. To promote the product online, Mindy also spends $2000 monthly on digital marketing to incre ase her brand awareness. She also needs to pay third -party logistics vendors $0.50 per 10 boxes to ship the items to the customers. Assuming that there are 30 days in a month. a. Construct a spreadsheet model to analyze the company’s monthly finance, the sp readsheet model should include total revenue ($), total expenses ($), profit/loss ($) and profit margin (%). Sketch the influence diagram of your model. (10 marks) b. If the company wants to increase the profit margin to 10% by changing the selling price of Apple Juice, discuss what will be the new selling price of apple juice, explain how you derive the answer ? Show your answer in nearest 10 cents. (4 marks) c. For e very $1 increase in price, the demand will drop by 20 boxes. It applies to all the juices. What is the relationship between the selling price and the demand for all juice? Calculate the demand for apple, orange and guava juice if the selling price is $1 6 per unit? (6 marks) d. What will be the new selling price for all juices, if the company wants to earn 20% profit margin, your model should include the price elasticity of demand. Total demand for each month has to be at least 1 5,000. Describe how you derive at the solution (write down the steps in detail) and clearly show the new pr ofit margin and selling price. (12 marks) Documentation, Presentation, layout and formatting Your models for Q1 must be neatly laid out and presented. You should write down your answer for each part explicitly. You must apply appropriate colours, alignment, labels and formatting of numbers. Documentation and legend are expected. (8 marks) SINGAPORE UNIVERSITY OF SOCIAL SCIENCES (SUSS) TMA – Page 5 of 6 Question 2 (40 marks) You are planning your finance for the next twenty year. Assume that you will earn a constan t interest rate on your money. At the beginning of the first year, you have $15,000 in your bank account. Your salary is $45000 in the first year and it is expected to grow at 3% per annum. Your expenses is $25000 and it is expected to grow at 5% each year. You will deposit the remaining amount to a fixed deposit account at the end of each year and it will earn an interest of 7% per year . a. Develop a spreadsheet model that will show you how much you will have accum ulated at the end of 20 years. Use your worksheet to compute the amount of money and interest earned at the end of 20 years for the inputs given here and tabulate your results. What is the average deposit and bank balance? You should have a separate column for income, expenses, annual deposit and interest earn for each year. You should also show the finance using appropriate graph. (16 marks) b. Identify what will be the interest rate the bank should pay, so that you have exactly $1,000,000 in 20 years? How much is the total interest earned? Explain how you derive the answer. (5 marks) c. You reckon that you may need to withdraw $ 25,000 from the bank at the start of the tenth year for your kid’s education for four years (year 10 th – 13 th) and the tuition fees is expected to increase by 10% each years . For an interest rate of 7% per year as given in part a), analyse what would be your bank balance at the end of 20 years and total wi thdrawal amount . You may add a new column for Withdrawal to take care of the withdrawal amount. (5 marks) d. State two ways how you can grow your bank balance at the end of 20 years . (6 marks) Documentation, Presentation, layout and formatting Your models for Q2 must be neatly laid out and presented. You should write down your answer for each part explicitly. You must apply appropriate colours, alignment, labels and formatting of numbers. Documentation and legend are expected. (8 marks) SINGAPORE UNIVERSITY OF SOCIAL SCIENCES (SUSS) TMA – Page 6 of 6 Question 3 (20 marks) Based on your experiences of running a virtual company in MonsoonSIM or working in a company, (a) Explain two ways how you can increase the cash flow for your company by analyzing some key performance indicators such as sale volume, COGS and production. (Limit your answer to 300 words). (10 marks) (b) You may refer to the virtual company in MonsoonSIM or any c ompany that you have worked for as reference, describe three ways how the company can increase the market shares and gives a detailed explanation of your recommendations. (Limit your answer to 300 words). (10 marks) You need to comply with the word count limit requirement stated above . Use the TMA template and the word count will be computed automatically for you in Question 3 worksheet. ** ***************** **** * END OF P APER *********** ***********************