this is an assignment for foundations of business computing. I will submit two files an excel sheet and a pdf file which is the specifications. You will have to do the calculations in the excel sheet and also do another word document, which is the report based on the calculations. Calculations should be finished on the excel sheet and also finish a word document which reports the calculation. Everything is clearly mentioned on the pdf file which is the specifications.
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 suburb of Norman Gardens. A business opportunity has become available to open a travel agency in the local shopping center. Joey Tribbiani is a local businessman and he is planning to open a travel agency to serve local community. He has contacted you to carry 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 business analyst, you will conduct a cost-benefit analysis by comparing expected costs with expected benefits 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 Tribbiani 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 breaks and section breaks), 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 carry 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 been 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 corresponding 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 incurred 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 remain 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 remain 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