Project #93406 - Bicycle Shop Excel Assignment

IS 2200 Ray’s Bicycle Shop ASSIGNMENT INSTRUCTION 1. Download and open the Ray’s Bicycle Shop template from the D2L drop box and rename it according to the naming instruction. You should only submit this Microsoft excel file when you complete the assignment. Please do NOT submit the instruction file along the completed excel file. 2. Switch to the IncomePrediction worksheet, then enter the following text on two lines within cell A1: Ray’s Bicycle Shop Predicted Income Statement 3. In cell A2, enter For the Years Ended December 31, 2014 through December 31, 2016. 4. In the range C6:E7, enter the following net sales and cost of sales figures: 2014 2015 2016 Net Sales 13,520 10,981 9,034 Cost of Sales 4,140 3,960 3,011 5. In the range C11:E14, enter the following expense figures: 2014 2015 2016 Salaries and Wages 1,632 1,481 1,392 Sales and Marketing 2,631 2,112 1,840 Administrative 521 410 375 Research and Development 501 404 281 6. In the nonadjacent range C18:E18; C20:E20; C24:E24, enter the following values for Other Income: 2014 2015 2016 Other Income 341 302 239 7. In the range C8:E8, enter a formula to calculate the gross margin for each year, where the gross margin is equal to the net sales minus the cost of sales. 8. In the range C15:E15, calculate the total operating expenses for each year, where the total operating expenses is the sum of the four expense categories. 9. In the range C17:E17, enter a formula to calculate the operating income for each year, where operating income is equal to the gross margin minus the total operating expenses. 10. In the range C19:E19, enter a formula to calculate the pretax income for each year, where pretax income is equal to the operating income plus other income. 11. In the range C20:E20, enter a formula to calculate the income taxes based on 15% tax rate. 12. In the range C22:E22, enter a formula to calculate the company’s net income for each year, where net income is equal to the pretax income minus income taxes. 13. Create a 2-d column chart including the Net Sales, Total Operating Expenses, and Net Income for years in prediction. Put this chart in its own sheet. To do this you can use the Move Chart feature on excel and select New Sheet. 14. Create a new sheet and copy your completed IncomePrediction sheet in it. Rename the sheet as InflatedIP. 15. Using the InfalatedIP sheet. Assume that the Salaries and Wages expenses turn out to be 20% higher than predicted, and net sales would also turn out to be 3% higher than expected. Do not forget to consider the relationship between Net Sales and Cost of Sales when you are updating the numbers for Net Sales. Also, the government decides to increase the Tax rate to 22%. Update this sheet with the new information (you should recalculate everything that is being affected by these changes in Net Sales, Salaries and Wages, and Tax Rate) 16. Change the font color for the changed numbers to red. 17. Provide a recommendation (based on the inflated income prediction) as to whether Ray has to keep his shop open for the next three years or he should consolidate and look for a new job! Use a text box within the InflatedIP page to express your recommendation. In word, please provide the following: (a powerpoint presentation was supposed to be done last week-**I really only need the info and new table so I can complete another assignment on time) a. A complete structure for your presentation: 1. Introduction to the presentation (cover slide, outline slide). 2. Discussion of the case (important numbers, observations, etc.)** 3. Predicted sales by year (1 slide including graphic from Excel). ** 4. Inflated income prediction (1 slide including graphic from Excel) You need to create a new graph for this (it was not included in your excel assignment) ** 5. Your opinion of the figures (recommendations/suggestions)* 6. Summary of the presentation.

Subject Computer
Due By (Pacific Time) 11/15/2015 10:00 pm
Report DMCA
TutorRating
pallavi

Chat Now!

out of 1971 reviews
More..
amosmm

Chat Now!

out of 766 reviews
More..
PhyzKyd

Chat Now!

out of 1164 reviews
More..
rajdeep77

Chat Now!

out of 721 reviews
More..
sctys

Chat Now!

out of 1600 reviews
More..
sharadgreen

Chat Now!

out of 770 reviews
More..
topnotcher

Chat Now!

out of 766 reviews
More..
XXXIAO

Chat Now!

out of 680 reviews
More..
All Rights Reserved. Copyright by AceMyHW.com - Copyright Policy