The Book-keepers Forum (BKF)
The Book-keepers Forum (UK) -> Professional Book-keepers in Practice -> AAT Spreadsheet Software exam tomorrow....I don't understand how I have got this formula wrong...any ideas please?
Post Info TOPIC: AAT Spreadsheet Software exam tomorrow....I don't understand how I have got this formula wrong...any ideas please?
rachel_mclean


Guru

Status: Offline
Posts: 625
Date:
AAT Spreadsheet Software exam tomorrow....I don't understand how I have got this formula wrong...any ideas please?
Permalink Closed

Hi, I have the AAT level 3 spreadsheet software exam tomorrow and am doing a bit of last minute revision no and I have put in a formula to work out the net and gross profit margin but it is adding on number so instead of 34.78% it is coming up as 3478.34%. The forumals I have been using are gross profit/sales x 100 and net profit/sales x 100.

Please can anyone see where I am going wrong? I have attached both the numbers and the formulas I have in below

Thanks in advance for any help on this!

Rachel

 

        
 SalesCost of SalesGross ProfitExpensesNet profitGross Profit MarginNet Profit Margin
April£9,900.00£2,232.00£7,668.00£3,720.00£3,948.007745.45%3987.88%
May£9,676.00£4,920.00£4,756.00£8,200.00-£3,444.004915.25%-3559.32%
June£10,682.00£1,926.00£8,756.00£3,210.00£5,546.008196.97%5191.91%
July£23,466.00£4,146.00£19,320.00£6,910.00£12,410.008233.19%5288.50%
August£11,420.00£4,896.00£6,524.00£8,160.00-£1,636.005712.78%-1432.57%
September£23,279.00£11,766.00£11,513.00£19,610.00-£8,097.004945.66%-3478.24%
        
 £88,423.00£29,886.00£58,537.00£49,810.00£8,727.00  
 SalesCost of SalesGross ProfitExpensesNet profitGross Profit MarginNet Profit Margin
April99002232=SUM(B4-C4)3720=SUM(D4-E4)=(D4/B4)*100=(F4/B4)*100
May96764920=SUM(B5-C5)8200=SUM(D5-E5)=SUM(D5/B5)*100=(F5/B5)*100
June106821926=SUM(B6-C6)3210=SUM(D6-E6)=SUM(D6/B6)*100=(F6/B6)*100
July234664146=SUM(B7-C7)6910=SUM(D7-E7)=SUM(D7/B7)*100=(F7/B7)*100
August114204896=SUM(B8-C8)8160=SUM(D8-E8)=SUM(D8/B8)*100=(F8/B8)*100
September2327911766=SUM(B9-C9)19610=SUM(D9-E9)=SUM(D9/B9)*100=(F9/B9)*100
        
 =SUM(B4:B10)=SUM(C4:C10)=SUM(D4:D10)=SUM(E4:E10)=SUM(F4:F10)  
        
        


__________________

Rachel
Meadowlands


Senior Member

Status: Offline
Posts: 246
Date:
Permalink Closed

Have you formatted those cells to show %? If so it seems to add in the extra numbers. If you format as % but don't multiply by 100 you will get the right answer.

Good luck - its a pretty straightforward exam just take your time

__________________

Alison - Simply Balanced Solutions
Cheshire


Master Book-keeper

Status: Offline
Posts: 8646
Date:
Permalink Closed

Hi Rachel
Might be better to include the actual spreadsheet.

__________________

 Joanne 

Winner of Bookkeeper of the Year 2015, 2016 & 2017 

Thoughts are my own/not to be regarded as official advice,which should be sought from a suitably qualified Accountant.

You should check out answers with reference to the legal position
Shamus


Forum Moderator & Expert

Status: Offline
Posts: 11981
Date:
Permalink Closed

That makes no sense that its not working rachel.

can you send your spreadsheet as an attachment to shamus.bkn@virginmedia.com and I'll take a look.

many thanks,

Shaun.



-- Edited by Shamus on Sunday 24th of April 2016 07:40:10 PM

__________________

Shaun

Responses are not meant as a substitute for professional advice. Answers are intended as outline only the advice of a qualified professional with access to all relevant information should be sought before acting on any response given.
Cheshire


Master Book-keeper

Status: Offline
Posts: 8646
Date:
Permalink Closed

I mean - by way of an attachement



