ISYS2059 BUSINESS INFORMATION SYSTEMSRMIT Classification: Trusted ASSESSMENT TWO TERM 1, 2021 ANALYSING A SPREADSHEET AND PRESENTING A BUSINESS REPORT Due Date/Time: Friday 12 March, 11:59 pm 30% of...

1 answer below »
please quote


ISYS2059 BUSINESS INFORMATION SYSTEMSRMIT Classification: Trusted ASSESSMENT TWO TERM 1, 2021 ANALYSING A SPREADSHEET AND PRESENTING A BUSINESS REPORT Due Date/Time: Friday 12 March, 11:59 pm 30% of Final Grade Case Study “Taps Galore" is a specialist online store, situated in Singapore and was recently established by owners Alex and Ash. It sells a limited range of tapware at discounted prices. Alex and Ash currently stock a range of taps from eight manufacturers. They advertise on social media and supplies four different ranges - value, standard, supreme and superior. They have calculated different discounts for each manufacturer based on the tap range. They believe that customers buying ‘value’ items will be more focused on ‘discounts’ than customers who purchase more expensive tapware. Alex and Ash buy the tapware at cost price and sell at recommended retail price less the advertised discount. Alex and Ash charge a flat rate of $9.99 for delivery but have recently noticed that several of their competitors have begun to offer deals which include free delivery. Alex and Ash conducted extensive market research before setting up the business. Since their first sale in January 2018 they have recorded the details of all sales in an excel spreadsheet. They have asked you to use the data provided in the spreadsheet to complete relevant calculations and to use Excel ‘tools’ to analyse the data provided. They then require you to write a business report which analyses and discusses both the past profitability of the business and the various options available to help increase future profitability. Alex and Ash are interested in both the popularity and profitability of different manufacturers and ranges. They hope you can provide some insight regarding, the type of tapware they should focus their efforts on. They also want to identify their best customers so that they can market to them appropriately. In addition, they want to have some ideas of potential growth in the coming months and the likely effect on the business if they offered free delivery for all purchases over $100.00. A spreadsheet has been provided containing the data for “Taps Galore” sales for 2018 and 2019. Requirements and Specifications You are expected to undertake descriptive, predictive and prescriptive analysis of the data that Alex and Ash have maintained over the past two years. The results of the analysis are then required to be presented in a professionally organized report with respect to the requirements of the owners above and some specific expectations as follows: · Provide an overview of gross profit trends, undertaking both descriptive and predictive analysis. · Discuss the results of your descriptive analysis and the options for future sales growth based on the requirements of Alex and Ash. · Identify and discuss the potential effects to the business if ‘Taps Galore’ offered free delivery for all purchases over $100.00. Do you believe this is a good option? Why? · Make three recommendations with regards to future changes which might assist in further improving the profitability of the business. Briefly justify these recommendations (i.e. prescribe what should be done and why this would be helpful, drawing on your predictive and prescriptive analysis). Preparing the Business Report Report structure The analysis, findings and recommendations which you prepare for Alex and Ash should be outlined in a professional business report. This report should include · A title page – with a relevant graphic, report title, student name/number, course name (Business Information Systems), course number (ISYS2059), your workshop time and your workshop tutors’ names. · A Table of Contents (TOC) automatically generated by Microsoft Word. Default settings for the table of contents should not be altered. · A brief introduction which outlines clearly the purpose of the report. · A discussion regarding the results of your analysis. Relevant descriptive, predictive and prescriptive analysis has been included. · Relevant charts and tables which are labelled correctly and assist the reader in understanding the results of your analysis. · A short concluding summary of the major findings of the report. (supported by relevant data: eg, revenue, costings etc) · Clear recommendations that the business owner can adopt. These recommendations must be drawn from the analysis you have carried out and must have been discussed in the body of your report. Report format The report should be developed using the guidelines covered in Canvas and workshops. Specifically, you should: · Ensure your report is concise - No more than 1200 words. This word count includes title page and TOC. It does not include appendices · Present the report in a professional, business format with no spelling or grammatical errors. · Use appropriate headings and subheadings throughout the body of the report. · Use double or 1.5 spacing. · Fully justify all text in the report. · Include a page header with a suitably formatted title (the header must not appear on the title page). · Include a page footer with your name, student number and page number (the footer must not appear on the title page). · Include relevant charts and tables you have created in the body of your report (appropriately labelled). The charts and tables (screenshots of your spreadsheet are not appropriate) must be discussed as part of the paper. · In an appendix include screen shots of calculations (as discussed below) which you have used to assist in your analysis. (Note: You do not need to refer to this appendix in the body of your report.) A guide to inserting screen shots commences on page 4 of this document. · Do not include references or citations in this report Report Submission The Business Report must be submitted, in MS Word format, by 11:59 pm, Friday, March 12, 2021, via the link provided in the “Assignments” area on the ISYS2059 Business Information Systems site on Canvas. A Student Guide to submission (using Turnitin) is available at: https://www.rmit.edu.au/students/support-and-facilities/it-services-for-students/canvas/turnitin A Guide to inserting a screenshot in ‘Word’ · Open and/or minimise the program/sheet you wish to create the screen shot from (in this case your Excel document). As your screenshots are designed to show calculations, you should make sure you have highlighted the relevant cell and the function is clearly visible in the function dialogue (fx) box. · Open your ‘Word’ document. · Select Insert/Screenshot/Screen Clipping. · Select the sheet you wish to create the screenshot from. The screen should fade and a + appear. Use the + as a cropping tool to select the required screenshot. Note: There is no need to show every cell. (See Figure 1 in which one of the relevant cells is highlighted (B3) and the function for column B is clearly visible.) · Label the screenshot appropriately. Figure 1 - Screen shot of Countif function If you need to create a screenshot of a column that is not located near the fx box (see Figure 2) you can: · Select the relevant cell. Double click on the cell to show the formula. · Create screen clipping as per the instructions above. Provide an appropriate label. Figure 2 – Screenshot of Sumif function To show several formulas in the one screenshot (see Figure 3) you can: · Select the relevant cells. · Select Formulas/Formula Auditing/Show formulas. · Take the screenshot as per the directions above. (NOTE: Text needs to be large enough to be read clearly.) Figure 3 – Screenshot showing functions in several columns ISYS 2059 Business Report Term 1, 2021Page 1
Answered 1 days AfterMar 05, 2021ISYS2059

