ACTY 2110 Lecture Notes - Lecture 1: Profit Margin, Fixed Cost, Financial Analyst
Document Summary
Get access
Related Documents
Related Questions
Decision Inputs (Data) | Cost Structure Alternative #1 | Cost Structure Alternative #2 |
Delivery price (i.e., revenue) per package | $60 | $60 |
Variable cost per package delivered | $48 | $30 |
Contribution margin per unit | $12 | $30 |
Fixed costs (per year) | $600,000 | $3,000,000 |
(1) Assume that forthe coming year total fixed costs are expected to increase by 10%for each of the two alternatives. What is the new break-even point,in terms of number of deliveries, for each decision alternative? Bywhat percentage did the break-even point change for each case? Howdo these figures compare to the percentage increase in budgetedfixed costs?
(2) Assume an averageincome-tax rate of 40%. What volume (number of deliveries) would beneeded to generate an after-tax profit, ?A, of 5%of sales for each alternative?
(3) Consider the original data in the problem. Construct a graphfor each of the two alternatives depicting pre-tax profit,?B, as function of volume (number of deliveries peryear). Clearly label the profit equation for eachalternative.
(4) Based on the graphs prepared in (9), which decisionalternative do you think is the more profitable one for thisbusiness?
(5) Based on the original data and the graphs prepared above in(10), which decision alternative is more risky to thebusiness? Explain. (Hint: Think about, and define in your answer,the notion of
Requirement 2:
The company has just hired a new marketing manager who insiststhat unit sales can be dramatically increased by dropping theselling price from $8 to $7. The marketing manager would like touse the following projections in the budget:
Check your worksheet by changing the budgeted unit sales inQuarter 2 of Year 2 in cell C5 to 75,000 units. The total expectedcash collections for the year should now be $2,085,000. If you donot get this answer, find the errors in your worksheet and correctthem.
Data | Year 2 Quarter | Year 3 Quarter | ||||
1 | 2 | 3 | 4 | 1 | 2 | |
Budgeted unitsales | 50,000 | 70,000 | 115,000 | 60,000 | 80,000 | 95,000 |
Selling price perunit | $7 | per unit | ||||
A | B | C | D | E | F | F | |
1 | Chapter 9: Applying Excel | ||||||
2 | |||||||
3 | Data | Year 2 Quarter | Year 3 Quarter | ||||
4 | 1 | 2 | 3 | 4 | 1 | 2 | |
5 | Budgeted unit sales | 50,000 | 70,000 | 115,000 | 60,000 | 80,000 | 95,000 |
6 | |||||||
7 | ⢠Selling price per unit | $7 | per unit | ||||
8 | ⢠Accounts receivable, beginning balance | $65,000 | |||||
9 | ⢠Sales collected in the quarter sales are made | 75% | |||||
10 | ⢠Sales collected in the quarter after sales are made | 25% | |||||
11 | ⢠Desired ending finished goods inventory is | 30% | of the budgeted unit sales of the next quarter | ||||
12 | ⢠Finished goods inventory, beginning | 12,000 | units | ||||
13 | ⢠Raw materials required to produce one unit | 5 | pounds | ||||
14 | ⢠Desired ending inventory of raw materials is | 10% | of the next quarter's production needs | ||||
15 | ⢠Raw materials inventory, beginning | 23,000 | pounds | ||||
16 | ⢠Raw material costs | $0.80 | per pound | ||||
17 | ⢠Raw materials purchases are paid | 60% | in the quarter the purchases are made | ||||
18 | and | 40% | in the quarter following purchase | ||||
19 | ⢠Accounts payable for raw materials, beginning balance | $81,500 | |||||
20 |
c. What is the total cost of raw materials to be purchased forthe year under this revised budget?
d. What are the total expected cash disbursements for rawmaterials for the year under this revised budget?
Here is what I got... but my answers are wrong... pleasehelp
Chapter 9: ApplyingExcel | |||||||
Data | Year 2 Quarter | Year 3 Quarter | |||||
1 | 2 | 3 | 4 | 1 | 2 | ||
Budgeted unit sales | 50,000 | 70,000 | 115,000 | 60,000 | 80,000 | 95,000 | |
⢠Selling price per unit | $7 | per unit | |||||
⢠Accounts receivable, beginningbalance | $65,000 | ||||||
⢠Sales collected in the quarter salesare made | 75% | ||||||
⢠Sales collected in the quarter aftersales are made | 25% | ||||||
⢠Desired ending finished goodsinventory is | 30% | of the budgeted unit sales of the next quarter | |||||
⢠Finished goods inventory,beginning | 12,000 | units | |||||
⢠Raw materials required to produce oneunit | 5 | pounds | |||||
⢠Desired ending inventory of rawmaterials is | 10% | of the next quarter's production needs | |||||
⢠Raw materials inventory,beginning | 23,000 | pounds | |||||
⢠Raw material costs | $0.80 | per pound | |||||
⢠Raw materials purchases are paid | 60% | in the quarter the purchases are made | |||||
and | 40% | in the quarter following purchase | |||||
⢠Accounts payable for raw materials,beginning balance | $81,500 | ||||||
Enter a formula into eachof the cells marked with a ? below | |||||||
Review Problem: Budget Schedules | |||||||
Construct the sales budget | Year 2 Quarter | Year 3 Quarter | |||||
1 | 2 | 3 | 4 | 1 | 2 | ||
Budgeted unit sales | 50,000 | 70,000 | 115,000 | 60,000 | 80,000 | 95,000 | |
Selling price per unit | $7 | $7 | $7 | $7 | $7 | $7 | |
Total sales | $350,000 | $490,000 | $805,000 | $420,000 | $560,000 | $665,000 | |
Construct the schedule of expected cashcollections | Year 2 Quarter | ||||||
1 | 2 | 3 | 4 | Year | |||
Accounts receivable, beginningbalance | $ 65,000 | $ 65,000 | |||||
First-quarter sales | 262,500 | $ 87,500 | $ 350,000 | ||||
Second-quarter sales | 367,500 | $ 122,500 | $ 490,000 | ||||
Third-quarter sales | 603,750 | $ 201,250 | $ 805,000 | ||||
Fourth-quarter sales | 315,000 | $ 315,000 | |||||
Total cash collections | $ 327,500 | $ 455,000 | $ 726,250 | $ 516,250 | $ 2,025,000 | ||
Construct the production budget | Year 2 Quarter | Year 3 Quarter | |||||
1 | 2 | 3 | 4 | Year | 1 | 2 | |
Budgeted unit sales | 50,000 | 70,000 | 115,000 | 60,000 | 295,000 | 80,000 | 95,000 |
Add desired finished goodsinventory | 21,000 | 34,500 | 18,000 | 24,000 | 24,000 | 28,500 | |
Total needs | 71,000 | 104,500 | 133,000 | 84,000 | 319,000 | 108,500 | |
Less beginning inventory | 12,000 | 21,000 | 34,500 | 18,000 | 12,000 | 24,000 | |
Required production | 59,000 | 83,500 | 98,500 | 66,000 | 307,000 | 84,500 | |
Construct the raw materials purchasesbudget | Year 2 Quarter | Year 3 Quarter | |||||
1 | 2 | 3 | 4 | Year | 1 | ||
Required production (units) | 59,000 | 83,500 | 98,500 | 66,000 | 307,000 | 84,500 | |
Raw materials required to produce oneunit | 5 | 5 | 5 | 5 | 5 | 5 | |
Production needs (pounds) | 295,000 | 417,500 | 492,500 | 330,000 | 1,535,000 | 422,500 | |
Add desired ending inventory of rawmaterials (pounds) | 41,750 | 42,500 | 28,000 | 36,500 | 36,500 | ||
Total needs (pounds) | 336,750 | 460,000 | 520,500 | 366,500 | 1,571,500 | ||
Less beginning inventory of rawmaterials (pounds) | 23,000 | 41,750 | 42,500 | 28,000 | 23,000 | ||
Raw materials to be purchased | 313,750 | 418,250 | 478,000 | 338,500 | 1,548,500 | ||
Cost of raw materials per pound | $0.80 | $0.80 | $0.80 | $0.80 | $0.80 | ||
Cost of raw materials to bepurchased | $251,000 | $334,600 | $382,400 | $270,800 | $1,238,800 | ||
Construct the schedule of expected cashpayments | Year 2 Quarter | ||||||
1 | 2 | 3 | 4 | Year | |||
Accounts payable, beginningbalance | $ 81,500 | $ 81,500 | |||||
First-quarter purchases | 150,600 | $ 100,400 | 251,000 | ||||
Second-quarter purchases | 200,760 | $ 133,840 | $ 334,600 | ||||
Third-quarter purchases | 229,440 | $ 152,960 | 382,400 | ||||
Fourth-quarter purchases | 162,480 | $ 162,480 | |||||
Total cash disbursements | $ 232,100 | $ 301,160 | $ 363,280 | $ 315,440 | $ 1,211,980 | ||