Working with spreadsheets/Spreadsheet design/Planning a spreadsheet

From WikiEducator
Jump to: navigation, search
OtagoPoly Logo S.png


Working with spreadsheets
Spreadsheet designIntroduction  |  Planning a spreadsheet  |  Working with columns and rows  |  Formatting data  |  Key points  |  Assessment



An spreadsheet plan on paper

Excel-plan-1.png

Even if you’re not one hundred percent sure how the spreadsheet is going to look, you do need to put some thought into what you want to achieve with it. Spreadsheet planning can be broken down into the following steps:

Step One
Decide what the worksheet is going to do - its purpose
Step Two
Decide the calculations needed to make it work - how to get the outcomes
Step Three
Decide how you want it to look - the formatting

In order to do this you will need to carefully read the brief or scenario and decide what is the worksheet supposed to do for the user? Decide what calculations you need to include.

Even if your plan is "back of the envelope" you need to keep and write up your plan in order to evaluate and if necessary, make changes to your sheet in order to meet the criteria of the brief.

You will probably find that as you’re creating your spreadsheet, you will be evaluating and modifying your plan as you go. This is ok.

We’re going to start off with two exercises – the first one will get you to look at a brief. From this you will decide on the sorts of sums that you will have to do. The second exercise will expand a little more and get you working in Excel.



OP icon activity.gif

Activity

The following exercise is about reading and interpreting a brief. You will not be creating a spreadsheet from this.

You have met Mr Mann from our previous unit. He has decided that he would like to start keeping some basic financial records using Excel. Once a month he would like to create a spreadsheet to track expenses involved in running his bookshop.

Mr Mann knows he has to track the following items. Some of these items will be expenses, and some will be income:

  • Power
  • Phone
  • Rent
  • Insurance
  • Books sold (in dollars)
  • Owners takings (As he’s self employed, this will be like wages)
  • Money spent on purchasing stock
  • Tax (In order to simplify matters we will only look at GST to be paid, not tax on income)
  • Other income

Task 1

  1. On a piece of paper or in an exercise book, copy out the above list and decide which ones are expenses and which ones are income.
  2. Check: you should have identified only Books sold and Other income as income - all the others are expenses.

Now that we have identified which items are income and expenses, we are going to set these out in Excel.

  1. Open Excel
  2. In the top left of the spreadsheet type Business income and expenditure
  3. Click in cell A4 and type Income
  4. Click in cell A8 and type Expenses

    Excel-bie-1.png

  5. Save your spreadsheet as Bookshop.xlsx





Previous.png | Next.png

Retrieved from "https://wikieducator.org/index.php?title=Working_with_spreadsheets/Spreadsheet_design/Planning_a_spreadsheet&oldid=862279"