Answer To: ISYS2059 BUSINESS INFORMATION SYSTEMSRMIT Classification: Trusted ASSESSMENT TWO TERM 1, 2021...

Komalavalli answered on Mar 07 2021
135 Votes
1
Contents
Introduction    3
Outline of Tap Galore    3
Analysis on products    4
Graph of Chrome finish product    4
Graph of Black finish product    4
Graph of Matt Black finish product    5
Graph of Gold finish product    5
Predictive analysis    6
Demand for the product finish:    7
Analyzing the impact of offering free delivery on Gross profit:    7
Conclusion:    8
Recommendation:    8
Introduction
This report focused on analyzing the gross profit for Tap Galore .It focused on finding the most demanded product finish; find which kind of product finish such as chrome, matt black, black and gold finish product that contributes to high gross profit. The predictive analysis use regression method to find whether offering discount r
ate contributes to gross profit or not and also to find whether offering free delivery contributes to gross profit or not.
Outline of Tap Galore
From the datasets we can say that the Alex and Ash stocks a range of Taps from six manufacturers such as Franke, Clark, Fantini,Abey, Phoenix, Mondella. They brought Basin mixer from Clark at $60 and sold at $80.19 received a gross profit of 20.19, brought Basin mixer, bath mixer, water saver shower kitchen tap mixer from Mondella cost ranges from $125 to $155 and sold at a range from 167.91 to 205.71 received a gross profit of 42.91 to 50.71,
They both brought Basin mixer, Basin sink mixer, Basin Tap set from Abey cost ranges from $135to $150 and sold at a range from 180.51 to 198.99 received a gross profit of 45.51 to 48.99. Brought Basin mixer, Basin sink mixer, Basin Tap set from Franke cost price is $160 and sold at $211.59 and received a gross profit of 51.59. Based on the finish of product such as chrome, Black, Matt Black and Gold we can analysis the relationship between Gross profit and Total Revenue.
Analysis on products
Graph of Chrome finish product
The above graph indicates there is a large amount of fluctuation between the total revenue and gross profit(GP). The GP for the product that has chrome finish ranges from ($150.79 – $153.99)
Graph of Black finish product
The above graph indicates there is a no fluctuation between the total revenue and gross profit. Gross profit increases with increase in total revenue of the product. The GP for the product that has black finish ranges from ($19.73 – $137.47)
Graph of Matt Black finish product
    
