Problem 1: What-if analysis and problem optimisation:
A farmer has 100 acres to plant in wheat and rye. He has to plant at least 70 acres. However, he has only $12,000 to spend and each acre of wheat costs $200 to plant and each acre of rye costs $100 to plant. Moreover, the farmer has to get the planting done in 150 hours and it takes an hour to plant an acre of wheat and 2 hours to plant an acre of rye. The average profit is $500 per acre of wheat and $300 per acre of rye. The farmer wishes to examine planting alternatives and their impact on total profit.
Tasks:
a) Construct a spreadsheet model for the problem using Excel.
b) Perform a what-if analysis to determine the maximum total profit that could be achieved if only rye (no wheat) is planted, given the cost and time constraints.
c) Use solver to determine the number of acres of each crop (wheat and rye) that should be planted to maximise total profit. Ensure you use appropriate constraints for:
• The lower and upper limits to the total number of acres planted
• The planting costs
• The planting time
• The number of acres of each crop should be whole numbers (use integer constraints)
Problem 2: Goal-seeking analysis and simulation
Jina is developing a business plan and an application for financing for a new inner-city courier business. She intends to run the service for 5 years, and then dissolve it. She has developed the following information on various aspects of the business.
Demand
The courier service is expected to make approximately 40,000 deliveries per year. The average delivery fee is $12.
Vehicle costs
Jina plans to buy four new Toyota minivans for the business for a net cost of $20,000 each. The resale value for a minivan after 5 years is estimated at $7,000. The following describes the annual operating costs for each vehicle:
• Annual insurance and registration costs: $2,400
• Maintenance and repairs: $1200 in the first year, and increases by $500 in each subsequent year.
Jina plans to borrow the $80,000 required to purchase the minivan vehicles.
Fuel costs
Each vehicle is estimated to utilise an average of 4 litres of fuel per delivery. The vehicles utilise regular unleaded petrol, which currently costs an average of $1.20 per litre.
Labour
Four people are employed as courier drivers at rate of $20 per hour. They are each expected to work an average of 40 hours per week, 48 weeks per year. Business overheads (office, phone, etc.) are estimated to be 15% of the courier labour costs. Jina will be the manager, with an annual salary of
$65,000.
Tasks:
a) Based on the above information, develop a model using an Excel spreadsheet to project financial statements for Jina's courier business. The bank usually wishes to see the following components for financial performance on an annual basis and over the 5-year period: total income, total costs, and cash flow. The cash flow should be used to calculate the net present value of the 5- year investment. (The net present value of an investment is the value of the investment in ‘today's money'). The built-in EXCEL function NPV can be used to calculate net present value. Use a discount rate of 10% [=NPV(DiscountRate,CashFlowFor5Years)-LoanAmount].
Hint: Ensure that the model enables any of the values given to be altered without having to change the value in more than one place.
b) Use goal seeking to find how many deliveries per year would be required for Jina's company to break even; i.e., to achieve a net present value of $0.00.
c) Jina would like to know how fluctuations in demand will affect her business. Use a normally distributed expected annual demand with a mean of 40,000 and a standard deviation of 5,000:
=INT(NORMINV(RAND(),40000,5000)). Run 20 simulations of the model. Summarise the mean, standard deviation, minimum and maximum net present values from the simulations in a table. Based on these results, what recommendations for changes to Jina's business plan would you make?
Problem 3: Mining a political affiliation dataset
A political science research centre has invited you to build a classifier for predicting the USA-based political affiliation (Democrat or Republican) of individuals based on data relating to their support of various issues and policies. You are asked to use the WEKA data-mining tool to compare data mining algorithms and recommend a suitable classifier for the problem domain. You will need to provide a report of your findings, including justification for your recommendation.
Tasks:
a) The historical data for political affiliation classification has already been compiled into a WEKA data file: "vote.arff" (available via LMS). Load this file into WEKA and answer the following questions about the data:
i) How many features or attributes does the data contain?
ii) How many examples does the data contain?
iii) What is the name of the attribute that describes an individual's political affiliation?
iv) How many examples are affiliated with the Democrat party?
b) If a classifier is to be built for selecting a group of 40 individuals to invite to a prestigious event held by the Republican party, which performance measure: accuracy, precision or recall; do you think would be more important for this problem? Why?
c) Utilise 7-fold-cross-validation to compare the performance of the following classifiers:
• J48 decision tree
• Multilayer Perceptron (MLP)
• Naïve Bayes
Use the default settings/architecture for each classifier. Present the following results for each classifier (assign positive to Republican):
i) The confusion matrix using the format below: (note that WEKA may present this differently)
|
|
True Class
|
|
Positive (Republican)
|
Negative (Democrat)
|
|
Predicted Class
|
Positive (Republican)
|
(TP)
|
(FP)
|
|
Negative (Democrat)
|
(FN)
|
(TN)
|
ii) The accuracy measure
iii) The precision measure
iv) The recall measure
d) Recommend one of the three classifiers for this problem. Justify your answer based upon your answer in part b).