Green Urban Solutions and Technology Pte Ltd (GUST) is a local business start-up that has chosen to focus on commercially viable sustainability solutions for urban environments. In Budget 2021, the Singapore Government had announced that petrol-driven cars would be completely phased out by 2040. New car registrations would only be for electric vehicles (EV). The Government had also set a target of installing 60,000 charging points (CP) by the year 2030 from some 1.900 CP's presently. These CP's will be operated by private enterprises. GUST decided to pursue such an opportunity. GUST plans to roll out Type 3 superchargers that can re-charge an EV from 0 to 80% in about 20 minutes. Each supercharger constitutes one CP. These chargers will be installed island-wide in clusters of 2 to 4 CP's per location. Every such CP will require a standard-size parking lot which GUST will rent from building owners and car-park operators. These CP's will operate on a self-service basis over 24x7 hours. EV customers will use a mobile phone app to operate the CP and pay for the charging service. Every customer will be allowed up to 30 minutes to charge their cars at a CP. Penalties will be imposed on those who stay beyond the 30-minute charging timeslot. On a typical day, a CP can be used for up to 48 charging timeslots. EV cars and EV chargers have very few parts and are known to be robust and reliable, requiring very low maintenance, if any. The chargers can be operated continuously over 24 hours with almost no operational impediment. It is the ideal digital solution for sustainable urban transportation and GUST hopes to be a significant player in this economic sector. This assignment requires you to apply spreadsheet modelling techniques to analyse and solve business problems in this case study. Note that all numerical results must be stated in words explicitly, and not left as-is in your models. (a) Develop a spreadsheet model that will enable GUST to analyse the number of Charging Points (CP) that it needs to install and the utilisation rate of these CP's. You may organise your model as shown in table 1 below. Cars to charge Per Day 10,000 No of CP points 800 No of Charging Slots 38,400 Utilisation 26.0% Table 1: Simple CP Analysis Model The example shows that if GUST had 800 CPs and 10,000 cars needed to be recharged. the utilisation rate of its infrastructure would be 26%. [Hint: Utilisation Rate = Number of Charging Slots Used/Total Number of Charging Slots Available* 100%] All the cells shown shaded (in yellow) must be automatically computed using appropriate Excel functions and formulae. Hardcoding is not permitted. From a business viewpoint, what would be the optimal number of CP's if 15,000 cars needed to be charged? Explain how you derive your answer using the model. (12 marks) (b) Develop a second model for GUST to determine the potential profit from this venture. You may copy your model from part (a) above and enhance it further for this part. You may wish to factor in the following parameters. The cost of electrical equipment for each CP is $90,000 inclusive of hardware, installation and maintenance by the supplier over a 10-year period. Rental for parking lot per CP is expected to be $500 per month. Cost of Electricity is $0.22 per kWh. The average charge per EV car is 40 kWh for each 30-minute slot. GUST plans to charge the customer $0.33 per kWh, which is a 50% premium. For this analysis, assume that GUST has 15,000 customers per day and has installed the optimal number of CPs from part (a). Equipment is evenly depreciated over 10 years. What is the optimal Annual Gross Profit? Is this optimal profit achievable? Why? [Hint: Gross Profit = Sales - CP Depreciation - Parking Lot Rental - Electricity Cost] Using your model and scenario for this part, what is the breakeven number of charging points needed? What is the corresponding utilisation rate? Explain how you obtain this answer. [Hint: At breakeven point, the Gross Profit is 0.] (20 marks) Presentation, layout and formatting Your models for Q1 must be neatly laid out and presented. You must apply appropriate colours, alignment, labels and formatting of numbers. Documentation and legend are expected. (8 marks)