Power-Ride Inc. is a privately owned business in Western Canada that has found success with the production
Question:
Power-Ride Inc. is a privately owned business in Western Canada that has found success with the production of two-wheeled, self-balancing personal transportation vehicles called Ride-Outs. The popularity of two-wheeled personal transportation vehicles is continuing to grow in both the private consumer and commercial markets. Two-wheeled personal transportation vehicles give people the option to use battery power as an alternative to cycling or walking. Power-Ride was founded eight years ago by two siblings, Ryan and Nick, who love the outdoors. Ryan suffers from severe asthma and had to significantly reduce his outdoor activities until he discovered two-wheeled personal transportation vehicles during a European vacation. He is very passionate about the business. Given that the business is mostly seasonal, growth is heavily dependent on the weather; however, the company is seeing an overall steady growth pattern. It is time to start preparing next fiscal year's budget for presentation to the board. You, CPA, are the controller for Power-Ride and have been asked by the owners to help prepare the documents required for the budget. Task #1 Ride-Out sales are highest in the spring and summer months, when private consumer purchases peak. The most profitable months are always March to May, when outdoor adventure stores are preparing for the summer season. The first nine months of this year have been difficult. Due to poor weather, monthly sales have dipped 4% over the prior three-year average, and this decrease is reflected in the estimate for the remaining three months. Fortunately, there is no indication that the bad weather will repeat into the upcoming year
You have obtained the following sales data for the past nine months, as well as three months of forecasted data: Month Units sold January 74 February 90 March 210 April 240 May 255 June 205 July 180 August 160 September 90 October (forecast) 85 November (forecast) 70 December (forecast) 65 Power-Ride had a one-time special order in April for 15 Ride-Outs that is not expected to occur this upcoming year. One of Power-Ride's retailers, Outfitters Inc., recently closed. It accounted for 5% of regular sales for January to March, and 8% of regular sales for the remainder of the year. The Ride-Outs currently sell for $1,400 per unit, and the company is planning to increase the selling price by 5% for the coming year. Market research shows that two-wheeled personal transportation vehicles are gaining popularity with those who live in hilly or windy areas and those with reduced fitness levels. As a result, experts are predicting that sales growth in this market will be at least 6% in the coming year. Power-Ride requires inventories of finished goods on hand at the end of each month to be equal to 50% of the following month's budgeted sales. Power-Ride expects to have 20 units on hand on this December 31 (at year end). Prepare a monthly sales budget in both units and dollars for the next fiscal year. In addition, prepare a budget in units to forecast required monthly production to meet expected sales. Your response should be no longer than half a page, excluding any Excel files.
Task #2 You have completed the budgets for the production inputs for next year, summarized below. January February March April May June Direct materials $30,624 $50,945 $75,547 $76,000 $63,230 $50,584 Direct labour $30,019 $49,940 $74,055 $74,499 $61,981 $49,585 Manufacturing overhead $12,615 $20,986 $31,120 $31,307 $26,046 $20,837 July August September October November December Direct materials $41,678 $32,772 $29,923 $27,608 $23,867 $22,528 Direct labour $40,855 $32,126 $29,332 $27,062 $23,395 $22,083 Manufacturing overhead $17,169 $13,500 $12,326 $11,372 $ 9,832 $ 9,280 The cost per unit of the opening inventory in January is expected to be $705 per unit. Monthly inventory is costed at the average annual manufacturing costs for the year based on the required production level, and any over- or under amounts are included in the cost of goods sold for the month. You were also provided with the following to use for the selling and administrative budget. Prior-year monthly expense information and assumptions: Shipping costs $45 per unit (no expected change) Wages and salaries $20,000 expected to increase by 3% inflation only at the beginning of the year and then remain constant Depreciation $8,200 expected to stay the same Utilities $7,100 expected to increase by 3% in February and then remain constant Insurance $4,100 expected to increase by 2% on renewal in April and then remain constant Miscellaneous $2,900 expected to stay the same a) Prepare the monthly cost of goods sold budget for the next fiscal year. b) Prepare the monthly selling and administrative expenses budget for next fiscal year. c) Prepare the annual budgeted net income statement for next fiscal year, ignoring income taxes based upon the information provided. Your response should be no longer than half a page, excluding any Excel files.
Task #3 Third-quarter results are in, and cost of goods sold is higher than anticipated. A direct material that had some variance in the quarter is the aluminum used in the Ride-Outs. You have decided to look at the flexible budget direct material variance related to this, as well as the flexible budget direct labour variance for all production. You have the following information to perform your analysis: Standard inputs Standard cost for one unit of output for one unit of input Direct materials 10.00 kg $32/kg Direct labour 8.00 hours $42/hour Actual performance for the company is shown below: Actual output (in units): 1,371 Direct materials: Purchased and used 13,750 kg Actual price per input $39.40/kg Direct labour: Labour hours of input 11,500 hours Actual price per hour $43.10/hour Calculate the flexible budget, rate, and efficiency variances for the direct materials and direct labour, and evaluate the results. Your response should be no longer than half a page, excluding any Excel files. Task #4 In order to offset the higher cost of aluminum, the board wants to look at the motor suppliers to see if material costs can be reduced in this area. We are currently using a local supplier, Alpha, with a cost of $130 per unit. Use Power BI to provide analysis of the supplier options to present to the board. The board would like you to do the following: i. Create a card visualization of how many suppliers there are. ii. Create a map visualization of the supplier locations. iii. Create a dashboard, including a table, chart, and slicer, showing the total costs per supplier. Submit your response by following the steps outlined in the Appendix and providing screenshots from Power BI.
Provide a recommendation to the board on the supplier with the lowest total cost. Compare this recommendation to the current supplier. Hints: Your visualization has to be selected before you can work on it. To select your visualization, simply click on it in Power BI. Using Microsoft's Snipping Tool, you can easily capture an image of the specific visualization requested. Copy the visualizations and paste them into your memo.