Reference no: EM132401299

Assignment -

You are employed as an administrator for The Music Stop. The Music Stop has two music shops, one based in Aberdeen, one in Glasgow. The Glasgow shop opened in 2009 and the Aberdeen shop opened in 2012. The shops sell musical instruments, and accessories. There are plans to extend the website to include music themed giftware and to sell sheet music. The Music Stop also has an instrument repair workshop based in Glasgow. Plans are in place to introduce a music tuition scheme, either on a one-to-one basis or to small groups. Both shops participate in the assisted instrument purchase scheme.

You have been asked by the Directors to use your expertise in problem solving using a spreadsheet to offer advice to them.

You have been provided with the following data.

- A workbook called The Music Stop containing information in worksheets about the Glasgow and Aberdeen income figures from 2014 to 2017, Income from Repairs and figures for North West Music.

- Database comprising information about Instrumental Teachers.

- Word file showing Staff Commission figures.

You have three tasks to complete and within each task there is a variety of questions to be completed. The tasks are to be carried out under open-book conditions but you must guarantee that the work is your own by completing the attached sheet.

Each task will give you instruction as to what evidence you must provide.

Your assessor may ask to observe you to ensure authenticity.

Each piece of evidence produced should have your name, task and question number and date as a footer.

Task 1 -

Open the workbook The Music Stop. Insert a blank worksheet and name this sheet Task 1 - Q1&Q2. Complete Questions 1 and 2 within this Sheet.

1. The company is purchasing a range of musical instruments for hire by customers using the Assisted Purchase Scheme. The Finance Manager is interested in the depreciation of these musical instruments. Use the following information (initial cost, salvage value and expected life) and calculate the depreciation for year one for each category of musical instrument. Depreciation figures for Year 1 should be shown clearly within the Task 1 - Q1&Q2 sheet. You have been asked to use straight line depreciation method.

2. The website is to be updated and frit, company wants to expand its range of products by selling exclusive musical giftware on the website. Additional salary costs will be incurred in recruiting a specialist buyer. You have been asked to investigate how the company can finance this development. Calculate the monthly repayment when borrowing £60,000 for 10 years with an interest rate of 5.5%/year, and the monthly repayment when borrowing the same amount of money over a 5 year period with an interest rate 8%/year. Calculations should be shown in the Task 1 - Q1&Q2 sheet that you created in Question 1.

3. The Music Stop has been setting up a database of Instrumental Tutors named Instrumental Teachers. The information in this database is to be converted to a spreadsheet. This will be used to calculate the weekly hours worked/payments to be made. Create a new worksheet named Task 1 - Q3&Q4 within The Music Stop workbook. Import from the Instrumental Teachers database file the ID numbers, Instrumental Tutors' Names, Addresses and NI Numbers. Add three further columns, one to show the number of hours worked, one to show the payment by hour, one to show total amount paid. No instrumental tutor is allowed to work more than 30 hours in any one week. In the hours worked column set a data validation restriction to prevent any more than 30 hours being entered, and set an appropriate Message when entry of more than 30 hours is attempted. Prepare the worksheet so that once the hours are keyed in the total payment to each Instrument Tutor is clearly shown. Insert an appropriate title onto this sheet.

4. The Finance Manager has given you a Word document named Staff Commission that contains employee names and the commission rates each employee is paid. This information should be imported into the Task 1 - Q3&Q4 worksheet within The Music Stop workbook so that it can be searched to find out the commission to be paid to each employee.

As periodic updates to commission rates are made, you should ensure the documents are linked to enable the worksheet to be updated to show the up-to-date commission rate for each employee.

Change the % commission to be paid to Charlie Williamson from 3% to 10% in the Staff Commission Word document and then replace your existing Staff Commission plain text file. Refresh the imported commission data in the Task 1 - Q3&Q4 worksheet to show the change to 10% for Charlie Williamson.

You should submit an electronic copy of the workbook.

Task 2 -

You have been given a workbook called The Music Stop. Within this workbook are two worksheets showing the income from the sales of musical instruments and accessories at the Glasgow (Glasgow Income Figures) and Aberdeen shops (Aberdeen Income Figures). Consolidate the information held in these sheets. Name this consolidated sheet Task 2 - Q1. Once you have completed this task create a copy of this worksheet and name it Task 2 - Q2.

1. Using the worksheet Task 2 - Q1, Sort on Category A to Z then Sub Category A to Z. Filter and subtotal for Musical Instruments then Drums and Percussion and Woodwind. Create two times series graphs (historigrams) to illustrate the consolidated total income figures for Drums and Percussion, and then for Woodwind Musical Instruments, for each quarter from 2014-2017. Add an appropriate trend line to each graph.

2. Using the worksheet Task 2 - Q2 insert an Annual Total column for each of the 4 years to the right of each Quarter 4 column. Calculate the Annual Total figures for each year and then hide all of the quarterly columns. Using the function Consolidate and the Annual Total figures for the 4 years, calculate the overall total sales across the 4 years, and also the, minimum and maximum annual sales figures. Create a Pivot table in a new worksheet to show by category and subcategory the four annual totals and the sum of the four annual totals. Name the worksheet Task 2 - Q2 Pivot.

3. Rename the worksheet named Income from Repairs as Task 2 - Q3. With the worksheet there are errors in it. Error check this worksheet and trace the errors, capturing a screen dump which should be inserted within the Task 2 - Q3 worksheet. Also within the worksheet briefly comment upon how the errors have occurred. Make the necessary corrections to this worksheet.

You should submit an electronic copy of the workbook.

Task 3 -

The Music Stop is considering diversifying its business by buying an independent music shop called North West Music which is based in Manchester. In addition to the sales of musical instruments the shop also sells a wide range of sheet music. Income figures and assorted costs have been provided and you are asked to analyse these figures so that The Music Stop can gather information for the business case to purchase, or not. Rename the North West Music worksheet as Task 3 and using the details of Income and Costs from 2014-2017:

1. Create a scatter diagram to show Advertising Expenditure (X) against Total Income (Y) from 2014-2017. Create a second scatter diagram to show Advertising Expenditure against Repairs Income from 2014-2017. Add an appropriate trend-line and the regression formula to each scatter diagram.

2. Use an appropriate function to forecast an estimate of Total Income if Advertising Expenditure was £12,000.

3. Calculate the correlation coefficient between Advertising Expenditure and Total Income. Calculate the correlation coefficient between Advertising Expenditure and Repairs Income. Comment on what this suggests about the relationship between Advertising Expenditure and Total Income, and between Advertising Expenditure and Repairs Income.

4. Create a Time Series graph (historigram) to illustrate Total Income over the period 2014-2017. Add an appropriate trend-line for time series data, and comment on the trend identified.

5. From the information you have gathered comment on the pattern of Income between 2014-17 and make recommendations on the advisability of purchasing North West Music.

You should submit an electronic copy of the workbook.

Attachment:- Assignment Files.rar