This page has 0 threads | Add post

Exercises

Finding our exercises useful? Please consider helping us to share more!

Software ==>	Excel (113 exercises)
Topic ==>	Building models (1 exercise)
Level ==>	Average difficulty
Subject ==>	Excel training

Create a spreadsheet to model your lifestyle as a winner of a game show

This exercise is provided to allow potential course delegates to choose the correct Wise Owl Microsoft training course, and may not be reproduced in whole or in part in any format without the prior written consent of Wise Owl.

Before you can do this exercise, you'll need to download and unzip this file .

Congratulations - you've won Britain's Got Talent! The file in the folder shown above will allow you to model your costs and income - it already contains an input line for your touring fee income over the next 10 years.

This dips, sadly - fame is a star whose light fades over time - but you could always audition for Love Island when your money is beginning to run out?

Following modelling best practice create an additional income line for song downloads (900k for year 1, 850k for year 2, etc, falling to 450k by year 10):

For readability, we've hidden some of the years.

Create the following day-to-day living cost inputs in a separate Projected costs section:

Day-to-day living expenses: these should be allowed to vary for each year, but initially be £300k for every year

Extra costs associated with the loose living you plan to enjoy: again, variable per year but initially £200k for every year

As a final input, enter housing costs in a third section as two separate lines:

A single cell input for the price of the house you intend to buy

An input line for the interest rates you expect to pay (you're a bear on rates, assuming they'll start at 4% but rise 0.5% per year to 8.5% by year 10)

Your spreadsheet should now look something like this:

Your formatting and range names may be different.

On the calculations sheet, create rows giving:

Your total income (touring income + downloads)

Your total daily expenses (day-to-day expenses plus loose living)

Your total housing costs (you will foolishly take out an interest-only mortgage, so this is just the house price times the mortgage rate)

Your net income (the sum of the three lines above - you did enter costs as negative numbers, didn't you?)

Apply sensible number formatting to all relevant cells, plus conditional formatting to the net income row so that numbers greater than 100k have a green traffic light, numbers between 0 and 100k have an amber one and negative numbers have a red light.

Your final model should look something like this:

Again, your formatting and range names may be different.

Save this model as Basic Model.xlsx, then close it down!

You can unzip this file to see the answers to this exercise, although please remember this is for your personal use only.

You can find other training resources for the subject of this exercise here:

Excel classes

This page has 0 threads

Add a new post

EXERCISE TOPIC

EXCEL EXERCISES

EXCEL EXERCISES

Create a spreadsheet to model your lifestyle as a winner of a game show

Head office

London

Manchester