making a workbook

[punkiegirl420]

David is the manager of a furniture store, and he is planning a sale. The store only has 75 square feet available to display and stock merchandise. During the sale, each of the folding table cost $5, retails for $11, and takes up two square feet of space. Each chair cost $4, retails for $9, and takes up one square foot of space. The max amount allocated for purchasing the tables and chairs for the sale is $280. David doesn't think he can sell more than 40 chairs, but the demand for tables is virtually unlimited. David has asked you to help him determine how many tables and chairs he should purchase in order to make the most profit.

I have to make a workbook from stractch. I have never done this before, so I need major help- if anyone is willing to do this. First, I have to make an Orders worksheet containing an income statement for the furniture store as welll as an estimate on the amount of space that the folding tables and chairs will take up in the display area.

I don't even know where to start.

[Bryan Hessey]

David is the manager of a furniture store, and he is planning a sale. The store only has 75 square feet available to display and stock merchandise. During the sale, each of the folding table cost $5, retails for $11, and takes up two square feet of space. Each chair cost $4, retails for $9, and takes up one square foot of space. The max amount allocated for purchasing the tables and chairs for the sale is $280. David doesn't think he can sell more than 40 chairs, but the demand for tables is virtually unlimited. David has asked you to help him determine how many tables and chairs he should purchase in order to make the most profit.

I have to make a workbook from stractch. I have never done this before, so I need major help- if anyone is willing to do this. First, I have to make an Orders worksheet containing an income statement for the furniture store as welll as an estimate on the amount of space that the folding tables and chairs will take up in the display area.

I don't even know where to start.

To make a new workbook from scratch simply press the 'New' icon, usually under the 'File' icon in the toolbar, or select File, New, or Ctrl/N.

Obviously the answer is 18 tables and 39 chairs, but you need to show how you worked that out, so

Headers for columns = A-Description, B-Quantity, C-Cost per Unit, D-Sell per Unit, E-Space per Unit, F-Profit Margin per Unit, G-Cost, H-Area,

Rows 2 & 3, enter description tables & chairs, Quantity 1 & 40, Cost pu 5 & 4, Sell pu 11 & 9, Area pu 2 & 1

Profit margin = (sell-buy)/buy
=(D2-C2)/C2

and formula fill that to row 3

Chairs show the most profit, thus sell only chairs, however, there is a maximum 40 chairs. (indicating that we are on the right track)

column H Area = Quantity * Space pu

the total of column H is a max of 75

40 chairs @ 1 sq leaves 35, but tables are 2 which would leave 1 sq waste. (a half of a table compared to a chair, = utilise the full space for maximum value)

39 chairs leaves 36, which is 18 tables.

fill in your remaining formula, and add a Total Area Occupied =Sum(H2:H3)

it must = 75

Add a total of cost =Sum(G2:G3) and ensure this does not exceed your $280

Hope this helps.
---

[punkiegirl420]

thanks so much!

I have a few more questions if you don't mind. I am confused about the quanity. Do I just type 1 & 40 in it as you said?

For column H.. what is * space pu mean?

Where should I add a total area occupied column?

thanks again so much!

[Bryan Hessey]

thanks so much!

I have a few more questions if you don't mind. I am confused about the quanity. Do I just type 1 & 40 in it as you said?

For column H.. what is * space pu mean?

Where should I add a total area occupied column?

thanks again so much!

All you were given for initial Quantity was maximum 40 chairs, so just start with 1 & 40

Space per unit

Where to put a column, anywhere that suits you, logically it would come next-ish after Quantity & Space per unit (these are used to calculate)


note, for totals you would (of course) use =Sum(H2:H3) on (say) row 4 (H4)
---