INTRODUCTION

The purpose of this MS Excel Major Assignment is to give you some experience with several of the advanced “decision support” features of the Microsoft Excel spreadsheet.  These features include:

• Data analysis using pivot tables
• Comparison of different scenarios using scenario manager
• Working with multiple worksheets
• Spreadsheet organisation using grouping & outlines
• Advanced MS Excel functions

DECISION MAKING SCENARIO

The purchase of a house usually entails some exchanges of prices between buyers and sellers.  The realtor is there to facilitate the transactions.  The seller has an asking price but typically settles for less.  The commission paid to Happy Realty (for property priced above $400,000) is 9% commission on the actual selling price for the first $200,000, and a further 2.5% for the remaining selling price.  A flat commission of 7.5% is charged (for property priced at $400,000 & below). The realtor in turns gets 40% of the total commission paid to Happy Realty.

Happy Realty wishes to encourage its realtors to try to sell the house as close to the asking price of the sellers as possible.  To do that Happy Realty will pay additional bonus for house sold at a certain percentage of the asking price.  The distribution of extra bonus is calculated as follows:

• A extra 1.65% bonus of the actual selling price is paid to the realtor if he/she is able to sell the house at 95% or more of the asking price.
• A extra 1.25% bonus of the actual selling price is paid to the realtor if he/she is able to sell the house sold at 90% or more of the asking price.
• A extra 1.05% bonus of the actual selling price is paid to the realtor if he/she is able to sell the house sold at less than 90% of the asking price
• No extra bonus will be paid for houses sold at less than 80% of the asking price.

Happy Realty revenue is calculated from the commissions paid by the seller minus the commission and extra bonus paid to the realtor.  Calculate the percentage of $ earned for each home based on the actual final transacted price.  Develop a worksheet to be used by the CEO of Happy Holdings Group of Companies to allow him/her to identify the key profit earned at Happy Realty.

Also use pivot tables to organise Salesperson’s commission by month with total commission for the first half of the year for each salesperson.  Also in another separate pivot table, organise your data to reflect the net profit by house and by month.

REQUIREMENTS

You are required to create on the 1^st spreadsheet (Commissions) a spreadsheet-based decision support model that allows the CEO of Happy Holdings Group of Companies to understand how the economic climate will affect the revenue earned at Happy Realty to compare different scenarios (e.g. Varying percentages collected from owners: 7%, 9%, 11% for the 1^st $200,000; the 2.5% is fixed for all scenarios).  Rather than having a separate model for each scenario, you are expected to design a single model and employ “scenarios” to change only those aspects of the model that varies with the scenarios being considered.  At the same time, he is able to use the same workbook to forecast income for his group of companies.

Use advanced MS Excel built-in functions (IF, HLOOKUP, VLOOKUP, etc.) in your spreadsheet wherever you see fits.  Also use pivot tables to organise Realtor’s commission by month with total commission for the first half of the year for each realtor.  Also in another separate pivot table, organise your data to reflect the net profit by house and by month.

On a 2^nd worksheet (Forecast), also plan a 5-year forecast of the Group’s corporate taxable income based on the following assumptions:

Organise your spreadsheet (if possible) into grouping & outlines.

Total Corporate Taxable Income = Group Income – Other Operating Expenses

On the 3^rd spreadsheet, (Loan) prepare a worksheet for all salespersons to use with the assumptions that if customer needs to take a house loan, it will normally be 75% of final transacted price with 25% being down payment.  The loan interest rate is prime rate (3%)  plus 0.75%, (assume prime rate to be 3%, and can be changed anytime).  Period of loan is usually 15 years.  All price used are net of GST and PST.

Calculate the following: (You may pick one of the houses listed in the 1st worksheet)

• Purchase Price of a house (may use linking worksheets feature)
• Monthly mortgage payment & Total house payments (including down payment) over the 15 years
• Total Interest for the house loan with Interest & Principal paid per month
• Starting & Ending dates of payment
• Beginning Principal & Ending Balance at the end of each month till the end of the loan

Example: 

…

…

DESIGN ISSUES

The following is a short list of generic design issues that you should consider when building your application:

