A 4-year production capacity planning for a new computer printer Lingnan Printer (LP) is planning to build a production plant to produce a new computer printer. LP wishes to decide the production capacity C (maximum pr

Lingnan University
Department of Operations and Risk Management
First Term 2025 – 2026
CLD9014 Spreadsheet-Based Decision Making
Assignment 1
Designedforthe lecturesinTopic 1
Total = 100 Marks
Submission Date: By 10:50 am, 25 September 2025
Submission Notes:
1. This assignment includes a case study, and you are required to answer all its parts.
2. Your answers should be clearly and properly presented in English.
3. Please provide your name and student number in your Excel worksheet.
4. Name your assignment solution file using the following format: “CLD9014 25_26_01 Assignment1 Your Name
(Student Number).xlsx”.
5. Apply best practices in Excel modelling—such as flexibility, readability, and appropriate formatting—to develop your
model and answer the question.
6. You may use the provided partial Excel template, “CLD9014 25_26_01 Assignment1_students.xlsx,” available
on Moodle for this assignment.
7. Include clear units for all numerical answers; failure to do so will result in your answers being marked incorrect.
8. Submit the final version (not a draft) of your assignment as a single electronic file via Moodle by the due date. No
hard copy submission is required.
9. Late submissions will not be accepted and will receive a grade of zero.
10. Warning: Any cases of plagiarism will be referred to the Student Disciplinary Committee for review.
2
A 4-year production capacity planning for a new computer printer
Lingnan Printer (LP) is planning to build a production plant to produce a new computer printer. LP wishes to decide the
production capacity C (maximum production quantity) of a production plant since it is very costly to change the settings
of the production plant once it is built. From a marketing research report, LP has estimated that the annual demand of
the new printer in the first year (year 1) follows a probability distribution as indicated in the following table and demand
for LP is expected to grow at a rate of 5% each year for the next 3 years (Year 2 and Year 4).
Demand (printers) Probability The expected demand for year 1 can be
calculated by multiplying each demand
level by its corresponding probability
and then summing these products.
10000 0.0800
12000 0.1500
14000 0.2500
16000 0.3000
18000 0.1500
20000 0.0700
If LP builds a production plant with a capacity of C units, then LP will incur a one-time building cost of $(100xC) in year
1. The building cost will not incur in year 2 to year 4.
LP can produce S units of the new printer each year where S should be less than or equal to C.
LP will sell each printer at a price of $1,000.
Each printer produced incurs a variable production cost of $500.
Each printer sold incurs a distribution cost of $20.
LP estimates that it will incur a cost of $50 per year to operate each production unit no matter whether the production
unit is used or not.
LP also plans to spend $250,000 to promote the new printer in the first year and will increase advertising spending by
2.5% each year after.
Finally, assume that the discount rate in this case is 4% over the next 4 years.
Year-end Profit (Sales revenue – total cost) should be calculated at the end of each year.
You are required to do the following tasks.
1. Define range names for all the input parameters and the decision variable, and clearly show the range names on
the worksheet. [10 Marks]
2. Construct an Excel model to calculate the net present value of the investment over the four years (From year 1 to
year 4) for any given capacity level C. [30 Marks]
3. Use a one-way data table to analyze how the level of capacity C built impacts the net present value (NPV) of the
total profit over the four-year period (year 1 to year 4). You should change the capacity level from 10,000 printers
to 20,000 printers with an increment of 2,000 units. [25 Marks]
4. Based on your result of question (3), create a scatter chart to demonstrate how the net present value changes
with the capacity level C from 10,000 printers to 20,000 printers. You should label your chart clearly. [10 Marks]
5. What capacity level C will maximize the company’s expected net present value of total profit over the 4-year period
(year 1 to year 4) Highlight the largest NPV among the list of NPVs using conditional formatting. [20 Marks]
6. Provide your conclusion with a text box below the excel model. [5 Marks]