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.

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?

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?

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.