• Never use a number in a formula. The purpose of a separate table of assumptions is to allow you to identify your assumptions and change them easily
• Named ranges and cells should be used where practical to make your formulas more readable.
• Use scenarios (instead of 2 separate workbooks) to compare situations that share the same basic model, but which have different values for critical decision inputs.
• Create 1st 2 spreadsheets in ONE workbook named Happy.xls and the 3rd spreadsheet in another workbook named Dream.xls and link them. Use linking formula across workbooks/worksheets wherever possible.

SUBMISSION

You are required to submit all relevant softcopies of your designed spreadsheets via email by the deadline.  You are required to e-mail to me with the e-mail subject (example FirstName_LastName_Campus) (example: your subject line should read JohnSmith_NW or JohnSmith_DL).

You should also make a backup of the system.  Late submissions will not be graded.

4

DATA (You MUST use the following data for your assignments.  )

Sales for the 1^st 6 months as follows:

Using your textbook (especially Appendix, Part C), identify the error in each sentence. Correct the error using Track Changes in Word.

1. Many students fail to understand the importance of proofreading their work and it shows in their project grades.
2. The report will be distributed to Operations, Research and Development and Accounting.
3. Just as we finished eating the bell rang.
4. Connie my Technical Communication instructor is looking forward to her winter break.
5. One of the many possibilities, is editing the draft to earn a higher grade.
6. I still need one thing to ace this assignment; a charming personality.
7. My instructor asked me if I knew what I was doing for last week’s assignment?
8. It is clear to me that this sweater is hers’.
9. She replied “Yes, that’s the correct answer for the last question.”
10. This textbook is a newly-acquired offering at the bookstore.
11. My accounting instructor assigned 3 textbooks.
12. 25 people showed up to the meeting on Thursday.
13. The university I attend is located in mankato, Minnesota.
14. I have always preferred pepsi over coke.
15. The screw measured .025 inches.

1) Consider the function f(x)=3x^2+4x-7
What is the average value of the function on [-2,5]?
2) Consider the function f(x)=4x^3-3x^2+6x-9
What is the average value of the function on [-2,4]?
3) Consider the function f(x)=5x^4-4x^3-3x^2+8x-12
What is the average value of the function on [-3,1]?
4) Consider the function f(x)=3cos(x)-5
What is the average value of the function on [-π,π]?
5) Consider the function f(x)=3e^x+4
What is the exact average value of the function on [0,3]?
Note: Maintain fractions if applicable.
6) Consider the function f(x)=4/x+8. (Supposed to say 4 root x, then outside the root is plus 8).
What is the exact average value of the function on [4,9]?
Note: Maintain fractions if applicable.
7) Consider the function f(x)=3x^2+2x-4
What is the exact value of c in the interval [-2,4] such that f(c)=fave?
Note: Maintain fractions if applicable.
8) Consider the function f(x)=5cos(x)+7
Find the exact value(s) of c in the interval [0,2π] such that f(c)=fave?
Note: For multiple answers, separate using commas and arrange in ascending order. Round
to the nearest hundredth.
9) Consider the function f(x)=4e^x+7
What is the exact value of c in the interval [0,5] such that f(c)=fave?
Note: Maintain fractions if applicable.
10) Consider the function f(x)=3/x+8. (Supposed to say 3 root x, then outside the root is plus 8).
What is the value of c in the interval [4,9] such that f(c)=fave?
Note: Round to the nearest hundredth.

Attempt THREE questions
Calculators are NOT permitted

