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.

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, and also the template for my assignment)

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.
LeongTY ProblemSet1a_Soln.xlsx/1.03 Windchill © 2014 Leong Thin Yin. All rights reserved. b1 0.16 Wind Windchill temperatures for different air temperatures (deg C) kmph 10.0 7.5 5.0 2.5 0.0 -2.5 -5.0 -7.5 -10.0 -12.5 -15.0 -17.5 -20.0 -22.5 -25.0 -27.5 -30.0 -32.5 5 9.8 6.9 4.1 1.2 -1. 6 -4. 4 -7. 3 -10. 1 -12. 9 -15. 8 -18. 6 -21. 4 -24. 3 -27. 1 -30. 0 -32. 8 -35. 6 -38. 5 10 8.6 5.6 2.7 -0. 3 -3. 3 -6. 3 -9. 3 -12. 3 -15. 3 -18. 2 -21. 2 -24. 2 -27. 2 -30. 2 -33. 2 -36. 2 -39. 2 -42. 1 15 7.9 4.8 1.7 -1. 3 -4. 4 -7. 5 -10. 6 -13. 7 -16. 7 -19. 8 -22. 9 -26. 0 -29. 1 -32. 2 -35. 2 -38. 3 -41. 4 -44. 5 20 7.4 4.2 1.1 -2. 1 -5. 2 -8. 4 -11. 6 -14. 7 -17. 9 -21. 0 -24. 2 -27. 3 -30. 5 -33. 6 -36. 8 -39. 9 -43. 1 -46. 3 25 6.9 3.7 0.5 -2. 7 -5. 9 -9. 1 -12. 3 -15. 5 -18. 8 -22. 0 -25. 2 -28. 4 -31. 6 -34. 8 -38. 0 -41. 3 -44. 5 -47. 7 30 6.6 3.3 0.1 -3. 2 -6. 5 -9. 7 -13. 0 -16. 3 -19. 5 -22. 8 -26. 0 -29. 3 -32. 6 -35. 8 -39. 1 -42. 4 -45. 6 -48. 9 35 6.3 3.0 -0. 4 -3. 7 -7. 0 -10. 3 -13. 6 -16. 9 -20. 2 -23. 5 -26. 8 -30. 1 -33. 4 -36. 7 -40. 0 -43. 3 -46. 6 -49. 9 40 6.0 2.6 -0. 7 -4. 1 -7. 4 -10. 7 -14. 1 -17. 4 -20. 8 -24. 1 -27. 4 -30. 8 -34. 1 -37. 5 -40. 8 -44. 2 -47. 5 -50. 8 45 5.7 2.3 -1. 0 -4. 4 -7. 8 -11. 2 -14. 5 -17. 9 -21. 3 -24. 7 -28. 0 -31. 4 -34. 8 -38. 2 -41. 5 -44. 9 -48. 3 -51. 7 50 5.5 2.1 -1. 3 -4. 7 -8. 1 -11. 5 -15. 0 -18. 4 -21. 8 -25. 2 -28. 6 -32. 0 -35. 4 -38. 8 -42. 2 -45. 6 -49. 0 -52. 4 55 5.3 1.8 -1. 6 -5. 0 -8. 5 -11. 9 -15. 3 -18. 8 -22. 2 -25. 6 -29. 1 -32. 5 -36. 0 -39. 4 -42. 8 -46. 3 -49. 7 -53. 1 60 5.1 1.6 -1. 8 -5. 3 -8. 8 -12. 2 -15. 7 -19. 2 -22. 6 -26. 1 -29. 5 -33. 0 -36. 5 -39. 9 -43. 4 -46. 9 -50. 3 -53. 8 Comments 1. Formula for windchill temperature (deg C) = 13. 12 0. 6215 * T – 11. 37 * T * V^0. 16 0. 3965 * V^0. 16 2. I t should not be hardcoded, in case scientists recalibrate and issue new parameter v alues. Documentation Wind speed kmph C4 Parameter a1 L4 Air temperature degree C E4 Parameter a2 N4 Windchill temperature degree C G4 Parameter a3 P4 Windchill effect degree C I4 Parameter a4 R4 Air temperatures degree C C7: T7 Parameter b1 T4 Wind speeds kilometers per hour B8: B19 Windchill temperature degree C C8 =$L$4 $N$4* C$7$P$4 *$B8^$T$4$R$4*C$7*$B8^$T$4 Windchill Wind kmph Deg C: Air 0.3965 a4 Ef f ec t a2 10 5 9. 8 -0. 2 0.6215 13.12 a1 a3 -11.37 LeongTY ProblemSet1a_Soln.xlsx/1.04 Foreign Currency © 2014 Leong Thin Yin. All rights reserved. Ra t e 1. 000 0. 740 0. 620 100. 220 31. 600 1. 250 Equivalent 2,400 1,776 1,488 240,528 75,840 3,000 Smallest Change 50 50 20 5,000 1,000 Description US D Euro ¬ Pound £ Yen ¥ Ba ht Total SGD Initial Basket 204. 43 151. 28 126. 75 20,488. 01 50,000 3,000 Initial % 8. 5 8. 5 8. 5 8. 5 65. 9 100. 0 Final Basket 200 150 120 20,000 50,000 2,973 Final % 8. 4 8. 5 8. 1 8. 4 66. 5 100. 0 Comments 1. The cells are color-coded for easy recognition of input cells. 2. This is particularly important here since inputs are done inside the table itself. 3. M ore work to be done to ensure the abov e model work s for all test cases. Documentation US dolla r C3 , 1 Euro per USD D3 Pound per US D E3 Yen per USD F3 Ba ht per US D G3 SGD per USD H3 US dolla r C4 Equivalent in Euro D4 =$C4/$C$3*D3 Smallest change C5: G5 Initial USD amount C8 =(C9/100)*($H8/$H$3)*C$3 Input % C9: F 9 , =(100-$G9)/4 Ba ht input a mount G8 Ba ht a s % of initia l ba s k et G9 =(G8/G$3)*$H$3/$H8*100 Initial total SGD H8 =H4 Initial total % H9 =S UM (C9: G9) Final USD amount C11 =RO UND(C8/C$5,0)*C$5 Final USD % C12 =(C11/C$3)*$H$3/$H11*100 LeongTY ProblemSet1a_Soln.xlsx/1.05 Time Sheet © 2014 Leong Thin Yin. All rights reserved. Total Income Tax Fee $/wk Total 25 J ul 26 J ul 27 J ul 28 J ul 29 J ul 30 J ul 31 J ul 1087 22% 8. 00 816 Worker Sun h/day Mon Tue Wed Thu Fri Sat h/day Ra te $/hr Week Total Gross Pay Tax $ Union $ Net Pay Abel, Ann 6 11 5 8 8 12 5 4. 50 55 247. 50 54. 45 8. 00 185. 05 Jones, John 9 4 9 11 4 6 5 6. 50 48 312. 00 68. 64 8. 00 235. 36 Smith, Sam 7 6 7 4 8 6 7 5. 00 45 225. 00 49. 50 8. 00 167. 50 Wall, Joan 5 10 4 4 12 12 8 5. 50 55 302. 50 66. 55 8. 00 227. 95 Comments 1. The dates here are automated to show last week . 2. This giv es a complete week of data and thus allows the pay to be issued. 3. Rev ised documentation for the date cells C4:I 4 are Documentation Date C4 , =I F (B4=””,TO DAY()-WEEKDAY(TO DAY())-6,B4) Total gross pay L4 =S UM (L7: L10) Income tax rate M4 Union fee N4 Total net pay O4 =S UM (O 7: O 10) Hours work ed C7: I 7 Hourly rate J7 Total hours K7 =S UM (C7: I 7) Gross pa y L7 =K7*J7 Income tax M7 =M $4*L7 Union fee N7 =N$4 Net pa y O7 =L7-M 7-N7 LeongTY ProblemSet1a_Soln.xlsx/1.07 Sprocket Hub © 2021 Leong Thin Yin. All rights reserved. SS/Order ratio 10% Demand Unit Order $ Unit Ca rry $ EOQ # Orders Order $ Ca rry $ Safety Stock $ Total $ 10,000 $0. 50 $15. 00 26 387 $193. 65 $193. 65 $2. 58 $389. 88 b 20,000 $0. 50 $15. 00 37 548 $273. 86 $273. 86 $3. 65 $551. 37 c Test Cases Demand U. O rder $ U. Ca rry $ Order Qty # Orders Order $ Ca rry $ Safety.S $ Total $ 10,000 $0. 50 $15. 00 600 17 $8. 33 $4,500. 00 $60. 00 $4,568. 33 10,000 $0. 50 $15. 00 700 14 $7. 14 $5,250. 00 $70. 00 $5,327. 14 10,000 $0. 50 $15. 00 775 13 $6. 45 $5,812. 50 $77. 50 $5,896. 45 10,000 $0. 50 $15. 00 800 13 $6. 25 $6,000. 00 $80. 00 $6,086. 25 10,000 $0. 50 $15. 00 900 11 $5. 56 $6,750. 00 $90. 00 $6,845. 56 Comments 1. Total cost does not change much when order quantities differ significantly from the EOQ . 2. This suggests that the EOQ need not be strictly followed. 3. The EOQ occurs at where order cost equals carry cost. 4. I t may be better to order at more ty pical time interv als, say daily , week ly or monthly . 5. For case b, the recommendation is to order monthly , i. e. , 12 times each y ear. Documentation Safety/Order ratio D3 Annual demand B6 Unit order cos t C6 Unit ca rry cos t D6 EOQ E6 , =SQRT(2*B6*C6/D6) Orders/year F6 =B6/E6 Order cost G6 =C6*B6/E6 Ca rry cost H6 =D6*E6/2 Safety stock cost I6 =D$3*E6 Total cost J6 =S UM (G6: I 6) Grading Note s 1. Remember to format order quantities as integers (i. e. , 0 decimal places). 2. This does not mean that the quantities are changed to integers. 3. I t is usually wise to leav e v ariables as real numbers. 4. Where needed, real v alues are changed to integers using ROUND , ROUNDUP , or ROUNDDOWN . 5. The work sheets in this work book can be protected. To protect each work sheet, select Review/Protect Sheet . 6. I nput cells should not be protected when sheet protection is activ ated. They should be conditional formatted to appear shaded when empty . 7. Columns G to J are done for bonus points. The implications are explained in Comments . LeongTY ProblemSet1a_Soln.xlsx/1.08 Paper Products © 2014 Leong Thin Yin. All rights reserved. Annual Sales # 50,000 Breakeven Fixed $ Variable $ Price $ Volume # Volume $ Years Variable % Profit $ 10,000 0. 23 0. 30 142,857 42,857 2. 9 77% 0 1 20,000 0. 18 0. 30 166,667 50,000 3. 3 60% 0 2 50,000 0. 15 0. 30 333,333 100,000 6. 7 50% 0 3 Test Cases To tal Sales Fixed $ Variable $ Price $ Volume # Volume $ Years Variable % Profit $ 20,000 0. 18 0. 30 160,000 48,000 3. 2 60% (800) 20,000 0. 18 0. 30 170,000 51,000 3. 4 60% 400 20,000 0. 18 0. 30 175,000 52,500 3. 5 60% 1,000 20,000 0. 19 0. 30 185,000 55,500 3. 7 63% 350 20,000 0. 20 0. 30 205,000 61,500 4. 1 67% 500 20,000 0. 21 0. 30 230,000 69,000 4. 6 70% 700 Comments 1. At current sales v olumes, option 1 tak es 2. 9 y ears to break ev en. The manual and electric hand tools currently in used are probably fully depreciated already . 2. At current sales v olumes, option 2 tak es 3. 3 y ears to break ev en. 3. At current sales v olumes, option 3 tak es 6. 7 y ears to break ev en. 4. Sales is howev er expected to grow and thus options 2 and 3 may break ev en sooner. Documentation Current a nnua l v olume C3 Fixed cost B6 Variable cost C6 Price D6 Brea k ev en v olume E6 =B6/(D6-C6) Brea k ev en s a les F6 =E6*D6 Brea k ev en y ea rs G6 =E6/C$3 Variable cost as % of price H6 =C6/D6 Profit $ I6 =E6*(D6-C6)-B6 Sales volume E14 LeongTY ProblemSet1a_Soln.xlsx/1.08 © 2014 Leong Thin Yin. All rights reserved. Grading Note s 1. M arginal contribution per unit sold is (Price – Cost). 2. Total marginal contribution = M arginal contribution per unit sold * Units sold. 3. Break ev en occurs when the Total marginal contribution = Fixed cost. 4. Equations such as these are not be giv en in real life. 5. You need to consult y our textbook , accountant, or just think on these basics. Evaluation Is the worksheet true to the specified requirements? Are all the required test cases computed? Are there enough test cases to surface possible problems? Is the worksheet simple and intuitive for another user to use? Is the worksheet idiot-proof and protected against tampering? Does this work sheet fits the computer screen well? Does it fit naturally well on paper within a page or two? Try print prev iew. Do not use font sizes smaller than those used here. That would be v ery user unfriendly . Do not reduce the magnification to fit the screen or printed page. Color is nice but not necessa ry . I t is useful if it helps user to understa nd better. Color work sheets ma y not come out nicely in bla ck -& -white prints. Color printout ha s to be rea da ble a fter photocopy ing in bla ck -& -white. Is the documentation comprehensive? Does not need to list all cells used in work sheet, but & * must be readable using just the printed copy (without the row and column headers) * must contain all the input and referenced cells * must contain all the unique formula cells with appropriate cell referencing * cell formulas when copied to other cells must still work Are there notes to explain rationale and use of the worksheet? Are these notes clear and easy to understand? Print and use this as a benchmark for y our school assignment or work project reference.
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 *********** ***********************

Needs help with similar assignment?

We are available 24x7 to deliver the best services and assignment ready within 3-4 hours? Order a custom-written, plagiarism-free paper

Order Over WhatsApp Place an Order Online

Do you have an upcoming essay or assignment due?

All of our assignments are originally produced, unique, and free of plagiarism.

If yes Order Similar Paper