Category Archives: Exams
biology
- Schmitz (1998) studied indirect interactions in a 3-level system: predator (Spider), herbivore (Grasshopper), and resources (Grass and Herbs). This plot shows Herb abundance with resources alone (1-level), resources and herbivores (2- level), and all three levels (Risk = spiders can’t eat, Predation =spiders eat). Based on these data, what how would you describe the impact of Spiders on Herbs here?
- TMII and DMII
- TMII but almost no DMII
- DMII but almost no TMII
- Neither TMII nor DMII
- Sale (1979) did a removal experiment with territorial damselfish on coral reefs. They removed individuals of three species and asked whether the individual taking over that spot would be the same species. The circular arrows represent the same species returning, while the others represent a different species colonizing a spot. What result would be most consistent with the predictions from niche theory?
- P. wardi replaces itself and takes over others’ sites.
- Each species replaces itself the great majority of the time.
- Each species takes over for others often, colonists are a more-or-less random “lottery”.
- MacNeil et al. (2005) sampled stable isotopes in sharks to ask whether they preyed on bluefish (a large-bodied, high-trophic level species) that migrate through the area in spring. Liver tissue “turns over” quickly, so its isotopic signature reflects the animal’s recent diet more strongly than other tissues like cartilage. Which interpretation of these data is most consistent with your understanding of stable isotopes?
A..Makos feed on higher trophic level food all year long.
- Makos prey on the spring bluefish much more than blue sharks and threshers do.
- All three species eat lots of bluefish in the spring.
- Which of the following would be an example of a trophic cascade?
- Praying mantises consume ladybugs, thereby increasing the population density of aphids.
- Mantises consume invasive Japanese ladybugs, thereby increasing the diversity of native ladybugs.
- Beetles drill into trees, creating habitat under the bark for leafhoppers, aphids, and other invertebrates.
- You collect two samples of invertebrates from two pots. Which diversity measure would indicate that Pot A had greater diversity than Pot B? Note that both A and B have the same proportions of the same taxa.
Taxon | Pot A | Pot B |
Worm | 20 | 200 |
Fly | 10 | 100 |
Roach | 5 | 50 |
Toad | 2 | 20 |
Ant | 1 | 10 |
Mpede | 1 | 10 |
Cpede | 1 | 10 |
- Shannon Index (H’) B. Probability of an Interspecific Encounter (PIE) C. Chao’s Estimator
- Based on this plot from Chase (2003), what would be the β richness for a really isolated pond (neighbor distance = 1000m)? Remember that β richness = γ/α.
- β = 1 B. β = 2.5 C. β = 12 D. β = 27
- Which of these ponds would Chase (2003) say you’d be most likely to find a
- Separation distance <100m
- Separation distance 300- 600m
- Separation distance >800m
- The Theory of Island Biogeography holds that large islands should have more species. Which of these plausible explanations is the one MacArthur and Wilson proposed?
- Large islands can host larger populations, which reduces extinction rates.
- Large islands are big targets, so they have greater immigration rates.
- Large islands have greater species turnover, so more species are there.
- Based on the IUCN Red List, the primary force that has caused animals to become threatened or endangered is
- Habitat loss
- Invasive species
- Climate change
- Most of the time, a non-native species introduced to a new place never becomes common in the new place. How would Elton explain that failure?
- Native species lack predators to limit their populations.
- Native species have better adaptations for that local environment.
- Native species represent more of the species pool, so they’ll “get lucky” in establishing themselves.
- Atlantic cod were top predators in North Atlantic Ocean food webs. Their populations crashed in the 1990’s, and the abundance of phytoplankton (their food’s food’s food) has increased dramatically. This is an example of
- Keystone predation
- Ecosystem engineering
- Trophic cascade
- Herring worms are long-lived parasites of orcas. Based on what you know about trophic relations and stable isotopes, what would you expect to be true of herring worm tissue?
- Ratio of δ15N to be about equal to that of orcas.
- Higher ratio of δ15N than orcas.
- Higher ratio of δ15N than free-living worms, but lower than orcas.
- The simple web below goes from a top predator (A) to intermediate consumers (B, C, D) to phytoplankton (E and F). Suppose a disease wiped out species B. Which result would support top-down control of populations in this food web?
- Populations of Species A decrease.
- Populations of Species C increase.
- Populations of Species D increase.
- Suppose you had data for 20 open and 50 covered samples of soil invertebrates. Based on the richness observed in the samples collected, which type of habitat do you think has greater diversity?
- open samples
- covered samples
- they have equal diversity
- One important difference between the conservation of terrestrial and marine animals is that
- Marine animals tend to disperse over far greater distances.
- Marine animals have less complex life cycles.
- Marine animals experience greater temperature stress due to climate change.
Short answers (6 pts unless noted otherwise)
- The Oostvaardersplassen is a natural park in the Netherlands that aims to “re-wild” the wetland and grassland ecosystem near Amsterdam. What is the biggest difference between the current ecosystem and the one the planners were trying to re-create (eg, the area about 5,000 years ago)? What would you recommend they do to make the current system more closely match the “natural” state?
- A new species of crayfish, which has European origin, appears in a pond in a nature preserve in VA. The plot shows the rank-abundance for taxa before the crayfish colonized the pond (open red) and after (filled blue).
(a) How has the crayfish altered the richness and evenness of the pond community?
- Rangers in the Everglades cruise roads at night to survey mammal abundance. Their observations found that most mammals were far more common before pythons were present (green filled) than after they had established (red open).
Give two effects pythons could have on mammals to account for the decline in observations. Note that direct predation by pythons will not fit for either effect.
Effect type | Description |
Trait-mediated direct | |
Density-mediated indirect |
- Potluck. Write a good question from this part of the course that could be worth six points. Note that good questions involve some analysis, not just ask respondents to report facts. “What is β diversity?” wouldn’t qualify, but “How would Chase (2003) expect β diversity to change in a network of ponds if you started fertilizing them regularly?” would.
(b) Write a full-credit answer to your question.
- How to manage wild horses in western North America is a controversial topic. Given what we’ve read about them, make an argument for why we should or shouldn’t consider them to be a harmful invasive species here.
- (3 pts) What advice would you give to students who were about to take this course? What should they do to be successful in this class? With your permission, I’ll attach some of your advice to next fall’s syllabus.
Equations galore!
|
)
|
Good luck!
Is this and multiple stable equilibria the same thing?
Engineering: solar energy
This online test contains 4 questions, each worth 25 marks.
Attempt ALL FOUR questions.
Q1. | a. Calculate the sun altitude and azimuth angles at 2 pm on 20th May in Aberdeen (latitude 57.1o north). Then determine the solar collector angles (both tilt and azimuth), assuming the collector uses an ideal tracking system.
b. Calculate the daytime hours for that day. Hint: the altitude angle is zero at sunrise and sunset times, and the daytime is the difference between these two times. c. Calculate the global solar radiation (sum of direct and diffuse solar radiations) at that time (2 pm). d. Now assume that the solar irradiance variation is approximately given by I = 900 × cos(15o×(local solar hour-12)) W/m2 for a specific day, and the sunrise and sunset times are respectively 6 am and 6 pm. Calculate the absorbed solar irradiation (energy) for that day and the peak sun hour.
|
[8 marks]
[6 marks]
[6 marks]
[5 marks] |
|
Total Question 1 | [25 marks] |
Q2. | a. In an ideal buck DC/DC converter, the input and output voltages are respectively 30 V and 15 V, the output current is 5 A, the switching frequency is 50 kHz, the output inductance is 0.5 mH and the capacitance is 100 μF. Determine the converter operating mode and calculate the peak-to-peak output voltage ripple.
b. Assume that a PV module with below IV characteristics is directly connected to a load with resistance R=3 ohm. Determine the approximated power delivered to the load, and its voltage and current. |
[6 marks]
[6 marks]
|
[6 marks]
- Now assume a buck DC/DC converter (operating in CCM) is placed between the PV module and load R, and P&O MPPT algorithm is implemented to keep the output of the PV module at MPP.
Calculate the power delivered to the load, and its voltage and current.
- The PV module is composed of 72 cells in series, and each cell has a [3 marks] parallel resistance of 4.8 ohm. Ignore the series resistance. If only [4 marks] the top PV cell is shaded and the module current is the same Impp, calculate the module MPP voltage and power for two cases;
- Without bypass diode,
- With a bypass diode across each cell with on-state voltage of
0.6 V.
Total Question 2 [25 marks]
Q3. It is required to design a stand-alone PV system for a house in a remote location, where the annual insolation is 1800 kWh/m2 and the energy
consumption is given in Table Q3a. The PV module data (under STC) and
battery specifications are given in Table Q3b. The PV system nominal DC voltage is 48 V. Assume the irradiance at earth in a sunny day is 1000
W/m2.
Table Q3a.
DC devices | Power (W) | Hours | AC devices | Power (W) | Hours | |
Air conditioner | 1500 | 8 | Fridge/ Freezer | 100 | 24 | |
Lamps | 200 | 5 | TV | 100 | 3 | |
Washing machine | 1000 | 1 | ||||
Microwave | 800 | 0.5 |
Table Q3b.
PV module | Battery module | |||
Voc | 59 V | Nominal voltage | 48 V | |
Isc | 5.8 A | Capacity | 8.8
kWh |
|
Vmp | 51.5 V | Type | Li-ion | |
Imp | 4.86 A | efficiency | 92% | |
Temperature coefficient of Pmax | -0.4 %/oK | depth of discharge (DoD) | 88% | |
Temperature coefficient of Voc | -0.3 %/oK | |||
Temperature
coefficient of Isc |
0.04
%/oK |
- Calculate the total electricity required (in Wh per day) and the [8 marks] module arrangement for the solar array. Assume the inverter efficiency is 97% and other losses (excluding inverter loss) is 9%.
- According to the battery specification given in Table Q3b, [5 marks] calculate the required battery capacity and number of battery modules if reserve time of 3 days is required.
- Assume that the PV system overall cost is £10,000, the retail energy [6 marks] price is £0.18/kWh and it is constant over time (no inflation). Also, assume the Feed in Tariff (FiT) payment is £0.03/kwh with an extra export tariff of £0.04/kwh for 50% of the generation. Calculate the pay-back time for this PV system with and without FiT.
- The PV cells temperature may increase to much higher degrees in [6 marks] summer. Calculate the power generated by the PV array assuming the PV cell average temperature is 45 o Comment if the number of PV modules should be changed at this temperature.
Total Question 3 [25 marks]
Q4. You need to design a 250 kW PV plant with the inverter and PV modules data given below (under STC) for a location with average sunny hours of
4.5 hours/day. Assume the irradiance at earth in a sunny day is 1000
W/m2, and the system other losses (excluding inverter) is 8%.
Table Q4.a – Inverter data Table Q4.b – PV module data
|
|
- Calculate the area of each module (in m2).
- Determine the PV array arrangement and calculate the annual
energy generation. [2 marks] c. The PV modules degrade 2% for year 1 and then 0.6% for years [8 marks] 2-25. Calculate the Annual generated energy for year 20. Assume the degradation happens at the end of each year. [4 marks]
- i) Repeat the design and determine the PV array arrangement if the inverter maximum DC current is 500 A.
- ii) Determine the lowest limit of the “maximum DC current” of [6 marks] the inverter that makes it inappropriate for this design. Consider a safety margin of 10% for maximum DC voltage/current. [5 marks]
Total Question 4 [25 marks]
Total
ECE 2020 Fall 2022 HW10
1.) Find the Laplace transform of the following functions:
a. ��(�) = sin (4�)�(�)
b. ��(�) = ��−2�
�(�)
c. ��(�) = (� − 1)�(� − 1)
d. ��(�) = (� − 1)�(�)
2.) Use partial fraction expansion and the Laplace Transform tables to find the inverse Laplace
Transform for the following functions.
a. ��(�) = �
�2+4�+3
b. ��(�) = 2�
�2+2�+2
3.) In the circuit below, vi(t) = 5e-4000t
cos(3000t) V R = 12Ω, C = 83.3µF, L = 1mH.
a.) Find s.
b.) Find the s-domain equation for voltage across the resistor, VO(s)
c.) Find vO(t)
4.) For the circuit below, the input Vi is a 10V pulse that lasts for 1 second. R = 106Ω, C = 44.4nF, L =
1mH. Assume all initial conditions are zero.
a.) Find the equation for Vi(t)
b.) Find Vi(s)
c.) Find the s-domain equation for voltage across
the capacitor, Vo(s).Find the voltage across the
capacitor, vo(t)
[ a ) h { sin ω t ) :twr
c ( 3 ( Ʃ tu ( t ) } =
h ⼀
TS
4 cttt sC-
) eFcssTuce .
Ts =
a{ ) FS =
s ~+ 16 < { ( t –
7 ) u ( t –
1 | =②
”
‘
⾼ i
< b ) L [ e
–
ats = sta |= c
[ } ) =
e .
s
红
LEtf ( t 1 } = –
ds E [ 3 ) cd ) L ( 5 dit ) } = L ( tuct ) ) – LLu (t ) ]
( ct a
2t
u < t ) } = 皆
< ( sti 1
FB ( S ) =
stzn Fd ( s >=
‘
b …
‘
}
LS [
+
C 品 tan | = simat
CG> FaEb ) = {
+1 ” ) < st 3 )
[ b ) Fn [ { ) =
( s + 1 ^+ 1
= –
z { st) + 点 ( st33
L ”
( ! +
. 1 = cost ”
c +
nau)
= ertost ‘
” c
itowr ) =
coswtuC ☆ ninlkeetcswe
L ”
( Fa ( s > ] =
L
”
(
– {
ast.) + 童 ( st 3 )
} ( ” ( ☆ea ) ^
tw. ) = e
-at sinwt
L +
[ 焱n
+ t
( }
= [
^ { ☆
t” ^+ 1 –
☆
t 1 ” ) z
+ 、 )
⼆⼀定 e ⽐七 {
e 张
( ) usy = Ze –
t
etsintTu cost –
2 ( t )
5 424 S + 22816 .
6
《 ) Wo < s ) =
–
0 .
G 243 –
135 1 .
a ☆
5 + 1000 .
4 st 12* ( 0 b
+ g 2 t8000 s tz5 × 10 c
–
924 c st 500 .
2 ) –
889 .
76 5 GL 4 { ) + 40003 –
87 G .
4
Ca ) { = juw 1 × 10 H =
( st 5 00 .
27 ^
+ s 427 . 79
⼗ 2 [ ☆+ 400033 + 30002
S = j 3000
Vo < t ] = – O
. 代
24 e –
500 .rt
cos ( 3423 . 7 Gt )
– { 859 .
7427.
79
} e
-500 .
rt
cb>÷L
1 s
=
号 sin ( 、 4 r 7 79 h ) + 5 .924 e
–
40 oot
co s C 3000 t ) – (
8700 ) e
-40 ot
亡s ths
83 .
3 × 10
~
6 F sincsoω t 3 )
< s =
e
–
40 t ( 5 . 4 r osot3 1 }-o .2 a sin ( soo .t )
=
LCs ^+ 1
( × 10
~
3 a
–
s 0 at ( o .
9 os ( s 4 L 7 . 9 I t 3 + 0 . 26 sin ( 342799e) }
=
1 × ( 0
” × 83 . 1003 × > 5 ← 1
=
12004 .
8
z +
12 ×<oc
v 。
cs ) = ( 贤 + 2 ) U . ( ∞ )
5 cst 4000 )
5 { { ^+ ( 2 x (
6
] { st 4000 )
72 [ S ^← 2 × 10 + 1000 . 4 S 7 ( } ~+ 8000 s ,
+ 35 x 0 )
= 12 t02004
. 8 tnx 1 o
6
s
‘ ct 80 osti5 xi0
1× 10
–
}
家
[ C ) 2 us ) =÷<
Ca 3 Ui ( t ) = ( 0 { ut ) –
ω< t –
( ) ) Z ( ( 3 ) =
家及1 C ( ) th =
5 uRcLtSht 级
HS 级 E
106 44 .
4 ×<0
61-
Ui ( s ) ,
☆ ( RSE ]
( b 3 vics ) = L τ ( ( o ( U (t ) –
U ( t –
+ ) } U 0 [ s } =
CR / c ] + L
R lo –
coe
‘
s
= 10 C ÷ –
号了 =
分 「 RCLtsLtR S
( 0 E 1 –
e ”
) CO 6 lo –
loe
–
s
= [ –
O < st 106 之 38 . 5
) (
st [ 06218 . 5
) 2 + 16 –
( × 10 ‘ 0
t
( 062 ⻢ 8 .
5
Cs + 100 L38 .
5 ) ^≈ 1 .
1 α 100 + 号 ) C ( –
e
”
」 =
47 xl 0
–
astio ”
s s… =
S
–
1 . 0 % 285× ( 05 dt 11
Uo { t | = [ Lo –
LO ←^ 1 .0621 ∞ 5 × 105 t
Cos C ( × Lo 5 tj } ust ) tl 0 ←
1 . 062 ] 85
BLDG 3100 Construction Operations
FINAL EXAM
Note: Each Question is worth 5 points = 210 points
True or False
T F Prime Contractors and General Contractors perform the same work.
T F OSHA only regulates safety rules for construction.
T F Heavy Construction involves building construction.
T F Larger earthmoving equipment has stabilized the heavy construction market.
T F Soil classifications are based on the building code.
T F Most materials swell when they are excavated.
T F The shrinkage of a material is most important when excavation is being performed.
T F A cubic yard of bank material weighs less than a cubic yard of loose soil.
T F A backhoe is an excellent general use piece of equipment.
T F A dozer excavates by cutting into the soil and pushing to a stockpile
T F Daily excavate removal is based on the capacity of the trucks.
T F Production during excavation is independent of the bucket size.
T F Trenchless technology is losing popularity due to excessive excavation.
T F Tower cranes are not used more often based on lack of versatility.
T F Owning Equipment is easier than leasing equipment.
T F The higher the concrete strength the more economical it is?
T F Concrete is sprayed with water during the curing process to keep it clean.
T F A low-cost type of compaction method is to allow vehicles to drive on the roadway for an extended period before paving.
Answer the 24 questions below:
- What are three (4) things a construction manager manages?
- List three (3) pieces of equipment used in the rock crushing process.
- What is the difference between aggregate and an admixture?
- What is the formula for concrete?
- What group sets the standards for concrete mixes?
- Why is bituminous concrete a preferred material in the Northeast?
- Why do we use an efficiency factor in production equations? Why is job efficiency important?
- Explain soil “Swell”, why do some site contractors cover the excavate piles?
- How many processes does aggregate typically go through before it is suitable for use?
- What is the relationship between an excavator’s daily capacity and truck hauling?
- What is OSHA and what does it do?
- What is the minimum depth for an excavation support system?
- What is the depth of frost in Massachusetts?
- What happens when the material used in cement concrete are mixed together?
- What are 3 factors that can drive up the cost of concrete?
- When is it more likely that a track boom crane would be used rather than a tower crane?
- What factors must be considered when determining how much weight a crane can lift safely?
- For one working day what costs would you include under the crane crew?
- What factors contribute to loss of efficiency in a hose?
- What makes an excavator so desirable, what ability does it have that changed the earthwork industry?
- What is excavation and why is it needed in building construction? What divisions outline the scope of Earthwork?
- Explain why when you dig a hole and then back fill and it the hole with the soil you had removed there is never enough soil to fill the hole up.
- Explain two different ways that construction managers evaluate production.
- Why is swing depth factor so important when calculating excavator production?
Financial Engineering and Machine Learning FINA1147
You will use one market index and four companies’ daily data and these companies should be
from two different sectors. (The required data can be downloaded from Yahoo finance:
http://uk.finance.yahoo.com/). The sample period should be latest and at least 2 years in
length (for example, from Jan 2018 to Jan 2020). For the volatility forecast, the required FX
data can be downloaded from the course Moodle page. For the empirical analysis, you can
use statistical software such as EViews, STATA, or SPSS etc., which has to specify in the
report.
A. Mean-Variance Optimization
1. Briefly explain mean-variance portfolio optimization.
(5 Marks)
2. Estimate the covariance matrix for the selected four companies’ stocks.
(5 Marks)
3. Plot by creating portfolios using the selected four companies and the obtained covariance
matrix. Discussing the results of the portfolio.
(10 Marks)
B. Panel Data Analysis
1. Construct a panel data set using the latest 100 days of the four stock prices. Transfer the
stock prices, market index, and risk-free rate into log returns.
(8 Marks)
2. Verify the CAPM theory using OLS, FE, and RE estimators. Select the appropriate model
(OLS, FE or RE). Discuss the obtained regression results.
(12 Marks)
C. Time-Series Data Analysis
1. Choose one of your stock price series, compute ACF and PACF for the log returns. Discuss
the results.
(4 Marks)
2. Forecast the log returns with AR(5) model and verify the forecasting accuracy by
considering the last 6 months of the data as out-of-sample.
(8 Marks)
3. Estimate the ARMA(3,2) model and comment on the estimations.
(8 Marks)
D. Volatility Analysis
1. Choose one of your stock price series, verify the ARCH effect and estimate GARCH(1, 1)
model.
(10 Marks)
2. Using the data provided, carry out a GARCH(1, 1) volatility forecasting by considering the
last 6 months of the data as out-of-sample. Discuss the results.
(10 Marks)
E. Discussions on Machine Learning Application
1. Discuss the following concepts.
a) Machine Learning
b) Supervised Learning
c) Differentiate between test set and training set
(12 Marks)
2. Explain your understanding on neural network in machine learning. Provide one possible
application of neural network in the financial practice and explain the processes.
(8 Marks)
You need to do all the above tasks and submit your results with detailed discussion on the
tests in a report form (using academic style and minimum 2000 words).
Please ensure you are following academic writing requirements, otherwise a
deduction of marks ranging from 10 to 30 will be applied. You will use one market index and four companies’ daily data and these companies should be
from two different sectors. (The required data can be downloaded from Yahoo finance:
http://uk.finance.yahoo.com/). The sample period should be latest and at least 2 years in
length (for example, from Jan 2018 to Jan 2020). For the volatility forecast, the required FX
data can be downloaded from the course Moodle page. For the empirical analysis, you can
use statistical software such as EViews, STATA, or SPSS etc., which has to specify in the
report.
A. Mean-Variance Optimization
1. Briefly explain mean-variance portfolio optimization.
(5 Marks)
2. Estimate the covariance matrix for the selected four companies’ stocks.
(5 Marks)
3. Plot by creating portfolios using the selected four companies and the obtained covariance
matrix. Discussing the results of the portfolio.
(10 Marks)
B. Panel Data Analysis
1. Construct a panel data set using the latest 100 days of the four stock prices. Transfer the
stock prices, market index, and risk-free rate into log returns.
(8 Marks)
2. Verify the CAPM theory using OLS, FE, and RE estimators. Select the appropriate model
(OLS, FE or RE). Discuss the obtained regression results.
(12 Marks)
C. Time-Series Data Analysis
1. Choose one of your stock price series, compute ACF and PACF for the log returns. Discuss
the results.
(4 Marks)
2. Forecast the log returns with AR(5) model and verify the forecasting accuracy by
considering the last 6 months of the data as out-of-sample.
(8 Marks)
3. Estimate the ARMA(3,2) model and comment on the estimations.
(8 Marks)
D. Volatility Analysis
1. Choose one of your stock price series, verify the ARCH effect and estimate GARCH(1, 1)
model.
(10 Marks)
2. Using the data provided, carry out a GARCH(1, 1) volatility forecasting by considering the
last 6 months of the data as out-of-sample. Discuss the results.
(10 Marks)
E. Discussions on Machine Learning Application
1. Discuss the following concepts.
a) Machine Learning
b) Supervised Learning
c) Differentiate between test set and training set
(12 Marks)
2. Explain your understanding on neural network in machine learning. Provide one possible
application of neural network in the financial practice and explain the processes.
(8 Marks)
You need to do all the above tasks and submit your results with detailed discussion on the
tests in a report form (using academic style and minimum 2000 words).
Please ensure you are following academic writing requirements, otherwise a
deduction of marks ranging from 10 to 30 will be applied.
CSIS 1190 – Excel in Business
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 1st 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 1st $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 2nd worksheet (Forecast), also plan a 5-year forecast of the Group’s corporate taxable income based on the following assumptions:
Forecast of Increase/Decrease Percentages: | |||
Restaurant | -2.25% | ||
Motor | 11.5% | ||
Realty | 20.0% | ||
Entertainment | -17.0% | ||
Computers | -9.0% | ||
Directors’ Fees | 9.5% |
Organise your spreadsheet (if possible) into grouping & outlines.
|
2022 | |||||
Group Income | ||||||
Net Profit from Happy Restaurant | $595,000 | |||||
Net Profit from Happy Motors | $485,800 | |||||
Net Profit from Happy Realty | X value | |||||
Net Profit from Happy Entertainment | $678,050 | |||||
Net Profit from Happy Computers |
$695,250 | |||||
Other Operating Expenses | ||||||
Annual Directors’ Fees | $790,900 | |||||
Misc. Dividends to Shareholders | Y value |
Total Corporate Taxable Income = Group Income – Other Operating Expenses
On the 3rd 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:
…
Purchase Price | $850,500.00 | House Loan | $637,875.00 | |
Monthly Payment | $4,638.77 | Down Payment | $212,625.00 | |
Total Payment | $1,047,603.63 | |||
Prime Rate | 3.00% | |||
3Plus rate | 0.75% | Period (years) | 15 | |
Month | Beginning Principal | Interest Paid | Principal Paid | Ending Balance |
Apr-22 | $637,875.00 | $1,993.36 | $2,645.41 | $635,229.59 |
May-22 | $635,228.59 | $1,985.09 | $2,653.68 | $632,575.91 |
…
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 1st 6 months as follows:
Address of House |
Date Sold | Asking Price | Final Transacted Price | Realtor |
2567 Mica Place | Jan-22 | $2,229,000 | $2,528,000 | Eric |
1288 Pinetree Way | Jan-22 | $1,923,000 | $2,200,000 | Eric |
204 #81 Elm Street | Feb-22 | $1,028,000 | $850,500 | Eric |
#101 800 Schoolhouse Ave | Mar-22 | $715,000 | $599,900 | Sam |
#501 1290 Greenway Pl | Feb-22 | $995,900 | $788,000 | Sam |
#409 999 Como Lake | Apr-22 | $822,000 | $750,000 | Carol |
#111 122 Gilmore Ave | Apr-22 | $499,800 | $399,800 | Sam |
2388 Sugarpine Ave | Jun-22 | $3,922,000 | $3,850,000 | Carol |
1634 Diamond Cres. | Jun-22 | $3,029,000 | $2,650,000 | Eric |
123 Holdom Ave | May-22 | $1,999,990 | $1,880,000 | Sam |
582 #14 West 13th St | Feb-22 | $650,000 | $499,900 | Carol |
Editing and Proofreading Task (Mechanics and Punctuation
Using your textbook (especially Appendix, Part C), identify the error in each sentence. Correct the error using Track Changes in Word.
- Many students fail to understand the importance of proofreading their work and it shows in their project grades.
- The report will be distributed to Operations, Research and Development and Accounting.
- Just as we finished eating the bell rang.
- Connie my Technical Communication instructor is looking forward to her winter break.
- One of the many possibilities, is editing the draft to earn a higher grade.
- I still need one thing to ace this assignment; a charming personality.
- My instructor asked me if I knew what I was doing for last week’s assignment?
- It is clear to me that this sweater is hers’.
- She replied “Yes, that’s the correct answer for the last question.”
- This textbook is a newly-acquired offering at the bookstore.
- My accounting instructor assigned 3 textbooks.
- 25 people showed up to the meeting on Thursday.
- The university I attend is located in mankato, Minnesota.
- I have always preferred pepsi over coke.
- The screw measured .025 inches.
6.5 The Mean Value Theorem for Integrals
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.