__________________

 Joanne 

Winner of Bookkeeper of the Year 2015, 2016 & 2017 

Thoughts are my own/not to be regarded as official advice,which should be sought from a suitably qualified Accountant.

You should check out answers with reference to the legal position
Lorry10


Veteran Member

Status: Offline
Posts: 88
Date:
Permalink Closed

Aren't you dividing by the Cost of Sales here, rather than the Sales Revenue ?

__________________

Lorraine 
Shamus


Forum Moderator & Expert

Status: Offline
Posts: 11981
Date:
Permalink Closed

Hi Peeps,

sounds like everyone wants to play with this one! :D



__________________

Shaun

Responses are not meant as a substitute for professional advice. Answers are intended as outline only the advice of a qualified professional with access to all relevant information should be sought before acting on any response given.
Lorry10


Veteran Member

Status: Offline
Posts: 88
Date:
Permalink Closed

Sorry, no, just rechecked and that doesnt explain the result. It sounds like cell formatting

__________________

Lorraine 
Meadowlands


Senior Member

Status: Offline
Posts: 246
Date:
Permalink Closed

Don't think I explained it very well - if you format as a % you don't need to divide by 100. So to show as a percent do net profit/sales but don't times by 100 and format cells to show %


__________________

Alison - Simply Balanced Solutions
rachel_mclean


Guru

Status: Offline
Posts: 625
Date:
Permalink Closed

Thanks everyone, wow really quick response. You were right Alison, I took out the *100 and it corrected it.

I have attached the original spreadsheet anyway.

I'm not sure how well I'm going to do, I've only been going through the textbook the last couple of days as I thought I wouldn't have to do much as I use excel most days. How wrong I was!! pivot tables, conditional formatting......I don't do most of this stuff,oh dear! it's going to be a long night!!

Any tips on the  most important things to learn....would be most grateful for blankstare

Thanks again



-- Edited by rachel_mclean on Sunday 24th of April 2016 08:12:11 PM

Attachments
__________________

Rachel
Shamus


Forum Moderator & Expert

Status: Offline
Posts: 11981
Date:
Permalink Closed

You have defined the field as a percentage but you are still multiplying by 100.

Change the fields to number format and it will work fine

__________________

Shaun

Responses are not meant as a substitute for professional advice. Answers are intended as outline only the advice of a qualified professional with access to all relevant information should be sought before acting on any response given.
Mike W


Veteran Member

Status: Offline
Posts: 52
Date:
Permalink Closed

Hi Rachel

As Shaun has indicated your formula is correct, but it looks like you have also formatted the cells to % so Excel is doubling-up? Re-format as a number and it should work?

__________________

Mike
Shamus


Forum Moderator & Expert

Status: Offline
Posts: 11981
Date:
Permalink Closed

or just don't multiply by 100.

at the moment you are basically saying (d4/b4)*100*100

HTH,

Shaun.

 

p.s. sorry crossed in the post mike.



-- Edited by Shamus on Sunday 24th of April 2016 08:18:36 PM

__________________

Shaun

Responses are not meant as a substitute for professional advice. Answers are intended as outline only the advice of a qualified professional with access to all relevant information should be sought before acting on any response given.
abacus12345


Guru

Status: Offline
Posts: 1363
Date:
Permalink Closed

I'm too late to the party

__________________

Johnny  - Owner of an overly-active keyboard. 

A man who can read, yet doesn't, is in no way wiser than a man who can't.

 
Meadowlands


Senior Member

Status: Offline
Posts: 246
Date:
Permalink Closed

Rachel,

The exam wasn't to bad. There were Hlookup, pivot tables etc but lots of it was pretty straightforward formula and formatting. Just read everything through carefully and follow the instructions and I am sure you will be fine.

good luck

__________________

Alison - Simply Balanced Solutions
rachel_mclean


Guru

Status: Offline
Posts: 625
Date:
Permalink Closed

Thanks Alison, the pivot tables and V and H look ups are on the top my list this evening

Thanks again

__________________

Rachel
Leger


Master Book-keeper

Status: Offline
Posts: 3904
Date:
Permalink Closed

Looks like I'm going to have to learn a lot more on excel when I get to level 3 lol.  To my shame although I've heard of pivot tables, I haven't a scooby what they do.

Good luck with the exam Rachel



__________________