Q.1 (a) Is i++; an atomic operation (in Java) on the int variable i? Give a brief
explanation.
(2 marks)
(b) Briefly describe the differences between processes and threads in
Java.
(2 marks)
(c) i) What is the meaning of the term liveness in the context of
concurrency?
ii) Briefly describe the three most common liveness problems. You
may use examples to illustrate your description.
(4 marks)
(d) lock() and tryLock() are methods of which Java interface? Name one
Java class that implements this interface.
(2 marks)
(e) Can constructors be synchronized in Java? Give a brief explanation.
(2 marks)
(f) An application that creates an instance of Thread must provide the
code that will run in that thread. There are two ways to do this:
provide a Runnable object or subclass the Thread class. Give a brief
explanation focusing on the advantages and the disadvantages of
each technique.
(3 marks)
(g) What is the output (i.e. printed) of the following code? Give a brief
explanation.
public class MultiThreading {
private static class MyThread extends Thread {
public MyThread(ThreadGroup tg, String name) {
super(tg, name);
}
@Override
public void run() {
System.out.println(Thread.currentThread().getThreadGroup().getName());
}
}
CS313 Page 3 of 5
public static void main(String[] args) {
ThreadGroup myThreadGroup = new ThreadGroup(“myThreadGroup”);
MyThread myThread = new MyThread(myThreadGroup, “myThread”);
myThread.run();
}
}
(5 marks)
Q.2 (a) Briefly describe the following Java Thread states: RUNNABLE,
WAITING and TERMINATED.
(3 marks)
(b) According to the Java API, specify two methods that can change the
state of a thread from RUNNABLE to WAITING.
(2 marks)
(c) Briefly describe the difference between the notify() and notifyAll()
methods.
(2 marks)
(d) Briefly describe the differences between daemon and non-daemon
threads in Java.
(2 marks)
(e) Briefly describe three techniques available to Java programmers to
implement synchronization. You may use examples to illustrate your
description.
(3 marks)
(f) (i) Briefly describe the following mutation operators for concurrency:
o MSP – Modify Synchronized Block Parameter
o RSB – Remove Synchronized Block
o RCXC – Remove Concurrency Mechanism Method-X Call
o RFU – Remove Finally Around Unlock
(ii) Specify which concurrency bug pattern each mutator operator can
cause.
(8 marks)
CS313 Page 4 of 5
Q.3 (a) What is the difference between client-server and peer-to-peer architecture?
(2 marks)
(b) What is the response time to request and receive a file on a non-persistent
HTTP connection? Explain why it takes this long.
(3 marks)
(c) Explain how a HTTP request is handled when a network uses a web cache.
(4 marks)
(d) (i) Given the following data calculate the UDP checksum:
1000 0110 0101 1110
1010 1100 0110 0000
0111 0001 0010 1010
1000 0001 1011 0101
Show your working.
(4 marks)
(ii) Given the data and the checksum received at the receiver how do you
check if it is corrupt?
(1 mark)
(e) What are all the situations that can occur in a stop and wait reliable data transfer
protocol when sending data? How are these situations handled?
(6 marks)
Q.4 (a) Explain the functions of the network layer.
(2 marks)
(b) What network architecture does the Internet use? Name an alternative
architecture and how it differs.
(2 marks)
(c) What protocol is used to assign hosts IP addresses? How does this protocol
work?
(5 marks)
(d) What is the main difference between IPv4 and IPv6?
(1 mark)
(e) Describe possible services offered by a link layer protocol.
CS313 Page 5 of 5
(5 marks)
(f) Explain the simplest form of parity checking. Give an example.
(2 marks)
(g) Describe 3 properties of link layer switching.
(3 marks)
END OF PAPER

Q1) Design a Knapsack public key cryptosystem, using this system, encrypt the plaintext “BHP”. Show the details of the design process (give the selected values with explanation why selected them) and encryption process. Include in your answer Octave codes used. Use the ASCII or Unicode  code to convert the plaintext characters to numbers. Finally, show how to decrypt the obtained cipher values to get the plaintext character back.       (LOs 2,4) [6 Marks]

Q2)   Answer each of the following:

• Explain why the security of the Monoalphabetic ciphers is easy to compromise?
• Define the “hashing”, then explain why hashing is used creating a digital signature.
• Distinguish between the Stream Cipher System and Block Cipher Systems, name one example for each system.                                                        (LO 4) [3 Marks]

Q3) Affine Cipher is an example of a Monoalphabetic substitution cipher. The encryption process is substantially mathematical done by using the following formula:

C = (P ∗ ?1 + ?2) mod 26.

Where k1, k2 are two integers representing the key (selected randomly), C is the ciphertext value, and P is the plaintext value. Where C and P integers with values between 0 and 25.

• Write an Octave programme to implement this system, run your programme using the   Plaintext: “the quick brown fox jumped over the lazy dog”. Show the obtained ciphertext with the selected values of the keys k1 & k2.                                         [4 Marks]
• Given that the plaintext “s” mapped to ciphertext “W”, plaintext “x” mapped to ciphertext “Z” when encrypted with Affine Cipher. Determine, mathematically, the values for the two keys K1 and K2.                                                                                      [4 Marks]

