1 Term Mar. 23 Computer Fundamentals- Final Assignment (40%) Your final assessment is based on the following scenario. Polar Frozen Pops opens its first store in Calgary in May 2022. It sells two types of fresh fruit-based ice cream (regular & special) to Calgarian kids. Below is the list of revenue and expenses for the stores first four months of operation (May- August 2022). May June July August Sales Revenue: $ $ $ $ Regular 4800 6000 9120 10800 Special 4500 6000 12000 15000 Expenses: Cost of Ingredients 3720 4800 8448 10320 Supplies 930 1800 3168 3870 Shop rental 1500 1500 1500 1500 Salaries and Wages 6000 6000 6000 6000 Telephone 100 100 100 100 Internet 90 90 90 90 Electricity 279 360 634 774 Water 230 180 317 387 Marketing & Advertising 1000 700 500 500 Insurances 180 180 180 180 Instruction: Your task is to record and analyse the stores revenue and expenses using Excel and summarise your findings in a Word Document. Below are the steps to complete the tasks. Part A- Excel 1. Open a new Excel workbook and enter the list of revenues and expenses above on an Excel sheet. 2. Label your sheet "Polar May - August". 3. Add a $ sign to all amounts. 4. Convert the list into a table. 5. Use the appropriate formula/functions to calculate the following: 5.1 Totals sales (Regular+ Special) for each month. 5.2 Total Expenses for each month 5.3 Net income (Total sales- Total Expenses) for each month. 5.4 Format your table so that it highlights the important figures and looks presentable. 6. Create a bar chart for sales from May to August 2 7. Create a pie chart for May expenses. 8. Create a line chart for net income from May to August. Note: o The charts should appear on the same sheet where the table is. You may position the charts below or beside the table. o Add a title for each chart, for example, "Polar Sales" for sales, "Polar Expense", and "Polar Net Income". o Add elements and labels to your chart. o Feel free to design your chart. For example, you can change the colours or design. Your goal should be to make your chart meaningful and presentable. 9. Save your workbook with the filename: First Name, Last Name - Final Assignment- Excel (You can also do this at the beginning) Part B- Word 1. Open a new Word document. 2. Write your complete name in the Header. 3. Write the document title Polar Frozen Pops Summary of Revenue and Expenses (May to August) 4. Present a summary of your Excel calculations a. Copy and paste the charts from the Excel sheet. (There will be three charts). b. Below each chart, write down brief descriptions/highlights or the key information contained in the chart. For example, you can describe the trend (increasing or decreasing?), the highest amount or the month of the highest amount, etc. You should provide at least two highlights for each chart. You can use the following questions as your guide in identifying which information to highlight. Of course, feel free to go beyond these. i. What is the trend of sales, expenses, or net income in the first four months of operation? ii. Which expense is the highest/lowest for May? iii. Which ice cream type sells more? 5. Format your document according to the following: a. The margin should be 2.54 cm all around. b. Font size is 14 for the title and 12 for the rest of the texts. c. Font style- Calibri or Arial d. Bold headings and subheadings if any. e. The title should be centred, and the rest of the texts aligned to the left. f. Feel free to use other formatting options in the paragraph command groups. For example, you can use the number, bullets, and spacing options if you wish. Your goal is to make the document presentable and readable. g. The whole document should be a maximum of two pages. 6. Save the word document with the filename: First Name, Last Name - Final Assignment- Word. (You can also do this at the beginning) Marking Guide Total Marks 100 marks (40% of your final grade in the course). Note: You should get at least 70 marks to pass this assessment. Items for Marking Mark Allocated MarkAwarded Comments Excel (60 marks) Sheet label 2 Workbook filename 3 Dollar signs for all figures 3 Total Sales 4 Total Expenses 4 Net income 4 Table (inc. design and presentation) 10 Bar chart (inc. design and presentation) 10 Pie Chart (inc. design and presentation) 10 Line Chart (inc. design and presentation) 10 Chart & Table Design marking Outstanding (shows creativity) 9-10 Basic (minimum or above requirement) 7-8.5 Poor (below minimum requirement) 0-7 WORD (40 marks) Correct filename 2.5 Header 2.5 Title 5 Charts 10 Chart Highlights/ Description 10 Formatting of the entire document 10 Outstanding (shows creativity) 9-10 Basic (minimum or above requirement) 7-8.5 Poor (below minimum requirement) 0-7