A few people are asking me about the progress of my latest book: working title is Spreadsheeting with Mr Sprite.
Good news: I have completed around 7 or 8 chapters now and have carried out my research and preparations for a few more. The book is aimed at Excel 2007 and is a comprehensive introduction to it. More than that, it includes a number of practical examples that people can use as templates for real life work. I am collaborating on part of the book with a manpower planning specialist (the world's number one, no less) and he is giving me material and ideas for what to include in that section.
I am aware that Microsoft is within a year or so of releasing Excel 2010 so I am keen to ensure I get in well ahead of that deadline!
Here is the current contents page ... subject to change. If YOU would like me to include something that you cannot see here, please write to me and I will consider all suggestions. duncan at duncanwil dot co dot uk ... please interpret that! I get enough spam in my inbox so don't want to encourage any more.
Contents
Part One: Introductory
1 Basics
- Introduction
- Pencil and Paper
- Basic Functionality
- The Ribbon
- Screen Resolution
- Minimise the Ribbon
- The Quick Access Toolbar: the QAT
- Basic Rules
- Add Subtract Multiply Divide
- More Complex Calculations
- Try it!
- Built in Functions: SUM ... AVERAGE ... MIN ... MAX
- Auto Fill
- Auto Fill Menu
- Formatting Cells
- Moving Around your Workbook
- Changing Column and Row Sizes
- Moving a Column or Row
- Formatting Text
- Cell Border
- Try it!
- Consolidation Exercise
- Selecting non Contiguous Ranges
- Why Select Non contiguous Ranges?
- Print Areas
- Transposing Data
- Cell and Range Addressing
- Range Names
2 Charting
- Introduction
- The Rules of Charting
- Setting up a Chart the Easy Way: the F11 key
- Default Chart Change
- Creating a Template Char
- Embedding a Chart with the Chart Wizard
- Embedding by Moving
- Titles and Axes Labels
- Copy Charts: make clones an move them
- Other Charts
- Category Axis Contains Labels from Three Columns
- Conditional Colours
- Waterfall Chart
- Create a Stacked Column Chart
- Finished Waterfall Chart
- Alternatives
- Percentage Waterfall Chart
- Radar Chart
- Self Expanding Chart
- Excel 2007 Solution 1
- Excel 2007 Solution 2a
- Excel 2007 Solution 2b
- Selecting Data from a Combo Box
- Spreadsheet Allowing the User to Highlight Alternative Scenarios
- Table and Chart Selection Including a Combobox
- Charts with Scroll Bars
- Basic Chart and Rates of Change Chart
- Ten year financial review of a company
- Double Vertical Axes
- Additional Charting Exercises
- Text to Columns
- Deriving Values from Published Charts
- Dashboards
- More Advanced Chart
3 Introduction to Pivot Tables
- Introduction
- Definition of a Pivot Table
- Why you need a Pivot Table
- Example PTs
- Copying a Pivot Table
- A Copy of a PT is a Clone of a PT
- Copying your PT
- Changing Sum of to Average of to Count of ...
- Moving a Field from Row to Column
- Drag and Move Within the PT
- Drag and move the chosen field within the Task Pane
- Filtering a Field
- Sorting a Pivot Table
- Formatting Fields
- Calculated Fields
- Drilling Down
- Calculated Item
- Other Calculated Item Features
- Adjusting the Formula
- More Complex Formulas
- Forecasting with a Calculated Item
- Values
- Show Values As
- Grouping PT Data
- Starting or Setting up a PT
- The Grouping Process
- When Grouping is a Problem
- Pivot Chart
- Formatting a Pivot Chart
- A Pivot Chart with a Data Table
- Formatting
- Page Fields
- Multiple Page Fields
- Reporting with Page Fields
- Pivot Table from an External Source
- Part Two: Data Analysis
4 Pivot Tables 2
- Expense account analysis
- Double entry bookkeeping
- Trial balance
- Final accounts/reports
5 Ratio and Other Company Analysis
- Ratio analysis of company information ranging from the simple to the complex
- Common size statements
- Growth ratios in ratio analysis
- Z Score analysis
- Using the ToolPak Add-In that comes with Excel
- Descriptive statistics
- Histograms
- Correlation analysis
- Ordinary least squares (regression) analysis
- There is a database of company data to accompany this chapter and the detail of the chapter will refer to that database throughout.
6 Statistical Analysis
- This chapter enhances the work started in chapter 5 above and includes
- t statistic
- Standard Error
- P Value
- F statistics
- Confidence intervals
- Multiple regression analysis
- The relevant range
- Chi square test
- Non parametric tests
- The Box and Whisker Plot
- More Useful functions for analysis
- INDIRECT
- INDEX
- OFFSET
- MATCH
Part Three: Decision Making
7 Behaviour of Costs
- Splitting costs into their fixed and variable elements
- MINVERSE
- MMULT
- Non linear costs
- The linear treatment of costs: true and fair?
8 Cost Volume Profit Analysis
- Building on the work of chapter 1 of this part of the book, we will now explore the practical application of the behaviour of costs under the heading of cost volume profit or break even analysis
9 Marginal Costing and Decision Making
- Continuing with the theme of the behaviour of costs and the application of it to management accounting situations, in this chapter we explore how a knowledge of marginal costs can be used in decision making.
10 Traditional and Activity based costing
- Traditional overhead analysis: absorption costing
- Allocation
- Apportionment
- Re apportionment
- Overhead absorption rates
- Absorption costing
- Activity Based Costing
11 Budgeting
- This chapter concentrates on the preparation of budgets and budgeting reports that include:
- Functional budgets
- Cash budgets
- Master budget
- Budget reports
- Balanced scorecard reporting
- Activity based budgeting
12 Capital Budgeting
- Payback
- Accounting Rate of Return
- Net Present Value
- Profitability Index
- Internal Rate of Return
- Divisibility of Projects
13 Excel Techniques for Analysis and Decision Making
- Goal Seek
- Scenario Manager
- SOLVER
I'm nervous about leaving it at 13 chapters and will probably expand that to accommodate the manpower planning spreadsheet models.
DW
