BUS100 Tutor-Marked Assignment
Question 1
(All parts of Q1 should be on THIS WORKSHEET. Do NOT create other worksheets!)
A company sells three products (A, B, C). Historical monthly sales data for the past 12 months has been provided. The company wants to forecast demand over the next 6 months, plan production, and analyse its finances. You may assume a linear relationship between sales and the number of months. The selling prices for A, B, and C are $20, $35, and $50, and their unit costs are $12, $20, and $30, respectively. There is a fixed overhead cost of $10,000 per month.
| Historical sales (12 months) for each product are shown in the table below. | |||||
| Month | A | B | C | ||
| 1 | 120 | 100 | 80 | ||
| 2 | 150 | 120 | 75 | Selling Price | |
| 3 | 140 | 110 | 85 | A | 20 |
| 4 | 155 | 125 | 90 | B | 35 |
| 5 | 160 | 130 | 95 | C | 50 |
| 6 | 180 | 150 | 100 | ||
| 7 | 160 | 140 | 105 | Unit cost | |
| 8 | 190 | 155 | 110 | A | 12 |
| 9 | 185 | 160 | 115 | B | 20 |
| 10 | 170 | 165 | 120 | C | 30 |
| 11 | 200 | 170 | 130 | ||
| 12 | 220 | 190 | 150 | ||
a. Using the past twelve months’ historical demand, determine the next six months’ demand for each product using the appropriate Excel function. Plot the appropriate graph to show the demand from month 1 to month 18.
| Month | A | B | C |
| 1 | 120 | 100 | 80 |
| 2 | 150 | 120 | 75 |
| 3 | 140 | 110 | 85 |
| 4 | 155 | 125 | 90 |
| 5 | 160 | 130 | 95 |
| 6 | 180 | 150 | 100 |
| 7 | 160 | 140 | 105 |
| 8 | 190 | 155 | 110 |
| 9 | 185 | 160 | 115 |
| 10 | 170 | 165 | 120 |
| 11 | 200 | 170 | 130 |
| 12 | 220 | 190 | 150 |
| 13 | 214 | 190 | 143 |
| 14 | 221 | 197 | 149 |
| 15 | 228 | 204 | 155 |
| 16 | 235 | 212 | 161 |
| 17 | 242 | 219 | 166 |
| 18 | 249 | 226 | 172 |
Step 1: Draw the diagram using data in month and sales data for each item to see the relationship)
Step 4: Find forecasted number of machines (eg, counter, staff, etc) for the next 10 years
Step 5: Find additional resources needed: New year – previous year
Step: Find the next 6 months’ demand.
=TREND(C$42:C$53,$B$42:$B$53,$B54)
b. Construct a spreadsheet model to evaluate the company’s finances for the next six months. You should use the forecasted demand from part (a) and create a table showing monthly revenue, production cost, overhead cost, total cost (including production and overhead costs), and profit. Use the model to determine total revenue, total cost, and profit accumulated over the next six months.
| Revenue vs Cost Analysis | Item A | Item B | Item C |
| Fixed overhead cost | Â $Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 10,000.00 | Â $Â Â Â Â Â Â Â Â 10,000.00 | Â $Â Â Â Â Â Â Â 10,000.00 |
| Cost per item | Â $Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 12.00 | Â $Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 20.00 | Â $Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 30.00 |
| # of months | 6 | 6 | 6 |
| Selling price per item | Â $Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 20.00 | Â $Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 35.00 | Â $Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 40.00 |
| Cost per item | Â $Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 12.00 | Â $Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 20.00 | Â $Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 30.00 |
| Demand for month 13: | 214 | 190 | 143 |
| Demand for month 14: | 221 | 197 | 149 |
| Demand for month 15: | 228 | 204 | 155 |
| Demand for month 16: | 235 | 212 | 161 |
| Demand for month 17: | 242 | 219 | 166 |
| Demand for month 18: | 249 | 226 | 172 |
| Total Demand: | 1389 | 1248 | 946 |
| Decision model for profit analysis | |||
| Item A | Item B | Item C | |
| Total Demand (6 months): | 1389 | 1248 | 946 |
| Total revenue ($) | Â $Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 27,780.00 | Â $Â Â Â Â Â Â Â Â 43,680.00 | Â $Â Â Â Â Â Â Â 37,840.00 |
| Total cost ($) | Â $Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 26,668.00 | Â $Â Â Â Â Â Â Â Â 34,960.00 | Â $Â Â Â Â Â Â Â 38,380.00 |
| Profit ($) | Â $Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 1,112.00 | Â $Â Â Â Â Â Â Â Â Â Â Â 8,720.00 | Â $Â Â Â Â Â Â Â Â Â Â (540.00) |
c. Determine the optimal sale volume for product A, for the 6th month from now, for the company to break even; you may assume that the other parameters all remain the same. For this part only, you may assume equal quantities across products. Explain how you get the answers.
| A | B | C | |
| Sell Price | Â $Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 20.00 | Â $Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 35.00 | Â $Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 40.00 |
| Cost Price | Â $Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 12.00 | Â $Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 20.00 | Â $Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 30.00 |
| Sales Volume | 249 | 249 | 249 |
| Total Variable Cost | Â $Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 2,988.00 | Â $Â Â Â Â Â Â Â Â Â Â Â 4,980.00 | Â $Â Â Â Â Â Â Â Â Â 7,470.00 |
| Total Revenue | Â $Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 4,980.00 | Â $Â Â Â Â Â Â Â Â Â Â Â 8,715.00 | Â $Â Â Â Â Â Â Â Â Â 9,960.00 |
| Fixed Costs | Â $Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 10,000.00 | Â $Â Â Â Â Â Â Â Â 10,000.00 | Â $Â Â Â Â Â Â Â 10,000.00 |
| Total Profit | Â $Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â (8,008.00) |
| Items | Item A | Item B | Item C | ||||||
| Month | Total Revenue | Total Cost | Total Profit | Total Revenue | Total Cost | Total Profit | Total Revenue | Total Cost | Total Profit |
| 13 | Â $Â Â Â Â Â 4,280.00 | Â $ 12,568.00 | Â $ (8,288.00) | Â $Â Â Â Â 6,650.00 | Â $ 13,800.00 | Â $(7,150.00) | Â $Â Â Â 7,150.00 | Â $ 4,290.00 | Â $ 2,860.00 |
| 14 | Â $Â Â Â Â Â 4,420.00 | Â $ 12,652.00 | Â $ (8,232.00) | Â $Â Â Â Â 6,895.00 | Â $ 13,940.00 | Â $(7,045.00) | Â $Â Â Â 7,450.00 | Â $ 4,470.00 | Â $ 2,980.00 |
| 15 | Â $Â Â Â Â Â 4,560.00 | Â $ 12,736.00 | Â $ (8,176.00) | Â $Â Â Â Â 7,140.00 | Â $ 14,080.00 | Â $(6,940.00) | Â $Â Â Â 7,750.00 | Â $ 4,650.00 | Â $ 3,100.00 |
| 16 | Â $Â Â Â Â Â 4,700.00 | Â $ 12,820.00 | Â $ (8,120.00) | Â $Â Â Â Â 7,420.00 | Â $ 14,240.00 | Â $(6,820.00) | Â $Â Â Â 8,050.00 | Â $ 4,830.00 | Â $ 3,220.00 |
| 17 | Â $Â Â Â Â Â 4,840.00 | Â $ 12,904.00 | Â $ (8,064.00) | Â $Â Â Â Â 7,665.00 | Â $ 14,380.00 | Â $(6,715.00) | Â $Â Â Â 8,300.00 | Â $ 4,980.00 | Â $ 3,320.00 |
| 18 | Â $Â Â Â Â Â 4,980.00 | Â $ 12,988.00 | Â $ (8,008.00) | Â $Â Â Â Â 7,910.00 | Â $ 14,520.00 | Â $(6,610.00) | Â $Â Â Â 8,600.00 | Â $ 5,160.00 | Â $ 3,440.00 |
| Breakeven Analysis | |
| 10,000 | fixed cost |
| 12 | Cost Price |
| 6 | Months |
| 1250 | Demand |
| A | |
| Â $Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 20.00 | Sell Price |
| Â $Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 12.00 | Cost Price |
| 1250 | Sales Volume |
| Â $Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 15,000.00 | Total Variable Cost |
| Â $Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 25,000.00 | Total Revenue |
| Â $Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 10,000.00 | Fixed Costs |
| Â $Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â – | Total Profit |
Assuming all sales volume is the same, using month 6 demand =Â 249 To breakeven, profit = 0.
Question 3
Type in your answers to Q3a and Q3b here in essay format. Don’t insert any new row or column, otherwise the word count formula won’t work.
For part 3a, you may create a new worksheet to show the table and respective graph if needed.
3a. Type your answer here in proper essay format with paragraph clearly separated.
3b. type your answer here in proper essay format with paragraphs clearly separated.
Total word count (Q3b Max 300)
All BUS100 Tutor-Marked Assignment Data in Excel Sheet
Order customised academic support for the BUS100 Business Skills and Management TMA
Native Singapore Writers Team
- 100% Plagiarism-Free Essay
- Highest Satisfaction Rate
- Free Revision
- On-Time Delivery