The above graph indicates there is a little fluctuation between the total revenue and gross profit. The GP for the product that has Matt black finish ranges from ($20.19 – $137.47).
Graph of Gold finish product
The above graph indicates there are a few fluctuations between the total revenue and gross profit. The GP for the product that has Gold finish ranges from ($150.79 – $153.99)
From the entire graph we can say that there is no fluctuation in gross profit for the product which has Black finish and high fluctuation in gross profit for the product which has Chrome finish. Therefore selling Black finish will increase the Taps Galore gross profit constantly.
Predictive analysis
    Regression Statistics
     
    Multiple R
    0.759572
    R Square
    0.576949
    Adjusted R Square
    0.576495
    Standard Error
    46.13595
    Observations
    934
    ANOVA
    
    
    
    
    
     
    df
    SS
    MS
    F
    Significance F
    Regression
    1
    2705454
    2705454
    1271.046
    2.72E-176
    Residual
    932
    1983786
    2128.526
     
     
    Total
    933
    4689240
     
     
     
     
    Coefficients
    Standard Error
    t Stat
    P-value
    Lower 95%
    Upper 95%
    Lower 95.0%
    Upper 95.0%
    Intercept
    -51.97
    3.71
    -14.00
    0.00
    -59.25
    -44.69
    -59.25
    -44.69
    $ discount
    3.21
    0.09
    35.65
    0.00
    3.03
    3.39
    3.03
    3.39
From this linear regression analysis we can say that there exist a positive relationship between discount rate and gross profit. The p value of discount rate is 0.0 which is less than 5%, so this variable is significant at 5% level of significance. One dollar increase in discount rate on an average increases the gross profit of Taps Galore by 3.21 dollar.
I have also conducted the regression analysis between gross profit and delivery charge. It is found that there is no relationship between both the variables.
In order to have future sales growth Alex and Ash has to focus increasing discount rate. Because increasing discount rate leads to increases sales and gross profit of the Taps Galore.
If Taps Galore’ offered free delivery for all purchases over $100.00, it will lead to increase the cost for the products and decrease the total revenue.
Demand for the product finish:
The demand for gold finish product was less. Only two consumer Singapore bathroom Renovation and ACC construction bought gold finish products. For chrome finish product the demand was relatively higher, because it has above 50 customers. The product that has Matt black finish also have low demand, the demand for this product arises from only ten consumers. The product that has black finish also have relatively low demand, the demand for this product arises from above ten consumer.
Analyzing the impact of offering free delivery on Gross profit:
Before offering free delivery to the customer who purchase Black finish product worth above $100 results in gross profit of a range $150.79 to $153.99, who purchase Chrome finish product worth above $100 results in gross profit of a range $42.91 to $137.47, who purchase Matt black finish product worth above $100 results in gross profit of a range $42.91 to $137.47, who purchase Gold finish product worth above $100 results in gross profit of a range $118.79 to $137.47.
After offering free delivery to the customer who purchase Black finish product worth above $100 results in gross profit of a range $140.80 to $144, who purchase Chrome finish product worth above $100 results in gross profit of a range $32.92 to $127.48, who purchase Matt black finish product worth above $100 results in gross profit of a range $32.92 to $127.48, who purchase Gold finish product worth above $100 results in gross profit of a range $108.80 to $127.48.
Overall offering free delivery for those who purchase above $100 will result in decreasing gross profit. Therefore it’s not a good idea to offer free delivery option.
Conclusion:
From this analysis we can conclude that the demand for chrome finish is high and discount rate has positive impact on gross profit.
Recommendation:
In order to increase the sales Alex and Ash should offer more discount for Black and Gold finish products and offer less discount for the products that has Chrome and Matt black finish. Secondly Alex and Ash should expand their marketing strategy by investing in various kinds of advertisement. Thirdly it should expand the business to a place where the demand for Tap Galore product is higher. Offering discount on products will attract more consumers and increase the demand for a product, expanding marketing strategy will give a reach for the product , the people who don’t know about the product will come to know about the existence of such product and buy more of it. Expanding the business will lead to attract more people to consume that product. Overall adopting this strategy leads to attract more consumers.
Matt...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here