(LOs 2,4) [8 Marks]

Q4) Given the following cipher text which has been ciphered with Mixed Alphabet Monoalphabetic Cipher:

UZQSOVUOHXMOPVGPOZPEVSGZWSZOPFPESXUDBMET SXAIZVUEPHZHMDZSHZOWSFPAPPDTSVPQUZWYMXUZU HSXEPYEPOPDZSZUFPOMBZWPFUPZHMDJUDTMOHMQ

By using statistical method to exploit the weaknesses in this cipher, you can conclude that there are two plain text letters that correspond two letters in the above ciphertext. Give these letters justifying your conclusion in detail.

(LO1) [ 3 Marks]

1. How many young people are there if there are 35 old people and the ratio of old to young is 7 to 1?

2. If 28 out of 100 persons, on average, prefer to watch action movies, what is the nearest full percentage of those people, rounded to the nearest multiple of ten?

3. One and a half pieces are required for one man to eat. There are four and a half portions required for three men to eat. How many servings are required if there are twelve men eating?

4. A beverage with five measures of pineapple juice, one measure of orange juice, and three measures each of lime juice and orange juice. Five beverages will need to be made, so

5. A t-shirt costs $4 extra for every ten people who purchase one. The t-starting shirt’s cost was $24. The t-shirt is purchased by 22 individuals. What is the current price of it?

6. An organization that provides physical training maintains a ratio of at least three instructors to every ten students. The number of teachers must always be even. What is the minimal number of instructors needed in the organization if there are 110 pupils there?

7. A group of pals, 72% of whom enjoy trekking in their spare time. Everyone else in the group does not. How may the number of friends, rounded to the next first decimal place, who don’t enjoy hiking be effectively stated as a percentage of the entire group?

8. A project is being worked on by a group of writers. The job should be finished in 140 hours. The team initially consists of ten writers. The anticipated project completion time drops by 4 hours for each additional writer on the team. The anticipated project completion time grows by 4 hours if a writer departs. The crew grows by 50% in size. The group then includes one more person. What is the updated projected date of project completion?

9. Two pieces of soft leather, three pieces of hard rubber, one piece of wood, and two pieces of cotton are used to make one pair of shoes. How much toughened rubber and cotton are required in total to create 12 pairs of shoes?

10. Over the course of eight months, an advertising-supported magazine makes $120,000. The magazine’s earnings will increase by 20% if it adds two more ad places. Advertising space and revenue have a linear connection. In eight months, the journal is able to make $156,000 from advertising. How many ad spots were made available?

11. Two men are attempting to strengthen their cardiovascular systems. One man runs five miles in the early morning. The second man runs a distance that is two times as long as the first, plus one additional mile. How far did the two men run together overall?

12. There are 148 footballs in a sports facility. In the first six months of the year, 28 footballs are either lost or irreparably damaged. Then, the facility buys 14 footballs. A total of 36 footballs are lost or irreparably damaged throughout the second half of the year. How many playable footballs does the facility have left at the end of the year?

13. The male has two interests. His primary interest is shooting, and he also enjoys wrestling. He must spend two fewer hours grappling for every hour more he spends shooting due to travel times and other issues. The man first spends 12 hours of wrestling and 20 hours of shooting per month. He chooses to spend an additional four hours filming the next month. How many hours does he wrestle in that month?

14. A worker puts in six hours a day and receives $10.50 an hour. How much money does she make in a week if she works five days a week?

15. Three pals choose out a hotel room to reserve. They create damage while they are here and are given a bill when they check out. The $682 total amount due. The first friend pays $242 toward the tab. How much does each friend pay if the two other friends split the remaining cost equally?

16. A company encounters a challenging period. At first, it offered its salespeople a wage of $20 per hour. Due to the challenging circumstances, this wage has been decreased by 25%. When business picks up, the proprietors raise each employee’s pay by 50% of what they were originally paid. How much does a salesperson currently make per hour?

17. A company has a maximum possible workforce of 24 employees. The company pays each employee $15 per hour. The company is currently working with only half of its full personnel. What is the total amount paid by the company if the present staff works for 6 hours?

