Introduction to Computers (Literacy & IT), MGMT1000 – Practical Assignments. Academic Year 2019/2020, Semester 2
1 | P a g e
PRACTICAL ASSIGNMENTS 1 & 2 – MICROSOFT EXCEL AND
MICROSOFT WORD
This assignment looks into the dealings of Focused Revenue Investment (FRI), a striving
company in the financial industry located in Ave 11, Bill Bongal, Trinidad. The company has
recently implemented some very important “greening” initiatives and wants you a newly promoted
Deputy Operations Manager to process some company data using Microsoft Office and prepare
important information for the Board of Directors and other staff to assist them in their decision
making process. Follow the components below to attain a greater understanding of the full
requirements. Your telephone number and email address is (868) 424-1001 and dom@fri.day.tt.
The assignment consists of two (2) gradable components using Microsoft Excel and Microsoft
word applications. Each component carries with it several questions for which you are required
to provide all solutions to fully maximize on mark potential. Please note that in some instances
you are required to integrate features between applications. The deadline for the assignment
is shown below. Download all related files.
COMPONENT DELIVERABLES
The following (completed) files are to be submitted via the course website web tool (the Learning
Exchange):
Deliverables File Type Deadline
studentid_open_s2_20xl MS Excel March 6, 2020 (6:00 PM Eastern
Caribbean Time)
studentid_open_s2_20wd1;
studentid_open_s2_20wd2
MS Word March 20, 2020 (6:00 PM Eastern
Caribbean Time)
`
Please note that “studentid” in the file names is to be replaced by your id number. For example:
411012456_open_s2_20xl
Introduction to Computers (Literacy & IT), MGMT1000 – Practical Assignments. Academic Year 2019/2020, Semester 2
2 | P a g e
Practical Assignment using Microsoft (MS) office – Word and Excel
The Word and Excel components are evaluated out of 100 marks each, which are then
apportioned out of 15 percentage points respectively.
Student will use knowledge acquired from the practical online tutorials and links provided to
assist in the completion of MS Word and Excel assignments.
This deliverable comprises two (2) components which are to be submitted at different intervals
as specified in the deliverables section below.
An MS Excel source file will be provided for download – <assgt_mgmt1000_open_s2_20.xls
. This is necessary for the completion of the entire assignment (both Word and Excel).
Students may make assumptions and as such, place or include data in source files accordingly.
For example the contact data file (Employee Department Contacts worksheet) may not include
official titles for recipients. For example Mr., Ms., Mrs. or Dr. Students may include these in
the source data (Excel), assigned arbitrarily (without prejudice). To do so insert a new column
and label accordingly.
Students are urged to note that the pictorial chart shown as an example in “Figure 1: Sample
Output” may vary from actual results once this section is attempted. This figure is given
primarily to assist students in formatting the required chart.
Students are required to upload all copies of their electronic word processing and excel
spreadsheet files to drop-box link provided on the related section of the course home page.
Introduction to Computers (Literacy & IT), MGMT1000 – Practical Assignments. Academic Year 2019/2020, Semester 2
3 | P a g e
SPREADSHEETS – MICROSOFT EXCEL
Available from Week 4 for Unit 3 – Application Software, from February 9, 2020
Due: March 6, 2020, 6:00 PM ECT
TOTAL: 100 Marks (to be calculated as a percentage of 15)
For this part of the assignment browse through all worksheets before attempting the
question. Using the Microsoft excel workbook assgt_mgmt1000_open_s2_20.xls complete the
questions below to provide solutions for the directors and board members of FRI, regarding their
financial reporting requirements: [100 Marks]
a. Using the “Green Savings” worksheet, provide the formulas to calculate the savings
for each computer type usage, and corresponding totals. [4]
b. Using the auto format feature format range A3:D12 in “Green Savings” worksheet.
Note that values are to be formatted with the thousands separator, ZERO decimal
places with no “$” signs on the final values. [2]
c. Using the “Quarterly Revenue – 2018” worksheet provide the relevant formulas to
complete the worksheet. Apply appropriate formatting. [5]
d. In the same worksheet in part (c) dynamically apply the following formats for Qtr1
and Qtr4 for all related revenue types:
i. Values equals or over $120,000 should be blue and bolded
[2]
ii. Values under or equals to $90,000 should be red and italicize
[2]
e. Create a clustered column chart with 3-D visual effect with all relevant labels, titles
and legend while changing the default colours as shown below in Figure 1. The chart
is to be inserted in new worksheet called Quarterly Revenue Chart – 2018
worksheet. Hint: change the font size on the text areas on the chart to assist in
resizing the chart.
[15]
Introduction to Computers (Literacy & IT), MGMT1000 – Practical Assignments. Academic Year 2019/2020, Semester 2
4 | P a g e
Figure 1: Pictorial view of chart requirements (Note: actual number representation may
differ from actual results)
f. Using the “Statement of Income” worksheet, center the financial statement titles
across the data below and bold the company name. [2]
g. Insert a column between columns D and E. Resize this new column to width 6.00 and
apply a green fill with a border around range E column to the point where it meets the
“Net Income” row. [4]
h. Insert a row above row 5 and resize to 7.50. [2]
i. Using the correct method/formula provide the correct values for each revenue item
for 2018. [5]
j. Complete the formulas in cells provided for revenues* for 2019. 2019 revenues have
increased by ¾ % over 2018 values. [10]
k. Using the correct function or formula calculate the total revenue and gains in the cells
provided. Insert the comment, “Discuss with CEO and finance Team” in the cell
containing the words “Total revenues and gains”. [4]
l. Complete the formulas in cells provided for expenses for 2019. 2019 expenses** have
decreased by ½% over 2018 values with the exception of computer expenses***
which should be used from the “Green Savings” worksheet. [15]
m. Using the correct function or formula calculate the total expenses and losses in the
cells provided. [4]
Sales
Interest
Investment Income
Other
Introduction to Computers (Literacy & IT), MGMT1000 – Practical Assignments. Academic Year 2019/2020, Semester 2
5 | P a g e
n. Insert a pie chart with a 2-D visual effect for 2018 using all expense items in a new
worksheet called, “Comparing Expenses”. Include all relevant labels, titles (not
shown) and legends while changing the default colours as shown below in Figure 2.
[5]
Figure: Pictorial view of chart requirements (Note: actual number representation may
differ from actual results)
o. Using all relevant cells calculate the income tax expenses for each year. [10]
p. Using the correct function or formula calculate the net income. [2]
q. Format the comparative income statement use dollars signs where necessary. All
values are to have the thousand separator format with no decimal places. [4]
r. Using the “Aggregates” worksheet, complete all formulas (hint: must use “Statement
of Income” worksheet). [3]
s. Save your MS Excel workbook as studentid_open_s2_20xl for submission. For
example, 411012456_open_s2_20xl.
56%
18%
19%
5% 2%
**Cost of goods sold
**Selling and admin administrative
***Computer (operating)
**Depreciation
**Interest expense
Introduction to Computers (Literacy & IT), MGMT1000 – Practical Assignments. Academic Year 2019/2020, Semester 2
6 | P a g e
WORD PROCESSING /MAIL MERGE – MICROSOFT WORD
Available from Week 4 for Unit 3 – Application Software, from February 9, 2020
Due: March 20, 2020, 6:00 PM ECT
TOTAL: 100 Marks (to be calculated as a percentage of 15)
Referencing your completed MS Excel assignment prepare a brief report to be sent to all
employees who are directly involved in marketing only. In the introductory part of the letter briefly
inform these stakeholders of benefits of using a spreadsheet solution and how easy it was to process
data to derive the information presented in the report. Briefly describe what is meant by “Green
Computing” and how it may impact on company sales via ecommerce (give two (2) examples).
Give a brief analysis of the data found in the charts created and include these graphics in the report.
The report should be no more than 200 words. Note that all employee internal contact information
is located in the “Employee Department Contacts” MS Excel worksheet. Prepare the
aforementioned by following the instructions below.[100 Marks]
a. Prepare a professionally constructed letter/report using your name, job title, department,
telephone number, and email address and a resized graphic of computer as part of the letter
head as relevant salutations. Insert a copy of the “green recycle” graphic as a water mark
in the center of the document behind all text. In addition, ensure requirements at point
above are included. [30]
b. All for formatting stays at the default except for the following: [10]
i. Left and right margins – 0.50”
ii. Page numbers – bottom right e.g. “3 of 5” where “3” is the page number and “5” is
the total number of pages (not to be shown on the first page)
iii. The description “Financial Analysis for 2018 & 2019” in the left header (not to be
shown on the first page)
iv. Your student id in the right header (not to be shown on the first page)
v. “First line indent” on each paragraph
c. Include a copy of the statement of income and the information in range A3:D12 in “Green
Savings” worksheet as figures 1 and 2. In one sentence briefly describe each figure copied
in the letter. [20]
d. Complete the report as your main document all using the most suitable feature in MS
Word. Include all necessary fields based on the requirements mentioned above. Save the
main document as studentid_open_s2_20wd1. For example,
411012456_open_s2_20wd1. [20]
e. Generate the required letters and saved the file (generated) as studentid_open_s2_20wd2.
For example, 411012456_open_s2_20wd2. [20]
f. Submit both documents to the drop box for grading.
Introduction to Computers (Literacy & IT), MGMT1000 – Practical Assignments. Academic Year 2019/2020, Semester 2
7 | P a g e
UWI GPA and Marking Scheme
Assignments are marked using the following Mark Scheme. The lowest pass mark/grade is 50%
(C).
GRADE % RANGE GRADE
POINT
GRADE
DEFINITION
GRADE DESCRIPTOR
A+ 90 -100 4.3 Exceptional Demonstrates exceptional performance and achievement in all aspects of the course.
Exceptional application of theoretical and technical knowledge that demonstrates
achievement of the learning outcomes. Goes beyond the material in the course and displays
exceptional aptitude in solving complex issues identified. Achieves the highest level of
critical, compelling, coherent and concise argument or solutions within the course.
A 80 – 89 4.0 Outstanding Demonstrates outstanding integration of a full range of appropriate principles, theories,
evidence and techniques. Displays innovative and/or insightful responses. Goes beyond the
material with outstanding conceptualization which is original, innovative and/or insightful.
Applies outstanding critical thinking skills
A- 75 – 79 3.7 Excellent Demonstrates excellent breadth of knowledge, skills and competencies and presents these
in appropriate forms using a wide range of resources. Demonstrates excellent evidence of
original thought, strong analytical and critical abilities; excellent organizational, rhetorical
and presentational skills.
B+ 70 – 74 3.3 Very Good Demonstrates evidence of very good critical and analytical thinking in most aspects of the
course. Very good knowledge that is comprehensive, accurate and relevant. Very good
insight into the material and very good use of a range of appropriate resources.
Consistently applies very good theoretical and technical knowledge to achieve the desired
learning outcomes.
B 65 – 69 3.0 Good Demonstrates good knowledge, rhetorical and organizational skills. Good insight into the
material and a good use of a range of appropriate resources. Good integration of a range
of principles, techniques, theories and evidence.
B- 60 – 64 2.7 Satisfactory Displays satisfactory evidence of the application of theoretical and technical knowledge to
achieve the desired learning outcomes. Demonstrates sound organisational and rhetorical
skills.
C+ 55 – 59 2.3 Fair Demonstrates fair breadth and depth of knowledge of main components of the subject.
Fair evidence of being able to assemble some of the appropriate principles, theories,
evidence and techniques and to apply some critical thinking.
C 50 – 54 2.0 Acceptable Demonstrates acceptable application of theoretical and technical knowledge to achieve
the minimum learning outcomes required in the course. Displays acceptable evidence of
critical thinking and the ability to link theory to application.
F1 40 – 49 1.70 Unsatisfactory Demonstrates unsatisfactory application of theoretical and technical knowledge and
understanding of the subject. Displays unsatisfactory ability to put theory into practice;
weak theoretical and reflective insight. Unsatisfactory critical thinking, organizational and
rhetorical skills.
F2 30 – 39 1.30 Weak Weak overall performance with very limited knowledge and understanding of the subject.
Little evidence of theoretical and reflective insights. Weak organizational and rhetorical
skills.
F3 0 -29 0 Poor Overall poor or minimal evidence of knowledge and understanding of the subject. Displays
little ability to put theory into practice; lacks theoretical and reflective insights. Incomplete
breadth and depth of knowledge on substantive elements of the subject. Little or no
evidence of critical engagement with the material. Responses are affected by irrelevant
sources of information, poor organizational and rhetorical skills.
