Excel- Solver, Macro
PT Soejadi Motor Company
PT Soejadi Motor Company is a new local start-up motorcycle manufacturer based on the industrial island of Batam. Unlike other manufacturers, its motorcycles will have electrically powered hybrid engines that also run on inexpensive palm oil. To encourage local entrepreneurship, the Indonesian Government has granted it a special 10-year rent-free permit to build its factory in a special corner of its home-grown industrial park.
The land plot is triangular in shape and PT Soejadi can stake out as much land as possible so long as its external fencing is also triangular in shape to maintain landscape symmetry. In addition, the Government has stated that the total perimeter cannot exceed 5 km. The length of each side should also be a round integer.
Obviously, it is advantageous to have as big a piece of land as possible. Its building contractor has advised that fencing on each of the 3 sides will cost S$50 (side A), $30 (side B) and $20 (side C) per metre respectively. Its financial controller is only able to provide a maximum budget of S$120,000 for the fencing project.
Apart from fencing up its manufacturing site, PT Soejadi will also need to set up a manufacturing plant.
Currently, it has 3 designs with the following cost structure:
Design Type Fixed Cost Variable Cost
Plant A S$6 million $600
Plant B S$8 million $400
Plant C S$12 million $320
These plants have different efficiencies. The variable cost component refers to the cost of producing one unit of motorcycle. Over the next 3 years, the company expects to be able to sell between 38,000 and 40,000 motorcycles.
Imagine you are a senior manager of PT Soejadi Motor Company. How will you be advising the CEO of the company in making strategic investments in Batam? What kind of leadership will the CEO have to provide in this endeavour?
Question 1
This question requires you to practise effective principles of spreadsheet design and construction. Develop an Excel model to analyse the industrial site in Batam. Using the builtin solver tool, determine the ideal lengths of each side of the triangular land plot that PT Soejadi should request from the Government given the constraints stated. What will be the ideal land area in hectares? Add in an “Init” button with an attached macro called “Initialise” to set the initial dimensions of sides A, B, C to 10m, 20m and 20m respectively. Add another “Solve” button with an attached macro called “DoSolver” to activate the Excel solver tool to solve for the sides of the triangle.
(Hint: Use Heron’s formula to compute the area of a triangle given its 3 sides.)
(40 marks)
PLACE THIS ORDER OR A SIMILAR ORDER WITH US TODAY AND GET A GOOD DISCOUNT