18. A woman chooses to travel a distance by car. She travels at a 60 mph average. She has traveled a total of 12000 miles. What is the total distance she has traveled after three days, assuming she drives for 10 hours per day at an average speed?

19. A business that offers outdoor training buys two parcels of property. 180 feet long is the first piece of land. The second piece of property is half as long as the first. How much land are on each plot together?

20. A man creates a brand-new bank account. On the last day of each month, interest is paid on the account’s balance. In the first half of the month, he deposits $140, and in the second, $540. 10% is the interest rate. On the first day of the second month that the account has been open, what is his balance?

21. A man spends $780 on one work of art and $2800 on another. The price of the art carries a 10% tax. What does the man pay in total?

22. Two types of tables are sold by a tiny local table manufacturer. The price difference between two types of tables is $40 and $56. The table manufacturer sells 120 of the first type of table and 600 of the second type over the course of three months. What is the appropriate ratio between the first table’s sales and the second table’s sales?

23. Eight toys are made in a day by a factory. How many toys does the factory manufacture in four weeks, assuming it produces the same number each day and is open seven days a week?

24. A man wants to put on more weight before a sporting event. The man is 220 pounds in the outset. The man increases his body weight by 5% during the first week of trying, and then he increases it by an extra 5% during the second week to reach his goal weight. What is the man’s new weight after this two-week period?

25. A company uses vehicle A and vehicle B, two different types of transport vehicles. Vehicle A can accommodate 24 passengers, while Vehicle B can accommodate 18 passengers. The company uses three vehicle As and two vehicle Bs for a specific journey. All of the vehicles are completely occupied. How many passengers are traveling?

1)
a) Discuss Neo-Classical Theory and critically examine the assumptions.
b) Solve the following game as a neo-classical economist.
c) What should be the move of player I in the first node.
d) Now think like behavioural economists and analyse the game. On what factors
would the outcome of the above game actually depend?
e) Under what conditions would player I choose the move X.
2) Explain with suitable example the concept of present bias in decision making.
3) Show that the problem of missing females is also a problem of present bias in our
(society’s) judgements.
4) Write a short note on nudge. Give examples and illustrate how can it be used in public
policy spaces.
5) Show that reciprocity and patience are precondition for cooperation to hold between
two parties.
6) What are heuristics? Write a short note on (Give suitable examples)
i) Representativeness
ii) Availability
iii) Anchoring and adjustment
7) Discuss the problem of low Female Labour Force participation as a behavioural
problem.
8) Formalise a choice problem under bounded rationality where behaviour is a function of
pears behaviour.
9) What determines the plan horizon of an agent. Illustrate using a suitable model.
10) In a class experiment two options were given:
a) Option A is a guaranteed return of Rs100
b) Option B is where one gets Rs 1000 if head comes in a coin toss or else 0.
What is the expected payoff from option B?
80% students choose option A, even though expected payoff of option B is greater
than the payoff from choosing A. What is the reason?
11) What is system1 and system2 type of thinking?
12) Describe using a game theoretical model the concept of fairness.
13) Two people decide to meet at a pre-decided venue between 6 to 7PM. Both are equally
likely to come at the venue at any time between 6 to 7PM. If they trust the other person
they will wait for some time before they leave. Trace the relationship between the
probability that they meet with trust.
14) What are the types of biases that may arise due to the following heuristics:
i) Representativeness
ii) Availability
iii) Anchoring and adjustment
15)
Consider the following static optimisation of making a choice between two paths,
red or blue. The red path gives a payoff of 8 while blue gives a payoff of 7.
Formally we may write the problem as:
Now consider the following dynamic problem:
There are 3 agents A, B, C.
A makes a decision till time = 2; B makes the decision till time = 4; and, C makes the
decision till time = 6. Their maximum value functions are given by
V?
(t = 0) = max
{??
}
∑ ?(??)
2
?=0
V?(t = 0) = max
{??
}
∑ ?(??)
4
?=0
V?
(t = 0) = max
{??
}
∑ ?(??)
6
?=0
Find, V?, V?, V? and optimal path, {??
} for each.
Why is the path different for each candidate?
?(?) = ൜ ? ? {??? , ????}
8 ?? ???
7 ?? ????
Ar???? ?(?) = ???
?( ? ) = max
?
?(?) = ? , ?ℎ??? ?(. ) ?? ?ℎ? ??????? ????? ????????
16)Consider the above dynamic problem for candidate D and E, whose value function is
given by V?( ? = 0) = max
{??
}
∑ ??
??(??
)
6
?=0 and V?
( ? = 0) = max
{??
}
∑ ??
??(??
)
6
?=0
respectively, where ?? = 0.2 and ?? = 0.8
Find V?, V? and optimal path, {??
} for each.
17) X and Y are two individuals who have the following hypothesis regarding workforce
participation of females:
log (
?
1−?
) = ?0 + ?1??? , ?ℎ??? ? ?? ?ℎ? ??????????? ?? ? ?????? ?? ????;
?0 = −4.11 ??? ?1 = 0.48
X’s mother is working while Y’s mother is a housewife. Therefore, X has a prior belief
that ? = 0.8 and Y has a prior belief that ? = 0.15. They both are friends now and
therefore are exposed to similar types of people around them. They observe education
level as well as working status of their 10 female friends. This leads to an update of
their believe over hypothesis. Table shows their observation. Find the updated
probability of hypothesis for each X and Y respectively, given their observation.
Status Education
Working 15
N

