Great Deal! Get Instant $10 FREE in Account on First Order + 10% Cashback on Every Order Order Now

Page 1 of 8 COIS11011— Foundations of Business Computing Assessment details for ALL students Assessment item 1— Developing Computing and Business Skills —Term 2 2018 Due date: Electronic submission of...

1 answer below »
Page 1 of 8
COIS11011— Foundations of Business Computing
Assessment details for ALL students
Assessment item 1— Developing Computing and Business Skills —Term 2 2018
Due date:
Electronic submission of TWO files (1 Spreadsheet file
and 1 Word file) via Moodle by 1:00 pm AEST, Thursday,
Week 5, 9 August 2018
ASSESSMENT
Weighting: 20% of total unit assessment
1 Length: No set length

Objective
The objective of this assignment is to test and enhance student’s ability to use Microsoft’s Office
suites applications such as Word processor and Spreadsheet. By completing the activities, students
should possess sound understanding of applying acquired practical knowledge to identify problems,
analyse data and organise information in the form of a business report using these applications.
Business Name: Escape Travel & Tours
Background
For this assignment, students can assume the role of a business analyst to advise small business
entrepreneurs to start up new business in a fast-growing Rockhampton subu
of Norman Gardens.
A business opportunity has become available to open a travel agency in the local shopping center.
Joey Tri
iani is a local businessman and he is planning to open a travel agency to serve local
community. He has contacted you to ca
y out a cost-benefit analysis and a payback analysis to
determine the financial viability of the proposed travel agency plan in Norman Gardens area. As a
usiness analyst, you will conduct a cost-benefit analysis by comparing expected costs with expected
enefits over a period of 7 years and advise Mr. Joey whether it is worthwhile continuing with the
proposed plan in question.
In this assignment, you will:
1. Conduct a cost-benefit analysis using Microsoft Excel
2. Write up a report using Microsoft Word to demonstrate how you evaluated costs and benefits
and determined payback period and advise Joey Tri
iani how to develop a business plan to
start a new business.
NOTE: This assignment requires you to produce 2 files; an Excel spreadsheet and a Word document.
Page 2 of 8
Key Spreadsheet skills to learn
Familiarise yourself with the following features of MS Excel:
Cell formatting, absolute referencing, SUM function, worksheet formatting and layout, I F
Function, PMT function, inserting a line chart and any other feature as may be required to
complete the assignment.
Key Word processing skills to learn
Familiarise yourself with the following features of MS Word:
Using styles for normal text, headings and sub-headings, auto-generated Table of Contents (ToC),
Breaks (page
eaks and section
eaks), Using Headers & Footers (ensure your header and footer
should not appear on title page and ToC page), spelling & grammar checking tool, report formatting
and layout, and any other feature as may be required to complete the assignment.
YouTube video tutorials are provided in the ‘Microsoft Office Learning’ block of the Moodle unit
webpage to help you learn and implement that spreadsheet and word-processing skills in your
assignment.
PART A: Cost Benefit Analysis
You will ca
y out two cost-benefit analyses:
• A Payback analysis
• A Return on Investment (ROI) analysis
The Payback analysis will determine how long it will take for the Escape Travel & Tours to pay for
initial investment. For this, you will need to estimate the initial set up costs, the annual operation costs
and the annual benefits. The initial set up costs, monthly operation costs and monthly benefits have
een supplied below. The Return on Investment (ROI) analysis calculates profit as a percentage of
the total costs over the analysis period.
Formula for calculating ROI = ((cumulative benefits – cumulative costs) / cumulative costs) *100
Formula for calculating payback in dollars = (cumulative benefits – cumulative costs)
Formula for calculating payback period in years = the last year which had a negative payback
(this will be a year number, NOT a dollar amount. For example, if year 5 is the last year which had a
negative payback then in the formula it will be 5) + (the first positive payback which will be a dollar
amount, in any given year / the first cumulative cost in dollar amount that is lesser than its
co
esponding cumulative benefit, in any given year)
To open a small travel agency business, owner of Escape Travel & Tours is required to make initial
investment as well as loan from the bank. Following information will be used in your spreadsheet:
Page 3 of 8
COSTS: The anticipated costs for the Escape Travel & Tours are grouped into 2 categories:
1. Set up costs
2. Operating costs
SET UP COSTS:
Hardware and software costs
As a business analyst, you have advised Mr. Joey to purchase the following items:
• 4 desktop computers @ $1,400 each
• 1 server @ $2,500
• 1 backup system computer @ $2,200
• 1 printer @ $1,200
• 1 photocopy machine @ $3,500
• Microsoft license (for operating system and Office suite) @ $1,500
One-off costs (these costs are incu
ed only once)
• Consultant fee @ $20,000
• Business name registration fee @ $2,500
• Office fit out/furniture costs @ $30,000
• Staff training @ $5,000
It is anticipated that the physical hardware equipment will be replaced every 4 years. After 4
years, the price of these physical hardware equipment items is expected to decrease by 30%. Software
license fees remain the same price over the analysis period but they must be renewed every 2 years.
OPERATING COSTS
Fixed costs (costs that are relatively constant and not dependent on level of use, assume these figures
except salary will not change over the analysis period)
• Office rent @ $1600/month
• Business insurance @ $100/month
• Internet Service Provider (ISP) charges @ $80/month
• Staff salary @ $6000/month
• Salaries are expected to increase by $500 every year
Joey’s bank has already approved a business loan of $50,000 for his business. The bank will charge a
fixed annual interest rate of 7.25% and the loan will be paid off in 7 years in monthly instalments.
Calculate the monthly payment and include as a fixed cost in your worksheet.
Page 4 of 8
Variable costs (these vary depending on level of use but assume the following average values will
emain same except electricity rates over the analysis period)
• Electricity @ $200/month
• Stationery @ $200/month
• Domain name and website costs @ $90/month
• Phone charges @ $100/month
• Miscellaneous expenses @ $500/month
• Electricity rates are expected to increase by 2% every year
BENEFITS
Monetary benefits of the travel agency operations will come from selling tickets, holiday travel
packages and making hotel bookings. Escape Travel & Tours will receive 10% profit on every one-
dollar transaction from their business partners.
Assume Escape Travel & Tours is selling $70,000 worth of tickets per month, $10,000 worth of
holiday travel packages per month and $15,000 worth of hotel bookings per month. These sales
emain at the same level for the whole 7 years. Calculate their monthly and yearly income over the
next seven years in your worksheet. In addition to their 10% sales profit, airlines, hotels and tour
management groups are also offering every year an extra 6% bonus if yearly income of Escape Travel
& Tours is above $50,000 and 3% bonus if yearly income of Escape Travel & Tours is under $50,000.
The bonus paid as cumulative, i.e. income plus 6% or 3% for the first year and then 6% or 3% on top
of that for the following year, this goes on for the whole 7 years.
Hint: Use Excel’s IF-Function formula to do the bonus calculation.
Your Tasks:
The Spreadsheet
Both Payback analysis and Return on Investment (ROI) will be done on the same spreadsheet as they
will use the same data set. Your spreadsheet should contain 4 worksheets appropriately named such
as Set up costs, Operating costs, Benefits and Cost Benefit Analysis. The idea is to store sets of related
data in one place (sheet) so that they can be manipulated independently. As well, you need to store
hardware and software details, monthly rates for operating costs and benefit details (the data
supplied to you above) in the appropriate sheets. Storing these details will enable you to:
• Use formulae to generate other data needed in the sheets.
• Make changes to details only in one place whenever there is a change.
For instance, by storing the monthly Internet Service charge in one cell, if there is an increase or
decrease in the rate then you need to only change the rate in that cell and the change will cascade to
Page 5 of 8
all other cells whose values depend on this rate (because all sheets are linked by formulae). In this
spreadsheet exercise, you should make use of the following Excel features:
• Use a spreadsheet containing more than one sheet
• Use appropriate formulae to generate needed data
• How to reference and copy a value from a cell in one sheet to a cell in another sheet
• Use absolute reference wherever needed
• Use PMT function. To calculate a loan payment in spreadsheet, you can use the PMT
function. The PMT function calculates the payment for a loan that has constant payments
and a constant interest rate. Enter an annual interest rate, the number of payments, and the
loan amount on the worksheet. Then, refer to those cells in the PMT formula. Please see the
‘Microsoft Office Learning (Excel)’ block in the Moodle unit webpage to learn how to
implement PMT function in Excel.
• Use IF function
• Format your data
• Draw a line graph from a given data set
Show data in a chart
The 4 sheets in your spread sheet should be linked via formulae. The data for the Cost-Benefit Analysis
sheet is derived from the other three sheets. After setting up all sheets, use the data from the Cost-
Benefit Analysis sheet to draw two-line graph series: one for cumulative costs and the other for
cumulative benefits. This line chart should be displayed under cost-benefit analysis worksheet with
appropriate chart title and axes titles.
Table 1 shows an example of what the set-up costs sheet should look like.
Set up costs
Items Time period over 7 years
Answered Same Day Aug 07, 2020 COIS11011 Central Queensland University

Solution

Meenakshi answered on Aug 08 2020
132 Votes
Escape Travel & Tours
In this project we prepare the business proposal for Escape Travel and Tours company .In this project my work is business analyst and prepare a plan for travel and tour agency. The Joey Tri
iani is a planning to open a travel agency to serve local as business analyst and we conduct a Cost-Benefit Analysis for the preparing a business proposal. We analysis the project and check, it is feasible for the business or not? A benefit cost analysis is a technique we are analyze decisions, projects and determine a value for intangibles . In this technique we estimate the value of benefit and cost of project establishes they are profitable or not? We knew the risk with the help of excel sheet and...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here