+ Reply to Thread
Results 1 to 6 of 6

excel activity advice

  1. #1
    Registered User
    Join Date
    12-27-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003
    Posts
    5

    excel activity advice

    I dont expect the work to be done for me but any help would be greatly appreciated .
    Gary and Tonya Smith own the Hendon Sign Company which specialises in creating custom signs for local retail businesses. The company produces two types of signs – ink jet signs and vinyl signs. Ink jet signs cost £20 to manufacture per unit, while vinyl signs cost £50 to manufacture. Total manufacturing costs for each type of sign are calculated by multiplying units sold by unit manufacturing cost. Ink jet signs are sold for £50 and vinyl signs are sold for £65. The company currently spends £50,000 on rent and utilities, £35,000 on administration and £25,000 on marketing.

    The company lost £5,000 in 2011 and the Smiths are concerned about the future. In order for the business to survive, they need to increase sales and/or reduce costs. As their business consultant, you offer to create a spreadsheet that projects their income and expenses for 2011-2014.

    Status Quo scenario
    The status quo scenario is based on the figures for 2011, which are as follows:-
     Ink jet signs (units sold) – 2000
     Ink jet signs (sales price) – £50
     Vinyl signs (units sold) - 3000
     Vinyl signs (sales price) - £65
     Ink jet signs (unit manufacturing cost) - £20
     Vinyl signs (unit manufacturing cost) - £50
     Ink jet signs (total manufacturing costs) - £40,000
     Vinyl signs (total manufacturing costs) - £150,000
     Fixed costs (Rent/Utilities) – £50,000
     Administration costs – £35,000
     Marketing costs – £25,000

    The status quo scenario is based on the following projections for 2011-2014:-
     Sales of both types of signs increasing by 5% a year.
     The selling price of both types of signs increasing by 1% a year.
     Unit manufacturing costs of both types of signs increasing by 1% a year
     Rent/Utilities increasing by 1.5% per year
     Administration costs decreasing by 10% per year
     Marketing costs increasing by 10% per year

    While Gary, Tonya and you broadly agree on the status quo scenario, each of you has different views about the best way forward for the Hendon Sign Company.
    Ink Jet Focus Scenario
    Gary is in favour of focusing on the ink jet signs. He wants to reduce the selling price of the ink jet signs by 10% a year. Gary believes that reduced prices, combined with aggressive advertising, will increase demand, so he projects unit sales of ink jet signs to increase by 25% a year. The higher volume of sales, he believes, will reduce the unit manufacturing costs for ink jet signs by 5% a year. Gary’s projections for vinyl signs are the same as those in the status quo scenario. Gary also agrees with the figures in the status quo scenario regarding all other costs.
    Control Cost Scenario
    You suggest that the Hendon Sign Company focuses on reducing costs. You propose to reduce the unit manufacturing costs of both types of signs by 10% a year. Your projections regarding unit sales prices are identical to those in the status quo scenario. However, you believe that the company can afford to spend slightly less on marketing. You therefore propose to increase the marketing budget by only 5% a year. In your view, this will be sufficient to boost unit sales of both types of sign by 10% a year. Your projections for rent/utilities and administration costs are the same as those in the status quo scenario.
    Move Location Scenario
    Tonya is in favour of moving the business to a smaller plant where the rent and utilities will decrease by 20% a year. Other than this, her projections are the same as those in the status quo scenario.
    Create a single spreadsheet that shows the effects of each of the above scenarios. The worksheet must conform to the following requirements:-

    1. It must have a suitable layout with clearly labelled sections and headings. The main body of the spreadsheet must be clearly divided into Income, Costs and Pre-tax profits.

    2. It must include an Assumptions area, showing projected rates of change and Year 1 values. All values loaded in the assumptions area must be linked to the main body of the spreadsheet through the use of suitable formulas.

    3. Appropriate formatting must be used (including shading, font colours and cell borders, etc) to produce a visually attractive but usable worksheet. Conditional formatting should be applied to Pre-Tax Profits, displaying losses in red with brackets, and profits in blue.

    4. Your workbook must include a Scenario Summary Report, with edited labels and formatting, that is easy to read and understand.

    Load the values for the Status Quo scenario in the assumption area. Rename the worksheet Task 1 and save the workbook as Portfolio1.

    In the same workbook (Portfolio 1) rename sheet 3 as Task 2. Now complete Task 2 below.
    Last edited by s-e-n-s-e; 12-28-2012 at 09:49 AM.

  2. #2
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: i need help with this cw im crap on excel

    hi and welcome to the forum, but I'm afraid:

    Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
    To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

    this looks a lot like homework to me-is it?
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  3. #3
    Registered User
    Join Date
    12-27-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: i need help with this cw im crap on excel

    it is an activity i have been given and i am struggling to do it

  4. #4
    Valued Forum Contributor
    Join Date
    07-27-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    826

    Re: excel activity advice

    Hi s-e-n-s-e,

    Just wondering how you spell "activity"? Is it like this: "h-o-m-e-w-o-r-k"? Or maybe "a-s-s-i-g-n-m-e-n-t"? Because that's very much what this looks like, and - for very good reasons - there are rules regarding homework.

    However, that wouldn't preclude us from assisting you or pointing you in the right direction if you have a specific problem to deal with. So, perhaps you could elaborate on how far you've gotten yourself (uploading the work you've done to date would help enormously), and tell us where, specifically, you're stuck?
    Brendan.


    __________________________________________________________________________________________________
    Things to consider:

    1) You can thank any poster by clicking the * at the left of a helpful post.
    2) You can help to keep the forum tidy by marking your thread as "Solved", if it has been answered to your satisfaction.
    3) Help us to help you, by uploading a sample workbook, showing the type of data you're dealing with, and clearly indicating what the results should be.

  5. #5
    Registered User
    Join Date
    12-27-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: excel activity advice

    Looool.its ok I've managed to get some help on my activity.thanks for the replies and if I have any other problems il upload a sample of what I'm stuck on.thanks

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,801

    Re: excel activity advice

    indeed BB1972...
    The company lost £5,000 in 2011 and the Smiths are concerned about the future. In order for the business to survive, they need to increase sales and/or reduce costs. As their business consultant, you offer to create a spreadsheet that projects their income and expenses for 2011-2014.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1