Spreadsheet Excel

profiledorritos_p
ModuleOnequestions.docx

The completed Excel worksheet image shown below lists several food items needed for your meals. Data given in the problem is shaded gray. Column B (rows 2 through 7) contains the price per package for each food item and column C (rows 2 through 7) contains the number of packages required. You will be providing proper formulas for the remaining data in the unshaded cells by answering the following questions using cell references from this worksheet. You will type your final answers in this document. A sample Excel file called M1HWsample.xlsx is available HERE [1] if you wish to use it to validate your formulas but your final answers need to be put into this Word document for submission.

Note that once you have completed a question, you may use the results of that question in subsequent problems. i.e., if you calculate the total cost for each food item in cells D2:D7, in subsequent questions you may reference these cells to perform additional calculations such as the total cost of all food items. Remember to start all formulas with an equal (=) sign and to always use cell references where possible. You are to fill in the _________________ with appropriate cell references or range values for each question.

1. (0-5 pts) Write a formula below that would go in cell D2 (and is copied down to cell D7) to calculate the total cost of cereal. (Hint: Price per package multiplied by the number of packages equals total cost.)

=______________ * _______________

2. (0-5 pts) Write a formula in cell D8 to calculate the total cost of all six (6) food items (cereal, milk, eggs, cheese, meat, and pasta). (Hint: For this we merely need to add up all of the values in the total cost column using SUM() function and a range of cells in the D column.)

=SUM(_______________)

3. (0-5 pts) Write a formula in cell E2 (and is copied down to cell E8) to calculate the cost of cereal as a percentage of the total cost of all six (6) food items. Assume the cell has been formatted for % and is showing as being rounded to the highest whole number. (Hint: since the cell is formatted as a percentage it will do the multiplication by 100 and display the % sign so all you need to do is divide the food item cost by the total cost.)

=______________ / ______________

4. (0-5 pts) Write an Excel formula in cell B11 to calculate the cost of a breakfast. Breakfast consists of 1/2 package of cereal and 1/4 package of milk. Assume this cell is rounded to show to the nearest cents (2 decimals). (Hint: No function is needed here – just a formula.)

=0.5* ___________ + 0.25 * _________________

5. (0-5 pts) Write an Excel formula in cell B13 to calculate the cost of a lunch. Lunch consists of 2 packages of pasta and 1/2 package of cheese. Assume this cell is rounded to show to the nearest cents (2 decimals). (Hint: No function is needed here – just a formula.)

=2 * ___________ + 0.5 * ________________

6. (0-5 pts) Write an Excel formula in cell B15 to calculate the cost of a dinner. Dinner consists of 1 and 1/2 packages of meat. Assume this cell is rounded to show to the nearest cents (2 decimals). (Hint: No function is needed here – just a formula.)

=1.5 * ________________

7. (0-5 pts) You have a budget of $25 per day. Write an Excel formula in cell B17 to determine how much money you have left over after you pay for breakfast, lunch and dinner. (Hint: No function is needed here – just a formula.)

=25 - ________________ - ____________ - ______________

8. (0-5 pts) Why does the displayed value for the money left over not show $13.99? What type of formatting is used in the D column for rows 2 through 8? What other column and rows should be formatted similarly and is not?

9. (0-5 pts) In two to three paragraphs of prose (i.e. sentences, not bullet lists) in APA format, summarize the content that was covered this week using the learning outcomes listed at the course for reference. In your summary, you should highlight the major topics, theories, practices, and knowledge that were covered. Your summary should also include personal observations, reflections, and applications. In particular, highlight what surprised, confused, enlightened, or otherwise engaged you. In other words, you should think and write critically not just about what was presented but also what you have learned through the session. Feel free to include questions that you would like answered by your instructor. Do not recap or restate what was covered in the meeting. Tell your instructor things s/he does not know like obstacles you ran into, what you did to overcome the obstacles, any a-ha moments and so on. Focus on the bolded text above and don’t spend more than 15 minutes on this question. Quality trumps quantity.

[1] If the M1HWsample.xlsx file does not download upon clicking, then right click on the link HERE and choose save or save as option to save it to your machine.