In this assignment, I have recently been asked to design and create a
spreadsheet model which can predict the potential impact of changes in
exercise and diets on a person’s BMI. Firstly, I will be willing to produce an
efficient spreadsheet model which can show a person how their BMI would
change over a period of at least ten weeks, based on activities they do and the
foods and they eat. From this, I will create 3 designs for a data model so that
each of the client requirements are met successfully which will include
worksheet structure diagrams and being able to provide a test plan.
Then, I will be reviewing the design with other clients and two other people to
identify and inform improvements. The model will be designed to be used by
people who are not experts in spreadsheets and should include a user
interface which allows the user to enter personal data,activity choices,the
length of time a person wishes to spend on each activity, and food menu
choices. In order to create that, I will use Microsoft Excel spreadsheet model
which enables a user to sort data, can see the report easily, can perform
calculations such as a person's BMI, and easily create data visualisations
charts.
The purpose of this is to help local people become fitter and healthier by
reducing their Body Mass Index (BMI). The model will be easy to navigate as
they are not experts in spreadsheets, I will include a text box on my
spreadsheet model to make it clear about what to type and where to go next.
Furthermore, I will include navigation bars so they can just go back to the
home page or dashboard page easily.
Furthermore, I will be explaining my justification to how the requirements are
fully fulfilled and then providing a well detailed and reasoned evaluation of the
optimised data model against client requirements that already exist.
Client requirements- / specifications
● Easy to use
● Allow the user to enter their personal information
● The module should calculate a person’s BMI
● Show a person’s BMI changed over 10 weeks
● It should show each person’s calorie intake
● How much calories burned
, ● And their predicted weight and BMI
● They would be able to see the difference between their actual weight
and BMI and their predicted one
● Would tell them if their BMI is overweight, underweight or normal
Functions that I will be using
● I will use Vlookup to calculate how much calories burned and intake
● I will also use conditional formatting to change the colour for BMI
category
My aim and objectives-
● Initially, the aim is to help the member understand the outcomes. For
example, I would use "conditional formatting" to make the values of BMI
clear to the user.
● I will create a table which will show the different food items that are
available, as well as that the calories will be displayed. The table I will
create will show different food items and their calories per portion. My
aim is to help the member understand how many calories they are
consuming
● A cell that displays the customer’s weight and body mass index (BMI).
My aim is the initial step would be to make a table showing the number
of calories consumed during the week period as well as the number of
calories burnt through exercise so that the user will have an idea if they
need to burn more calories or not
● Another aim is that my model will produce reports that initially will be
used to present the 10- week progress plan on diet and exercise about
the user. There will be 2 cells one of which will provide an overall picture
of nutrition and exercise during the 10-week period. In this process, the
consumption of food and the duration of exercise will be recorded.
Another one where they will be able to enter their actual weight and BMI
to see the differences.
, ● If incorrect information is input, this should lead to input errors. In order
to prevent input errors I will make sure they don’t have to type too much
such as I will use a drop down box.
Designs-
Worksheet structure diagram-
This shows how my sheets are linked to each other, when a user enters their
data it goes to another page to get information.
The data model I’m creating will involve a range of different pages. The pages
I will include listed below-
● Home page
● Personal details
● Food menu
● Exercises
● Progress
● Dashboard
Firstly I will create 3 designs for the home page then I will get feedback from
my clients and 2 other people. Then I can decide which one is good or if I have
to make any improvements in any of these. I might agree or disagree with it
but I will explain why I have chosen that particular design.
, The pages I will be creating are-
Progress will be the first one on my list because this is likely to be the most
visited page as user will record their each weeks food intake and the activities
they do also here I will add table where they can add their daily meals and
activities also I will add another table called “weight and BMI tracker” which will
calculate their each weeks how much calories burned, calories gained, weight
kg lost/gained, weight each week.
Personal details- Here the users need to type their personal information such
as name, phone no, email, address, post code, town, gender, date of birth.
Food menu- The user don’t have to type anything, they will check what are the
food available and how much calories
Exercise- The user doesn't have to type anything. They can just see what the
activities are, how much calories they burn and time.
Design 1-
This is the first home
page design I have
created. It's clear you
can see the title and
each navigation bar
clearly. I only used black
and white colours
