Spreadsheet Modeling - Assignment Example

Reports for the Tasks Task 2 Margaret Jaszczyk Smith and Jones Spreadsheet Modeling From the computations, the dimensions of all the materials used and their computed weights are as follows: The silver sheet measures 1*1*0.7 cm in length and would weigh up to 1.5g The silver wire is 1*0.08 in length and weighs 0.04g The chain has a length of 1 cm and weighs 0.07g For each month, the computed silver costs of each commission from the two suppliers are: Commissioned Orders Rooksons Metal Direct   Cost(£) Cost(£) Domed circular pendant with chain £10.19 £10.46 Heart shaped earrings £4.21 £4.32 Rectangular drop pendant £5.88 £6.03 Diamond shape pendant and earrings set £13.90 £14.27 Triple stack ring Size M £0.43 £0.44  Total £34.61 £35.52 From the results of the computations, Jimks should consider ordering its silver from Rooksons each month. It is because the monthly total silver cost of commission for Rooksons is £34.61 compared to that of Metal Direct, which is £35.52. Based on the current prices of orders, Rooksons is still a better option (Slack & Chambers 2010). Metal Direct charges £667.08 per kg including VAT@20% at the time of writing, which is higher than the £0.67 charged by Rooksons per gram including VAT@20%. Going by the mode of pricing, Rooksons offers a better deal compared to Metal Direct. Metal direct places prices on the basis of the daily stock price for silver while Rooksons considers a fixed price for silver that is reviewed quarterly. By offering to work on the basis of a fixed price for silver, which is reviewed on a quarterly basis, Rooksons presents a better deal. Besides, Rooksons has stated its ability to meet any silver requirements that Jimks may have. It is regardless of the monthly fluctuations in the silver prices. Rooksons is therefore, a better option. Task 3 To: A. L. L. Students From: Smith and Jones The priorities of the two groups for spending are different. They seem to have different ideas on what is most important in the following areas; • Increasing the staff at the stations • Introducing more CCTV at stations and on trains • Extending services to run later in the evening • And providing more frequent daytime services for commuters According to the survey the company carried out of 200 adult travelers while on the system at peak times, the major factors that may influence how safe customers feel when travelling on the service include: • The respondent ID number, recorded as 1 to 200 inclusive • The approximate travel time spent on the system (in minutes) • The number of trips the respondent has made on the system in the last month (including this one) • The Rating for reliability of the system (1=not at all reliable, 10 = very reliable) • The rating for safety on the system (1=not at all safe, 10 = very safe) Of the three factors; travel time, number of people travelling together (trips) and reliability, only the number of people travelling together (trips) has an association with ratings of safety on the system. R=0.961. It means that there is an extremely strong positive direct linear association between the number of people travelling together (trips) and the ratings of safety on the system. According to Rohr (2013), one simple linear regression model that may be used to predict a customer’s rating of safety, using only the strongest relationship identified is: y = bx + a, where; Y - Safety, Slope (b) = 0.108, x – the number of people travelling together, and Intercept (a) = 1.349 R = 0.961, representing an extremely strong positive direct linear relationship. The estimated safety/ the rating likely to be given by a customer, who has travelled for 23 minutes, made 36 trips in the last month and gave a score of 6 for reliability is computed to be 5.252. The forecasts on safety ratings from the model are likely to be more reliable if the numbers of trips the respondents make using the system increases every month.   Task 7 From: A. L. L. Students, External Business Consultants To: Mens Outlet Clothing, Inc. The sales figures for the past thirty days reveal that Men’s Outlet Clothing is experiencing contractions in the business. There is no consistency in the increase of the company’s sales in the five weeks. The following simple linear regression model provides an appropriate sales forecast for the company (Rohr 2013). Regression Equation y = a + b*x, sales = 802.7664 - 1.11242*time Intercept (a) = 802.7664 Slope (b) = -1.112422 From the model, the sales figures are dependent on time. There is a significant negative relationship between the sales figures of the company and time. The sales figures of Men’s Outlet clothing reduce as days increase. The recent reduction in sales in the last thirty days can be attributed to time. A closer examination of the daily sales shows that the sales will be lower on Tuesday and Wednesday. The pattern is anticipated to remain the same in the coming weeks with the sales still being lower on Tuesday and Wednesday. From the examination of the recent sales, there is no proof that there is any correlation between the low sales and the number of full-time sales staff. Men’s Outlet does not have to adjust the company’s staffing resources by any amount. You may maintain your eight full-time sales staff, working every day with the two extra part-time staff working on Fridays and Saturdays. However, due to the pronounced reduction of sales on Tuesday and Wednesday, the full-time sales staff should be reduced on these days. The forecasts of the company’s daily sales (1000’s) for one week into the future will be; Day Linear trend using regression equation Multiplicative Forecast Sunday 34 764.9439959 605.4219107 Monday 36 762.7191511 357.7941075 Tuesday 37 761.6067288 532.4796503 Wednesday 35 763.8315735 667.5156528 Thursday 31 768.2812629 776.9502715 Friday 32 767.1688406 1122.332867 Saturday 33 766.0564182 1296.276565 References Rohr, R. (2013). An Introduction to Econometric Forecasting and Forecasting Models, Klein, Lawrence R., and Young, Richard M., Lexington, Mass: D. C. Heath and Company, 1980. Journal of Forecasting, 322-322. Slack, N., & Chambers, S. (2010). Operations management (6th ed.). Harlow, England: Financial Times Prentice Hall. Appendices For Task 2 Prices charged during writing Supplier Price excluding VAT Price including VAT Unit Metal Direct £555.90 £667.08 Kg Rooksons   £0.67 g VAT Rate 20% £0.65 g Dimensions and weights of Each Materials Used Dimensions Length Weight Unit (cm) (g) Sheet 1*1*0.7 1.5 Wire 1*0.08 0.04 Chain 1 0.07 The computed monthly silver costs of each individual commission from the two suppliers No Commissioned Orders Rooksons Metal Direct     Cost(£) Cost(£) 1 Domed circular pendant with chain £10.19 £10.46 2 Heart shaped earrings £4.21 £4.32 3 Rectangular drop pendant £5.88 £6.03 4 Diamond shape pendant and earrings set £13.90 £14.27 5 Triple stack ring Size M £0.43 £0.44     £34.61 £35.52 For Task 3 R = - 0.5347, hence there is no relationship. R = 0.961, hence there is an extremely strong positive direct linear relationship. R = - 0.042, hence there is no relationship. Task 7 Seasonal component of shopping data(multiplicative) Week Mon Tue Wed Thu Fri Sat Sun Total Correction Factor 1 1.234235778 1.84467714 0.862439 2 0.732335 0.511467 0.68637168 0.90185582 1.401428814 1.88806888 0.791696 3 1.048208 0.28 0.62305112 1.12980964 1.616755037 1.44676997 0.682629 4 0.841346 0.615938 0.78817734 1.00238949 1.599790649 1.58950889 0.829285 5 Average 0.873963 0.469135 0.69920005 1.01135165 1.46305257 1.69225622 0.791512 7.000471 1.000067 Adjusted average 0.873904 0.469103 0.69915303 1.01128364 1.462954186 1.69214242 0.791459 7 The forecasts of the company’s daily sales (1000’s) for one week into the future will be; Thur 31 768.2812629 776.9502715 Fri 32 767.1688406 1122.332867 Sat 33 766.0564182 1296.276565 Sun 34 764.9439959 605.4219107 Thur 35 763.8315735 667.5156528 Mon 36 762.7191511 357.7941075 Tue 37 761.6067288 532.4796503 Read More