John 

 

 

 Any advice given is for general guidance and professional advice should be sought applicable to your circumstances.
rachel_mclean


Guru

Status: Offline
Posts: 625
Date:
Permalink Closed

Thanks Alison, the pivot tables and V and H look ups are on the top my list this evening. I'm getting there...(I think)

Thanks again





__________________

Rachel
Shamus


Forum Moderator & Expert

Status: Offline
Posts: 11981
Date:
Permalink Closed

abacus12345 wrote:

I'm too late to the party

Lol Johnny,

it was an Excel query, almost the moment that Rachel posted we were beating each other off with sticks in order to answer it.

 

 

 



__________________

Shaun

Responses are not meant as a substitute for professional advice. Answers are intended as outline only the advice of a qualified professional with access to all relevant information should be sought before acting on any response given.
rachel_mclean


Guru

Status: Offline
Posts: 625
Date:
Permalink Closed

Data validation.....ahhh. Sorry for the last minute questions as there are some people who studied long and hard for this, I've been a bit of a cheater no

So I have to use data validation to circle retailer margins that are below 17.50% on this worksheet. So I've gone DATA - DATA VALIDATION - ALLOW: Decimal, DATA: less than, MIN: 17.5, which to me looks right and I go cirlce invalid data and nothing is circled!! I did try is greater than or equal to to see if anything would circle and they all did! does anybody know what I am doing wrong here please?

LoCal Shakes Pricing and Margins


Shakes Cost Price Discount Discounted Price Marked up Price "Recommended
Retail Price" Retailer Margin
1000 £5.00 2.00% £4.90 £6.13 £7.00 12.50%
2000 £5.00 5.00% £4.75 £5.94 £7.00 15.18%
3000 £5.00 6.00% £4.70 £5.88 £7.00 16.07%
4000 £5.00 6.00% £4.70 £5.88 £7.00 16.07%
5000 £5.00 7.00% £4.65 £5.81 £7.00 16.96%
6000 £5.00 8.00% £4.60 £5.75 £7.00 17.86%
7000 £5.00 8.00% £4.60 £5.75 £7.00 17.86%
8000 £5.00 9.00% £4.55 £5.69 £7.00 18.75%
9000 £5.00 10.00% £4.50 £5.63 £7.00 19.64%
10000 £5.00 11.00% £4.45 £5.56 £7.00 20.54%




__________________

Rachel
Shamus


Forum Moderator & Expert

Status: Offline
Posts: 11981
Date:
Permalink Closed

Leger wrote:

Looks like I'm going to have to learn a lot more on excel when I get to level 3 lol.  To my shame although I've heard of pivot tables, I haven't a scooby what they do.

Hi John,

any help that you need with Excel just ask.

There are some video's on YouTube called ExcelIsFun... Sounds like the name that you would give to a primary school video I know but they are some of the very best video's that I've ever found and explain absolutely everything. Unfortunately there are actually thousands of videos in the collection so its near impossible to work through all of them.

Personally I've done a couple of hundred of them and even the simple one's I've come away learning something (and I thought that I was good at Excel before I did them!).

Its definitely a YouTube channel thats worth subscribing to.

Pivot tables very much like conditional formatting are nothing to worry about once you understand them especially after they changed to make them sooo much easier and user freindly in Excel 2007 (if you last tried them in 2003 or before they are really not like that at all anymore).

Once you start using pivots properly you will find yourself summing things much less and you also start to design your spreadsheets better as feeders for the pivot tables.

 

p.s. Pivot tables summarise the data that you identify in your feeder table. You can then drill down into any field.I find them quite good for building dashboards where you can have several pivots on the same page taking different views of the same data. Perhaps comparing months or years side by side helping with easy trend diagnosis and being able to quickly drill down into the summarised anomalies to identify underlying issues.

As you can tell, I'm a big fan of pivoit tables!



__________________

Shaun

Responses are not meant as a substitute for professional advice. Answers are intended as outline only the advice of a qualified professional with access to all relevant information should be sought before acting on any response given.
Leger


Master Book-keeper

Status: Offline
Posts: 3904
Date:
Permalink Closed

Thanks Shaun 

I will check that youtube link out when I have time, and I'll be sure to ask you anything that I get confused lol



__________________

John 

 

 

 Any advice given is for general guidance and professional advice should be sought applicable to your circumstances.
