# Excel Project 2
Due Date: Feb 2
Important Websites: N/A
Q: What is this? (3rd highest digit of your ID#)% (2nd highest digit of your ID#)%
A: For example, if your ID number is 998908614, then 3rd highest digit is 6 (NOT repeated counting), and 2nd highest digit is 8.
It means: "A 6% price decrease leads to a 8% quantity increase", i.e. elasticity is - 8 / 6.
Q: How to find the equation for the demand curve?
A: You are given the elastisity and a particular point on the demand, then you can figure out the slope and intercept of the linear demand.
See:
YouTube Link
Q: What are constrains for question 4?
A: There are 1 TEU constraint, 1 weight constraint, and 2 ratio of # of containers constraints.
See:
YouTube Link
(Mistakes in the video: the ratio constraints should be applied on either number of containers (between 0.6 to 1) or TEU of containers (between 1.2 to 2) but NOT on total weight.)
(Also, you could use the weight per container in the table which is different for every country OR you could use fixed max-weight in the case too)
Q: How to install and use Solver?
A: Well ... You probably skipped WIT tutorial, but anyways ...
See:
YouTube Link
Q: How to assign parameters names (i.e. name tags)?
A: Select a column of names and values --> go to Formulas tab --> Create from Selection --> OK.
See:
YouTube Link
Q: Why aren't the optimal quantities not change when the data changes?
A: Solver is not a function, you need to use Macro/VBA (optional!)
See: (Optional)
YouTube Link
Q: How to write a business report?
A: I have no idea! Perhaps take a look at Adam's slides posted on Blackboard might help.
Notes from my Feb 1 Tutorial gives suggestions on possible ideas.
Q: What is the optimal lenght for the report?
A: Around 8 would be good. Be concise. Be complete. --> Be compact!
# Excel Project 1
Due Date: Dec 30
Important websites:
Interest Rates ;
Stock Price Old ;
Stock Price New
The following are the questions and answers I had and gave during my office hours that I would like to share with students who are interested:
Q: Where to find stock data from the Internet?
A: Use the above links and see:
YouTube Link
Q: How to compute the dividends?
A: Rearrange the formula given in the lectures: RET = RETX + DIV / Pt-1
where Capital gain = RETX = (Pt - Pt-1) / Pt-1
NOTE: you should use the dividend given by Yahoo Finance.
See:
YouTube Link
NOTE: In the video, the formula for DIV column is wrong! Div = (RET - RETX) * Pt-1 NOT dividing!
Q: How to compute SP500 and TB3MS return?
A: RET for SP500 = (Pt - Pt-1) / Pt-1 since there are NO dividends.
RET for TB3MS (i.e. risk-free rate) = (Oct 31 2012 bond return) / 100 / 12, i.e. last period's return.
Q: Why does Yahoo! Finance give the stock prices at the beginning of a month?
A: I don't know, just don't worry about it, VLOOKUP will solve the problem.
See next question.
Q: How to fix the data on, say Jan 1 v.s. Dec 31 when merging the data?
A: VLOOKUP(xxx, xxx, xxx, TRUE) will fix this when merging the data.
See:
YouTube Link (Updated!)
Note: the instructor uses end of month dates which gives wrong numbers, so you could use it too.
(i.e. no need to change to beginning of month dates as in the video)
Q: How to compute the the number of returns that are positive?
A: Use these functions: 1) AVERAGE(*); 2) STDEV.P(*); 3) COVARIANCE.P(*, *);
For marginal table: COUNTIF(*, *) and AVERAGEIF(*, *); For joint table: COUNTIFS(*, *, *, *);
See:
YouTube Link
NOTE: You need to do three joint distribution tables like the one I made: Stock A v.s. B, Stock A v.s. SP500, Stock B v.s. SP500.
Q: How to find 'optimal' portfolio with 1 risky and 1 risk-free asset?
A: Theory see:
FAQ Midterm 2 Page
You could just copy and paste the table of the example used in class and change the cell references.
You need to add portfolio return and portfolio risk at the end.
Q: How to find 'optimal' portfolio with 2 risky assets?
A: Theory see:
FAQ Midterm 2 Page
You could just copy and paste the table of the example used in class and change the cell references.
You need to add price of risk for both companies.
Q: How to find 'optimal' portfolio with the mixed 2 risky assets and 1 risk-free asset?
A: Theory see:
FAQ Midterm 2 Page
You could just copy and paste the table of the example used in class and change the cell references.
(These are NOT official answers. They are personal opinions from Young Wu and is not officially related to the course ECO204 in any way)