• Match the words to language acquisition: (7 pts)
  1. Sounds idiomatic
  2. Sentences phonology
  3. Word usage LAD
  4. Word learning overgeneralization
  5. Pre-meaning infer grammar
  6. Common rules babbling
  7. Meanings shaping
  8. Forming plurals lexicon
  9. Selective utterances syntax
  10. True version semantics
  11. No relationship                                                 imitation
  12. Innate tendencies pragmatics
  13. Special version scaffolding
  14. Invitation for newness                                         motherese

• Name the acuities developed during infancy.  1 pt

• What are the temperament dimensions? 2 pts

• What is the opposite of? 3 pts
  1. Non-REM
  2. Expressive style
  3. Signaling
  4. Contingent
  5. Causality studies
  6. Semantic relations

• What does Parten and Piaget play theories differ? 2 pts

• What theory/theorist is reflected in the Ape Story? 2 pts

• What did Lil Genie’s experience teach us about development? 2 pts

• What is mindset and why does it matter? 2 pts

• What is the significance of the Word Gap Study? 3 pts

• Name the theorists/theories reflected in the Island Story. What is your rationale for your answer? 2 pts

• According to Dr Burke, how is PTSD different from Toxic Stress? 2 pts

• Why is self-esteem understood as an internal judge? 2 pts

• What does the Mirror Test demonstrate? 2 pts

• Name the influences on attachment formation. 1 pts

• Explain conservation. 2 pt

• What causes a rapid rate of synaptogenesis? 1 pts

• What contemporary study reflects the life of Sara Baartman? 1 pts

• Name the primary domain: 12 pts
  1. Buttoning clothing
  2. Tracking
  3. Self-esteem
  4. Counting
  5. Self-Awareness
  6. Writing
  7. Grouping objects
  8. Listening
  9. Language
  10. Temperament
  11. Attachment
  12. Playing with friends
  13. Myelination
  14. Conception
  15. Synaptogenesis
  16. Acuity
  17. Assimilation
  18. Habituation
  19. Coordination and integration of sensory information
  20. Ecocentrism
  21. Self-regulation
  22. Classification

• Why do preschoolers use aggression and why does it change? 2 pts

• List play types that are based on attitudes and dispositions.  2 pts

• When were young children viewed as talented pets? 1 pt

• What factors influence prosocial behavior? 1 pts

• What is the connection between Harlow, Bowlby, Maslow, and Erikson? 4 pts

• Pick two grand theorists from 2 different theoretical frameworks. (2 pts) Describe ALL components of each ones theory (5 pts). How do the theorists differ (3 pts)? How are the theorist the same (3 pts)?

• Which attachment type is the most problematic? Why? 3 pts

• Arrange the following in order of development (From 1^st to last): 5 pts

1. Broad jump
2. Primary Circular Reactions
3. Loss of body fat
4. Roll from stomach
5. Loss of Babinski
6. Moro
7. First symbiosis
8. Sucking
9. Associative Play
10. Initiating social interchanges