Business Finance problems

[hrmajor]

I have no clue how to do this assignment. We are to answer two verbal questions in Excel first.
The second part of the assignment is about sales forecast, variable costs, total contribution margin, aggregate contribution margin ration, and break-even point in dollars.
I am not an accounting or finance major but for some reason we have to take this class.
Can anyone help me?

 

[Robert Mika]

Hi hrmajor,
If this is an assigmnent probably your tutor shoudl help you or give some advice what to do.

You can post your question here and maybe someone will come up with an answer

 

[hrmajor]

I do not have a tutor. Our instructor just says we need to get comfortable using Excel in verbal and numerical ways. That was absolutely no help.

 

[Smitty]

Welcome to the Board!

Robert wasn't being rude, it's just that we generally don't do homework here. HOWEVER, we'll do whatever we can to help you get to where you need to be, but you need to be an active participant.

As for this part:

The second part of the assignment is about sales forecast, variable costs, total contribution margin, aggregate contribution margin ration, and break-even point in dollars.

Is there a question in there somewhere? What part of it are you having a hard time with? It'll help if you can share some examples of what you've done and where you're stuck.

And it doesn't matter if you're an Accounting or Finance major when it comes to using Excel, it's used all over the place. Hell, I have a degree in Ranch Management and I use Excel daily.

Take a look at the link beneath my sig for how to post a shot of your sheet(s).

 

[hrmajor]

Oh no! I was saying my instructor's comments were not help! I'm so sorry you thought I was talking about Robert's reply.

I use Excel although I am not a guru by any means.

The assignment has two discussion questions which we do all the time, but in Word. This instructor wants us to answer them in Excel. How do you do that?

The second part of the assignment gives a fictional company and product line. It give total of sales and contribution of margin ratio.

We have to do a table showing sales, total variable costs, and total contribution margin associated with each product, based on a sales forecast for the next year, of $250,000 and total fixed costs of $50,000.

We have to show what the aggregate contribution margin ratio is, indicative of the sales mix. Then we have to show at that sales mix what the break even point is in dollars.

I am not asking anyone to provide me the answers. I just don't know how to set it up, or do the verbal questions.

 

[Smitty]

The assignment has two discussion questions which we do all the time, but in Word. This instructor wants us to answer them in Excel. How do you do that?

If you want to teach your professor a thing or two, then here's a trick (since most people who use Excel as a text editor (which it isn't) will merge cells, which is BAD). You can enter your text, then select the range you think it will cover and goto to Home-->Editing-->Fill-->Justify. It won't merge cells, but it will put your text where you want it. And it looks like you manually justified each line, but it's really all one continuous block of text...

If you want to do it another way, you can enter running text into a cell and have carriage breaks by using Alt+Enter to start new line, just make sure to go Format-->Cells (Ctrl+1)-->Alignment-->Wrap Text.

Example of Fill-->Justify

Excel 2010<TABLE style="BORDER-BOTTOM: #bbb 1px solid; BORDER-LEFT: #bbb 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #bbb 1px solid; BORDER-RIGHT: #bbb 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #dae7f5" width=25><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #dae7f5; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>asdfkjasdlfjsd alfjasdlfjsadlfjasd lfjasdl;fjsadlkf</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>sadl;kfasdl;kfjsd alfjsadlfjsadl;k jfs;dlajf;lsdjfsd</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD>alfjsdlfjsadl ;fjl;sdajsdalf</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>


As for the example, post what you've got and someone can help you fill out the parts you don't get.

Unfortunately, I'm packing to head out of town in the morning, but someone else will step in. That's the great thing about this place.

Good luck

 

[jl3287]

If you organize your numbers correctly in basic algebra and find the answer, then you should have no problem creating formulas to meet your needs.


Dime a Dozen Diamonds makes synthetic diamonds by treating carbon.
Each diamond can be sold for $100.
The materials cost for a standard diamond is $30. The fixed costs
incurred each year for factory upkeep
and administrative expenses are $200,000.

x = units made/sold
y = years
30x + 200,000y = 100x

1 year break even point:

1. years
2. sold
3. fixed
4. materials
-
5. =(3*1)/(2-4)

2 year break even point:
change year, everything else the same

hope that helps.

 

[hrmajor]

This problem consists of four product lines for the fictional stores.
One column has the % of total sales.
The next column shows contribution margin ration in %.
The total sales for the next year are forecast to be $250,000.
The total fixed costs will be $50,000.
We must create a table showing: 1) sales 2)total variable costs 3)total contribution margin associated with each product
Then we have to give the aggregate contribution margin ration indicative of the sales mix.
Finally we have to show the break-even point in dollars.
I guess I need to be an algebra or data guru to do this. I can do basic math like a whiz but creating tables and figuring data is not my strong suit.
I appreciate any advice.
Thank you.

 

[shawnhet]

Based on your responses here, it seems as though you need some help understanding how to use Excel formulas. My suggestion is to try some of the online resources available - here for instance.

http://spreadsheets.about.com/od/excel101/a/frmla_funct_hub.htm

If you can do the calculation with a pencil and paper, you'll be able to do it on Excel. You just need to learn the proper method of entering formulae.

Cheers,