Bucks Bodger


Senior Member

Status: Offline
Posts: 206
Date:
Permalink Closed

If I can just chip in with my four pennyworth here - like Rachel I sat Level 3 AAT a couple of years ago, and like her, I thought I
was reasonably proficient at it until the exam came up with things I'd barely even heard of, and was convinced I would never use.

However, once I had learned what pivot tables were, I find I now use them to summarise expenses and credit card statements, so
it wasn't all a complete waste of time ! (now VLookups and HLookups is another matter in my world!)

Let us know how you got on, Rachel !



__________________

Eunice Cubbage
abacus12345


Guru

Status: Offline
Posts: 1363
Date:
Permalink Closed

How did the exam go?

__________________

Johnny  - Owner of an overly-active keyboard. 

A man who can read, yet doesn't, is in no way wiser than a man who can't.

 
rachel_mclean


Guru

Status: Offline
Posts: 625
Date:
Permalink Closed

Thanks for all your help

I got the results this morning PASSED!!

I got 3 things wrong
I couldn't remember how to do HLOOKUP twice and then I couldn't work out how to use 'IF' but all the rest had no errors, so very pleased! even managed to do a pivot table, a very basic one. The pivot table asked for was nowhere near as complicated as the textbook!

2 more exams to go for level 3 then on to level 4. Ethics and costs and revenues.




__________________

Rachel
Cheshire


Master Book-keeper

Status: Offline
Posts: 8646
Date:
Permalink Closed

Well done. Rachel

__________________

 Joanne 

Winner of Bookkeeper of the Year 2015, 2016 & 2017 

Thoughts are my own/not to be regarded as official advice,which should be sought from a suitably qualified Accountant.

You should check out answers with reference to the legal position
Meadowlands


Senior Member

Status: Offline
Posts: 246
Date:
Permalink Closed

congratulations! Knew you could do it. Ive just got Ethics left to do - must get motivated

__________________

Alison - Simply Balanced Solutions
abacus12345


Guru

Status: Offline
Posts: 1363
Date:
Permalink Closed

Congratulations :)

__________________

Johnny  - Owner of an overly-active keyboard. 

A man who can read, yet doesn't, is in no way wiser than a man who can't.

 
Leger


Master Book-keeper

Status: Offline
Posts: 3904
Date:
Permalink Closed

Well done Rachel, brilliant news



__________________

John 

 

 

 Any advice given is for general guidance and professional advice should be sought applicable to your circumstances.
Hyperion


Veteran Member

Status: Offline
Posts: 53
Date:
Permalink Closed

Well done Rachel!

I should be starting my level 4 in September all being well.

I think the ethics module turned out to be one of my personal favourites. I kinda went with "just do the right thing monkey!" attitude :).

Good luck with it all

Parmi


__________________

Parminderjit Nunwa
Page 1 of 1  sorted by
 
Quick Reply

Please log in to post quick replies.
The Book-keepers Forum (UK) -> Professional Book-keepers in Practice -> AAT Spreadsheet Software exam tomorrow....I don't understand how I have got this formula wrong...any ideas please?
Tweet this page Post to Digg Post to Del.icio.us
Members Login
Username 
 
Password 
    Remember Me  
©2007-2024 The Book-keepers Forum (BKF). All Rights Reserved. The Book-keepers Forum (BKF) is a trading division of Bookcert Ltd. Registered in England Company Number 05782923. 2 Laurel House, 1 Station Rd, Worle, Weston-super-Mare, North Somerset, BS22 6AR, United Kingdom. The Book-keepers Forum and BKF are trademarks of Bookcert Ltd. This forum is a discussion forum only. There will usually be more than one opinion to any question and any posting should not be viewed as a definitive solution. No responsibility for loss occasioned to any person acting or refraining from action as a result of any posting on this site is accepted by the contributors or The Book-keepers Forum. In all cases, appropriate professional advice should be sought before making a decision. We reserve the right to remove any postings which are offensive, libellous, self-promoting or engaged in covert marketing. We will not notify users of removals. The views expressed in the forum posts are those of the individual and do not necessary reflect or agree with those of The Book-keepers Forum. Any offensive or unsuitable posts will be removed by the moderators. Any reader of this forum can request for a post to be looked into by sending an email to: bookcertltd@gmail.com.

Privacy & Cookie Policy  About