CAT Assignment 1 PDF

Preview

Summary

SMU - Assignment 1Class – G11/ GDue: 23 :59 PM, 09/02/20 20 , upload to elearn Assignment1 Folder.Answer each 1, 2 and 3 and bonus question in separate worksheets.Question 1 (1 Marks)Your CEO has asked you to evaluate a proposal to acquire RedTen a tech company that makescloud analytics software and...

Description

SMU - Assignment 1 Class – G11/ G12 Due: 23:59 PM, 09/02/2020, upload to elearn Assignment1 Folder. Answer each 1, 2 and 3 and bonus question in separate worksheets.

Question 1 (1.5 Marks) Your CEO has asked you to evaluate a proposal to acquire RedTen a tech company that makes cloud analytics software and sells it on a subscription basis. Redten forecast’s for the next three years is that it will earn profits of $4.6 million next year, $5.85 million the following year, $7.2 million in the third year. You expect to be able to sell RedTen for a price of $22 million in the fourth year. Your company expects to earn a minimum 40% per year on its investments. The rate at which you discount future monies is 10% per year. Decide whether you will recommend to purchase Redten or not. a) Create a table computation that will tell you the base price to pay today for RedTen. b) On negotiation, RedTen says that they will accept 1/3 of the total payment today, 1/3 in a year, and 1/3 in two years. What is the actual price you are paying currently for RedTen, at the end of the entire transaction in year four what is the total projected profit in today’s terms. c) If you assume the correctness of the estimates in a) and the results of the negotiations in b) what is the actual rate of return on your money?

Question 2 (4 Marks) You are the procurement manager of Ah Kong Trading company with 200 employees. You are planning to add daily transport benefits to your employees and have received three quotations from three transport companies Merlion, Delta and Max. Users can simply contact the chosen company and request transport. 

Merlion fare charges are time sensitive and are as follows. From 7 to 9 AM the charges are $1.12 per km, From 9 AM to 4:00 PM the rates are $0.72 per km From 4:00 PM to 7:00 AM the rates are $1.18 per km.







Delta Taxis charges a per trip starting fee (fixed charge) of $3.80 at the start with $0.27 for every 250 meter’s charged for the first three kilometers. After three kilometers the charge reduces to $0.21 for every 300 meters. Max Transport charges a per trip starting fee (fixed charge) of $5.00 with $0.28 for every 250 meter’s charged for the first three kilometers. After three kilometers the charge reduces to $0.19 for every 300 meters. Delta and Max have a minimum distance computation of 3 km (i.e even if the person travels less than 3 km they still chare for 3 km)

80% of the time your staff travels to office at 8:00 am and leaves by 6:00 pm. However rest of the time staff comes in late (i.e they start after 9:00 am) but they still leave office at 6:00 pm. The average travel distance from home to office is 15.2 KM, and total number of working days in a month is taken to be 22 days. a)

b)

Your objective is to calculate the maximum cost per employee for each supplier and choose the cheapest option. Note that the formulas must work when using the copy across (horizontally) function for all 3 companies Your boss has also asked you what will happen if a greater percentage of the employees travel late (i.e start after 9:00 am) and at what percentage will your choice of taxi company change. Document your answer in the spreadsheet

Note: • Per Trip Starting fee – this is charged when the passenger first boards the taxi • Starting Rate – this is charged at the start of journey (0 km onwards) and will be used for all block distances that are started within the first 3 km • Extended Rate – this is charged after the first three km and when Starting Rate charge is completed for all its block distances started • Your spreadsheet must all be formatted to two decimal places. • 80% of the time means 80% of time staff travel to office in a month. • You need to follow the design layout below exactly.

Question 3 (2 Marks) Lazely Inc. is an internet shopping portal with ambitious plans for the coming financial year. Last year every week on average they have 100,000 visitors to their website. Of their visitors, 2% shop and place orders (called purchasers). These orders average $23 each per purchaser. Thus this week they are taking orders for $46,000 this week (considered week 0, which is the end of the last financial year). For the upcoming year the board has set a target to increase the visitors by 2% per week. They expect the percent who place orders to stay the same. They have also set a target to increase the average amount ordered by $0.15 per week. a) Create a worksheet to predict your user and revenue growth over the next year based on the boards expectations. You should have columns for the week numbers (0 to 52),

rows for the number of visitors (integer-round up), number of purchasers (integerround down), average amount ordered, and total ordered for each week. b) Also the marketing director has asked you to compute the advertising budget given the above target, you are currently paying $11.50 for every new visitor acquired(Customer acquisition cost) and expect the acquisition cost to rise by 20% for the three peak holiday periods of weeks 1 to 6, 23 to 27 and 44 to 52. You buffer an additional 15% to your budget (because of wastage advertising to existing customers). Create a table to work out your required advertising budget (weekly and grand total). c) Use a data table to show how will the Marketing budget figures change if the board increases the target weekly growth rate to 2.5% or falls to 1.5% (i.e show what will be the marketing budget for growth rates of 1.5%, 2% and 2.5%) Note:Follow the model below when building the table.

Question 4 (bonus question – Optional to be done) You are a regular customer at an internet cafe have gotten lucky and have won a prize of $2000 for being the 100th customer of this café. You are offered with two options: (I) take the money as a variable installment at the end of each month for the 6 months (Month 0 to Month 5) as follows $200 , $300, $300, $300, $400, $500 (Month 0 means now). Or (II) take the $2000 now which will incur an additional $25 processing fee; Which option should you take to maximize the value you get, assuming the yearly interest is 5%? To get the credit, you must explain how you arrive at that conclusion and the formula used.

Assessment The assignment will be graded on a scale from 0 to 100%. Scores of 70% to 90% are given when the assignment is essentially done completely and correctly. Scores 90% to 100% are reserved for complete and correct work where extra initiative or design innovation clearly sets the completed work above the simple and perfunctory completion of the assignment. Some of the useful spreadsheet engineering and style best practices are as follows:  The model designed is intuitive and formulas used are correct and rigorous  Providing accurate documentation of all formulas used  Layout o Read top-to-bottom and left-to-right, create functional areas  Formulation – be concise  Formatting – format for description, not decoration o Neatly placed rows and columns, wrapped cells to make table more presentable, good use of merged cells, alignment of cells  Error checking  Numbers formatted correctly to two decimal places

Spreadsheet design guidelines reference Bewig, Philip L. (2005) “How do you know your spreadsheet is right?” http://www.eusprig.org/hdykysir.pdf...